Check SQL database if value exists and then return value if it does

Jamie Fritz picture Jamie Fritz · Nov 11, 2011 · Viewed 69.9k times · Source

I am pretty new to both php and SQL. I have a login page where I ask for the users username and password. I would like to search a database I have created for both the username and password to see if they exist. The database table has two columns, Username and Password. I don't care to much about security so a simple script will work. But I do want to be able to expand on it someday, so therefor I am using a database, because currently I just use an array in php to store usernames and passwords.

I am currently trying to get this code to work but am not getting the results I need.

$user_name = "db_username";
$password = "db_password";
$database = "db_name";
$server = "db_server";



$db_handle = mysql_connect($server, $user_name, $password);
$db_found = mysql_select_db($database, $db_handle);

if ($db_found) {
$result =mysql_query("SELECT 1 FROM my_table WHERE Username = $username");
if ($result>0)

    {
        echo 'Username and Password Found'; 
    }
else
    {
    echo 'Username and Password NOT Found';
    }
}
else {
print "Database NOT Found.";
mysql_close($db_handle);
}

This always returns Username and Password Found no matter is the username is in there or not. When printing $result I get Resource id #2. Thank you

Answer

Buttle Butkus picture Buttle Butkus · Nov 11, 2011

$result I think will evaluate to true even if the result set contains zero rows. It only returns boolean false on error according to the manual. Use mysql_num_rows to determine if you actually found anything with the query.

if ($db_found) {
$result =mysql_query("SELECT 1 FROM my_table WHERE `Username` = '$username'");
if ($result && mysql_num_rows($result) > 0)

    {
        echo 'Username and Password Found'; 
    }
else
    {
    echo 'Username and Password NOT Found';
    }
}
else {
print "Database NOT Found.";
mysql_close($db_handle);
}

EDIT: Of course, as of now (November 2013, and since long ago), the mysql_* functions have indeed been deprecated. Apparently you can now use identical mysqli_* functions (maybe just use find/replace), but most people are using PDO.