Create a simple PHP script and MySQL database for user authentication.

MySql Logo
PHP logo

In this tutorial, we will show you how to create a simple script using PHP for user authentication. User data are stored in a MySQL database. So, lets create the database first and one authenticated user called “test”:

create database auth;
use auth;
create table authorised_users ( name varchar(20),
password varchar(40),
primary key (name)
);
insert into authorised_users values ( 'testuser',
sha1('password') );
grant select on auth.*
to 'webauth'
identified by 'webauth';
flush privileges;

Now that we have created the database and the “testuser” authenticated user lets build the form and the PHP script that identifies the user. Here is the code:

<?php
$name = $_POST['name'];
$password = $_POST['password'];
if(!isset($_POST['name'])&&!isset($_POST['password']))
{
//Visitor needs to enter a name and password
?>
<h1>Please Log In</h1>
This page is secret.
<form method="post" action="secretdb.php">
<table border="1">
<tr>
<th> Username </th>
<td> <input type="text" name="username"> </td>
</tr>
<tr>
<th> Password </th>
<td> <input type="password" name="password"> </td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="Log In">
</td>
</tr>
</table>
</form>
<?php
}
else
{
// connect to database server
$mysql = mysqli_connect( 'localhost', 'webauth', 'webauth' );
if(!$mysql)
{
echo 'Cannot connect to database.';
exit;
}
// select the database
$selected = mysqli_select_db( $mysql, 'auth' );
if(!$selected)
{
echo 'Cannot select database.';
exit;
}
// query the database to see if there is a record which matches
$query = "select count(*) from authorised_users where
name = '$username' and
password = sha1('$password');
$result = mysqli_query( $mysql, $query );
if(!$result)
{
echo 'Cannot run query.';
exit;
}
$row = mysqli_fetch_row( $result );
$count = $row[0];
if ( $count > 0 )
{
// visitor's name and password combination are correct
echo '<h1>Login Correct!</h1>';
echo 'Enjoy.';
}
else
{
// visitor's name and password are not correct
echo '<h1>Login Failed!</h1>';
echo 'Check username and password again.';
}
}
?>

Save the PHP Code file as script.php and you are ready 🙂 Note that password is encrypted using sha1().

6 thoughts on “Create a simple PHP script and MySQL database for user authentication.

  1. Ryan

    Parse error: parse error, unexpected $end in index.php on line 71

    Okay now i have tried to fix this. Im guessing the problem lies around the $query and the “. But no matter what I alter to have it look like it should work, it just doesn’t. Any ideas?

  2. Alan

    If you look closely, about halfway down, there is a closing ” missing on one of the lines. On Dreamweaver, all the code below it lights up in the correct colours when you fix it.

  3. osobimbo

    sha1(‘$password’)” missing last ” in sentence

    not secretdb

    or

    name the script secretdb.php

    anyway

    with user testuser and password password still did not work

Leave a Reply

Your email address will not be published. Required fields are marked *