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:

$name = $_POST['name'];
$password = $_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">
<th> Username </th>
<td> <input type="text" name="username"> </td>
<th> Password </th>
<td> <input type="password" name="password"> </td>
<td colspan="2" align="center">
<input type="submit" value="Log In">
// connect to database server
$mysql = mysqli_connect( 'localhost', 'webauth', 'webauth' );
echo 'Cannot connect to database.';
// select the database
$selected = mysqli_select_db( $mysql, 'auth' );
echo 'Cannot select database.';
// 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 );
echo 'Cannot run query.';
$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.';
// 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. 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. 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. Also, I think this script should be split across two files as I’m getting secretdb.php not found. Needs a closer look…

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

    not secretdb


    name the script secretdb.php


    with user testuser and password password still did not work

Leave a Reply to Raj Cancel reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.