This lesson will give you a step by step guide on how to build a login script using PHP and MySQL. You will get a better understanding how a content management system like WordPress communicates with a database, selects data from it, and processes that data to give final HTML code to a user’s browser.
First of all we are going to need a database table carrying our login information as a reference. We could use the following table to do that:
1 2 3 4 5 | create table user ( id int(20) primary key auto_increment, username varchar(120) unique, password varchar(120) ) |
The attribute “id” is just to have a fast integer based way of sorting and processing our user information. It is our primary key and will be incremented automatically by the DBMS if a new row is inserted.
The next attribute “username” is a text column carrying our unique username.
The last attribute “password” is also a text column carrying the password for each user, whereas that does not have to be unique. Furthermore we sure want to place a hash value here instead of our clear text password, but that will be done later using PHP.
For now, that table is in charge of holding our login information. We will insert a few users:
1 2 3 | insert into user values (null, 'bob', 'secret'); insert into user values (null, 'paul', 'god'); insert into user values (null, 'sandra', 'flower'); |
We notice that we will just use clear text passwords for now, just to make it easier to understand how stuff works. I will cover a more secure way of storing password information shortly.
As discussed within this article we will have to construct a little login form which deals with POST parameters. We could use the following code within a file “login-form.html”:
1 2 3 4 5 6 7 8 9 | <html><head><title>Login form</title></head> <body> <form action="user-login-processing.php" method="POST" enctype="text/plain"> Username: <input type="text" name="username" value="" /><br /> Password: <input type="password" name="password" value="" /><br /> <button type="submit" value="Login">Login</button> </form> </body> </html> |
That’s all we need for a basic HTML login form. The user is asked for his username and his password. Then when he clicks the Login button, the form and its parameters will get forwarded to the PHP script “user-login-processing.php” using POST method.
Now let’s see what our script “user-login-processing.php” needs to do.
As discussed within this article we will first have to connect to our database. We do that as follows:
1 2 3 4 5 6 7 8 | <?php $server = "dbs.your-domain.com"; $user = "bob"; $password = "P4ssW0rD!"; $database = "wordpress"; mysql_connect($server, $user, $password); mysql_select_db($database); ?> |
Once connected, we are able to process our parameters.
The first thing to do is to grab our parameters:
1 2 3 4 | <?php $username = @$_POST["username"]; $password = @$_POST["password"]; ?> |
The code above stores our user input values inside variables having the same name as our parameters do. That’s just to make things as easy as possible. The @-sign is to ignore missing parameter values.
As soon as we’re both connected and parameter-ready, we can ask our database if a user exists with the credentials provided within the login form.
What is going on in the code below is discussed within this article. The important thing here, however, is the fact that we’re asking the database if there is a row for two specific conditions — both username and password values must be present.
Since our username is unique, there won’t ever be a second data record. Still there could be no row at all if either the username or the password is wrong. We’re looking for a perfect match for those two attribute values.
1 2 3 4 5 6 7 8 9 | <?php $sql="select id from user where username = '$username' and password = '$password'"; $stmt = mysql_query($sql) or die ( mysql_error() ); //$row = mysql_fetch_row($stmt); //if we wanted to test our statement //echo $row[0]; //if we wanted to test our statement $num_rows = mysql_num_rows($stmt); if ($num_rows == 1) { echo "You are logged in"; } else { header('Location: login-form.html'); } ?> |
The attribute that is actually selected is, in our case, “id”. That is irrelevant and just serves the purpose to actually have a result in case of a found match.
If there is a match, you will see “You are logged in”. If there is no match, the script will head back to the login form.
This is the full blown version of the code discussed above.
The HTML login formular “login-form.html”:
1 2 3 4 5 6 7 8 9 | <html><head><title>Login form</title></head> <body> <form action="user-login-processing.php" method="POST" enctype="text/plain"> Username: <input type="text" name="username" value="" /><br /> Password: <input type="password" name="password" value="" /><br /> <button type="submit" value="Login">Login</button> </form> </body> </html> |
The PHP target script “user-login-processing.php” used for login:
<?php // parameter grabbing $username = @$_POST["username"]; $password = @$_POST["password"]; // database connection $server = "dbs.your-domain.com"; $user = "bob"; $password = "P4ssW0rD!"; $database = "wordpress"; mysql_connect($server, $user, $password); mysql_select_db($database); // initiate login procedure $sql="select id from user where username = '$username' and password = '$password'"; $stmt = mysql_query($sql) or die ( mysql_error() ); //$row = mysql_fetch_row($stmt); //if we wanted to test our statement //echo $row[0]; //if we wanted to test our statement $num_rows = mysql_num_rows($stmt); // login successfull? if ($num_rows == 1) { echo "You are logged in"; // do your stuff } else { header('Location: login-form.html'); } ?> |
A great video tutorial serving the same purpose as this blog article with just slightly different code follows right here:
https://www.youtube.com/watch?v=5XpBzLVHkPY
I hope this helps you in getting started. Enjoy! 😉
The code above is not the most secure 🙂 You may want to fight SQL injection and other sorts of manipulation. I’m currently thinking about an entire section on PHP and MySQL security within this blog.
For now, please at least use the following procedure to not store your passwords as clear text within your database.
The following code will produce a hashed version of “asdf” as a possible (insecure) password.
1 2 3 | <?php echo sha1("asdf"); ?> |
You can use sha1($password) within the code snippets above to hash the user’s password input right after grabbing it as a parameter value and right before sending it to the database. That procedure will result in the same answer — to either have a username/password match or to have none.
However it requires you to store hashed passwords within the database in the first place. You can use the echo sha1() code above to look up your desired hash value.
Furthermore you may want to escape your parameters, etc. — just take a look at the video above for a little more security.
Even though we use a common problem to demonstrate how data is being read out of a database, the basic lesson you need to understand is this: First you need to make a database connection using your PHP script. Then you use that connection to ask the database for certain information. Finally you use the data obtained from the database to process it further, for example by echoing that data or by changing the user’s heading – like in our login example.
That roundtrip is what we talked about in the very beginning. All that is left for a user’s visit cycle to complete is to return that result as HTML that a browser can understand, while supporting functionality to request changes to the database. That is what we will cover in our final lesson.