mysql check if a value in my table doesn't exist

[icpooreman]

Hey I'm very new to databases and am using php with mysql. I want to check if a value in a field already exists but when I try to query the following line

$query = "SELECT * FROM users WHERE name='$name'";

I get the following error
Unknown column '$name' in 'field list'
$name being the variable I'm checking to see if exists

I only get that error when $name isn't in the table and thats what I need to check for so if anybody has any insight into whats wrong with my query above please help.

[txbakers]

I would rename your local variable and try that.
Also, $name should have a default value so if it is blank you won't get an error.

Just some ideas.

[greigmc]

When the database isn't finding the '$name' value in your field it is returning 0 rows. I bet your php code assumes that you are getting at least one row back. Hence the error when the resource doesn't have anything in it.

There are two ways around it. Either you can modify your sql to
$query = "SELECT count(*) FROM users WHERE name='$name';";
which will guarantee you a result where you can just check that it's greater than 0 or you can use the
mysql_num_rows() function to check that you actually have a row before you fetch the first row e.g.

// does user exist?
$name = mysql_real_escape_string($name);
$query = "SELECT * FROM users WHERE name='$name';";
$res = mysql_query($query);
if (mysql_num_rows($res) > 0) {
// yes, pull in the user details
} else
// no, user doesn't exist
}