[RESOLVED] Comparing values in database

mhodge87

I am very new to php and mysql. I have created a form that allows a user to register. What i'm trying to do is have, when the user clicks submit, first check to make sure both passwords field match, but then i am wanting it to check the mysql table to make sure someone with the same cid isn't already registered. my code is below. Any help would be greatly appreciated.

//insert information

$query="SELECT cid = " . $_POST[cid] . "FROM userinfo";

If($_POST[password] == $_POST[password2]) //ensures passwords match before submission
{	
	If($query == $_POST[cid]) //checks to see if user is already registered.
	{
		die("Your CID is already registered.");
		}
	else
		{
		$sql="INSERT INTO userinfo(firstn, lastn, cid, email, psswd, rating)
		VALUES
		('$_POST[fname]','$_POST[lname]','$_POST[cid]','$_POST[email]', '$_POST[password]', '$_POST[rating]')";
	}
}

else
{
	die("Your passwords did not match, please go back and re-enter");
//check to see if information was sent, if not dispaly error. 
}

When i test it, the form does everything it should except compare the values from the user entered "Cid's" to the ones in the database already. I'm sure the error lies in the following line of code:

$query="SELECT cid = " . $_POST[cid] . "FROM userinfo";

i'm just not sure of how to write it and a google search didn't clear anything up. Thanks again

bradgrafelman

Array indeces in your case should be strings. $row[name] does not have a string as an index, but a constant. PHP will detect this (assuming there actually isn't a constant named 'name') and throw an E_NOTICE and then assume it is a string. So, use quotes: $row['name'].
It appears as though you're vulnerable to SQL injection attacks. User-supplied data should never be placed directly into a SQL query! Instead, sanitize it with a function such as [man]mysql_real_escape_string/man.
Your first query isn't valid... the format of a SELECT query should look something like
```
SELECT column1, column2 FROM table_name WHERE something='something'
```
If you're simply trying to check if 'cid' already exists in the table, you could do something like:
```
SELECT 1 FROM table_name WHERE cid='cid_value' LIMIT 1
```
and then use [man]mysql_num_rows/man to see if 1 row was returned (cid already exists) or not.
You never execute your SQL queries. Simply typing them into a string doesn't do anything - you have to actually send them to the SQL server using [man]mysql_query/man (assuming you're using the MySQL extension). Also note that I don't see any code that first connects to the SQL server and then selects the proper database, so I'll assume that you've already done that.

mhodge87

Thanks for the tip on the sql injection attack. I'll go through and fix it.

So if i understand you correctly, in order for it to work the code should be the following?

$result=mysql_query("SELECT 1 FROM userinfo WHERE cid='cid_value' LIMIT 1", $con);
$num_rows = mysql_num_rows($result);

next question. the cid value, that should be what the user entered correct? because checking the database against what the user originally entered. So how would you put a variable into that string?

bradgrafelman

Same way you would with any other [man]string[/man]... though that'll probably change once you use the [man]mysql_real_escape_string/man function. I normally sanitize data in queries like so:

$query = sprintf("SELECT col1 FROM foo WHERE bar='%s' OR foobar='%s'",
        mysql_real_escape_string($_POST['bar']),
        mysql_real_escape_string($_POST['foobar']));
$exec = mysql_query($query);

although you could just do simple concatenation.