Php – How to authenticate a user in PHP / MySQL


So recently I learned how to properly add a username and password to a database.
My database is usersys, and the table storing user information is called userdb. The table has two columns – username (primary), password.

The registration form works great, enters the users input into the database correctly and also checks to see whether the user's username is already in the database or not.

With that said, I am asking if anyone could help me create a login script. So far, this is what I have:

$username = $_POST['username'];
$password = $_POST['password'];
$displayname = $_POST['username'];
$displayname = strtolower($displayname);
$displayname = ucfirst($displayname);           
echo "Your username: " . $displayname . "<br />";

mysql_connect("localhost", "root", "******") or die(mysql_error());
echo "Connected to MySQL<br />";

mysql_select_db("usersys") or die(mysql_error());
echo "Connected to Database <br />";

$lcusername = strtolower($username);
$esclcusername = mysql_real_escape_string($lcusername);
$escpassword = mysql_real_escape_string($password);

$result = mysql_query("SELECT * FROM userdb WHERE username='$esclcusername' AND     password='$escpassword'") or die(mysql_error());
$row = mysql_fetch_array( $result );
$validateUser = $row['username'];
$validatePass = $row['password'];

The POST data is from the previous log in page. I want this script to check the table (userdb) and find the row for the username that the user entered from the previous form and verify that the password entered matches the username's password set in that row, in userdb table.

I also want some type of way to check whether or not if the username entered exists, to tell the user that the username entered does not exists if it can not be found in the table.

Best Solution

This is not a direct answer to this question but a GOOD value-add. You should use MYSQL SHA1 function to encrypt the password before storing into the database.

$user = $_POST['userid'];
$pwd = $_POST['password'];
$insert_sql = "INSERT into USER(userid, password) VALUES($user, SHA1($pwd))";

$select_sql = "SELECT * FROM USER WHERE userid=$user AND password=SHA1($pwd))";