Authentication with PHP/MySQL

February 3, 2024 by Mike Maguire

This tutorial will walk you through setting up a database and using it to authenticate users to your site. This will allow only certain pages to be seen by users that have legitimate passwords. Once you read this tutorial you will be able to understand the basics of sessions which pass the authentication from page to page. There are several ways to improve and/or make the system better, but I’ll leave that to you. 🙂 Make sure you follow John’s php tutorials to understand the concepts we will be using:

MyAdmin Database Construction
Access Database with PHP
Creating Data for Database in PHP

First we need to set-up our database. All we need is a database with the fields of Username, PW, and ID number (which will auto-increment to be the Primary Key which has to be Unique.) The password field needs to accept a lot of characters (100 or more) because we are going to encrypt them. You could add other things like an approved option that once a user signs up you would have to approve them before they have access, but for the sake of this tutorial, we are going to keep it simple.

First, we need to create a temp file that will allow us to add a user to the database. We are going to encrypt our passwords using SHA1 so we wont be able to manually add them into the database. To do this we need to make a file that will run the SHA1 algorithm on whatever text in in the input box. I called this file tt.php. On pressing of the submit button it will send us to insert.php. All we need in the first file is some simple html as shown below:

<form method="post" action="insert.php">

Full Name: (Example: Michael R Maguire) <br />

<input type="text" name="user_name" size="50" maxlength="50"/> (50 Characters Max)

<br />
<br />

User Name: <br />

<input type="text" name="SHA_PW" size="20" maxlength="20"/> (20 Characters Max)

<br />
<br />

<input type="submit" value="Create User" />

</form>

This file should look like this when opened:

Next we need to create insert.php which will be called when the Create User button is pressed. This file will start with the following:

<?php
$user_name = $_POST['User_name'];
$SHA_PW = $_POST['SHA_PW'];

The above code will set the variable $user_name equal to the value of the user name form on the last page. Ensure that your input boxes on the last page match what you put in single quotes within the POST command. They are case sensitive.

$dbname = "teamtutorials_test";
$conn = mysql_connect ("localhost","teamtutorials_test","tutorial") or 
die ('cannot connect to database error: '.mysql_error());
mysql_select_db ($dbname);

This initaites the connection to our database so that we can insert the values into the database.

if(empty($user_name) || empty($SHA_PW)) {
echo "<h2>Please fill in all fields</h2>n";
echo "Please use the back button in your browsers and fill in all required fields.n";
die ();
}

This snippet of code will check to make sure that the variables that we set have values and were not left blank. It will prompt you to go back and change the entries. If either is empty, it will stop parsing the php and the rest of the file will not be executed. (**For some reason the code converter that we use here at TeamTutorials is showing “empty” twice. There should only be one**)

<?php
$sql ="INSERT INTO `teamtutorials_test`.`users` ( `User_ID` , `user_name` , `sha_pw`) VALUES (
NULL, '".$user_name."' ,'".sha1($SHA_PW)."');
mysql_query($sql) or die ('update failed: '.mysql_error())
?>
 

This code inserts the values of the variables into the database. Please note that there are several ways to include variables in a php/mysql statement. This is just one of those ways.

<?php
echo "User Name: $user_name";
echo "Sha Password: ".sha1($SHA_PW);
?>
 

This will echo the values of the data inserted into the database if the php completes. Once you see these values, you should have an additional entry in your database. The last step is to create our authentication file. There are several ways to do this. The way we are going to do it is to send them to a failed page on failure and to a success page on success. We will call this file login.php.

<?php

session_start();

?>
 

This should be the first line of the php file. It need to be parsed before ANY header information is sent to the browser. This starts the session. This will allow the authentication to be passed from page to page.

<form method="post" action="session.php">

Full Name: (Example: Michael R Maguire) <br />

<input type="text" name="user_name" size="50" maxlength="50"/> (50 Characters Max)

<br />
<br />

User Name: <br />

<input type="text" name="password" size="20" maxlength="20"/>

<br />
<br />

<input type="submit" value="Create User" />

</form>

As you can see we set up the input boxes almost the same as our user creation file. Once they hit submit it will send them to session.php. Let’s make that file now:

<?php

session_start(); 

if (isset($_POST['username']) && isset($_POST['password']))   
{
$username = $_POST['username'];      
$password = $_POST['password'];       

$conn = mysql_connect ("localhost","teamtutorials_test","tutorials") or 
die ('cannot connect to database error: '.mysql_error());
      
$sql = mysql_query("select count(*) from users 
where user_name = '$username' and sha_pw = sha1('$password')") or die(mysql_error());        

$results = mysql_result($sql, "0");   

if ($results == 0){          
header( 'Location:http://teamtutorials.com/tutorials/loginfailed.php');
}
else         
{
$_SESSION['valid_user'] = $username                  
header( 'Location:http://teamtutorials.com/tutorials/members.php'); 
}
}
?>

This code does several things. It first, sets the variables equal to the values of the input forms on the last page. Next it creates the connection to the database. Then it queries for a count (which will return the number of results that match the username and sha’d version of the text entered). If then sets the results equal to a variable and checks the variable. If it is equal to 0 it sends it to the failure page and if it equals anything other than 0 it sends it to the welcome page. The last things we need to do are make the welcome page and the logout page. Since we set the session variable, all we have to do is check that for a value and if it exists they are currently logged in.

<?php

session_start(); 

if (isset($_SESSION['valid_user']))
{
echo 'Welcome to the members section.';
}
else
{
echo 'You are not logged in. Please login first.';
}
?>

This checks to see if the value is set and if it is show them the welcome message. If it is not set (if someone went straight to the index.php file rather than going to login.php) it would tell them to log in first.

This concludes this tutorial. There are a lot of conecpts given here than can be hard to grab until you get used to them. I hope that you were able to follow this tutorial and that it will help you on your way. Please feel free to comment with your results and your recommendations. Thank you for reading.