Simple PHP Login System Using MySQL and jQuery AJAX

This tutorial help to create a simple PHP script for the login system using MySQL and ajax. The authentication functionality is a very important feature of any web project.

The Login system of the user helps to restrict the access of sensitive information or pages from unauthorized user.

This PHP tutorial will handle login functionality, Creating a login form using Bootstrap CSS and MySQL to validate user records from the database.

You can also get a login system in PHP CMS like WordPress, Joomla and drupal etc.

You can check other recommended tutorial of User Authentication in PHP,

• Simple Example of Laravel 5 Login System Using Sentry
• Website Login With Google or Yahoo/WordPress/OAL Account
• Email Verification With Activation Token In Laravel 5 Using Sentry
• Live Username Availability using PHP & jQuery

I will use following files for this user login tutorial,

• index.php: This file is main entry file of php login script application.
• response.php: This file will contain all login and logout method.
• dashbord.php: This file will use to show welcome dashboard page once user logged-in.
• partials/: This folder will have partial file like header.php and footer.php file.
• connect/: This folder will contain MySQL db connection file.
• libs/: This folder will have all dependencies files.

We need to create above files and folder structure into 'php_ajax_login_with_php_jquery' folder, where php_ajax_login_with_php_jquery is the name of login php script application.

Header and Footer Partial File

I will create partials/header.php file and added all dependencies libs files into header section of this file.

<?php session_start();?>         
<script type="text/javascript" charset="utf8" src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-2.0.3.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery-validate/1.17.0/jquery.validate.min.js"></script>
<script type="text/javascript" src="libs/common.js"></script></pre>

<div class="container">
    
</div>

I’ve also started the PHP session at the top of the file, so all pages that include the header.php file will now have PHP session information.

I will create partials/footer.php file and add footer information of the login system application into this file.

@copyright 2021

Create MySQL Database and table For PHP Login

We will create 'test' database into MySQL database server and create table 'tbl_users' using below MySQL script.You can also use MySQL command line to execute SQL file.

--
-- Table structure for table `tbl_users`
--

CREATE TABLE IF NOT EXISTS `tbl_users` (
`id` int(11) NOT NULL,
  `user` varchar(255) DEFAULT NULL,
  `password` varchar(100) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `profile_photo` varchar(200) DEFAULT NULL,
  `active` tinyint(1) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

Now, I will create a sample user entry within the above MySQL table, You need to run the below query into 'test' database.

//create sample data
INSERT INTO `tbl_users` (`id`, `user`, `password`, `email`, `profile_photo`, `active`) VALUES
(1, 'phpflow', 'cc03e747a6afbbcbf8be7668acfebee5', 'test@phpflow.com', NULL, 1);

I have created a new record into 'tbl_users' table which has sample user credentials to login into php login system.The password is encrypted using php md5() method.

MySQL Database Connection using PHP mysqli

I will create connect/db_cls_connect.php file and added below code into this file.

<?php
session_start();
Class dbObj{
 /* Database connection start */ var $servername = "localhost";
 var $username = "root";
 var $password = "";
 var $dbname = "test";
 var $conn;
 function getConnstring() {
  $con = mysqli_connect($this->servername, $this->username, $this->password, $this->dbname) or die("Connection failed: " . mysqli_connect_error());

  /* check connection */  if (mysqli_connect_errno()) {
   printf("Connect failed: %s\n", mysqli_connect_error());
   exit();
  } else {
   $this->conn = $con;
  }
  return $this->conn;
 }
}

?>

As you can see, I have passed below MySQL database parameters.

• $servername : MySQL server host //localhost
• $username : MySQL database user name //root
• $password : MySQL database pass
• $dbname : MySQL database name //test

PHP login Form Validation Using jQuery Validation

I am using the jQuery validation plugin to validate login form input fields, jQuery validation is an awesome jQuery plugin to validate HTML form fields.

Bootstrap Login form has username and password fields that need to validate and show error messages using jQuery hide/show class.

There are submitHandler method that will use to submit login form using AJAX to server side.

I will create a common.js file into libs/ folder and added below code into this file.

$(document).ready(function(){
 /* handling form validation */ $("#login-form").validate({
  rules: {
   password: {
    required: true,
   },
   username: {
    required: true,
    email: true
   },
  },
  messages: {
   password:{
     required: "Please enter your password"
    },
   username: "Please enter your email address",
  },
  submitHandler: submitForm 
 }); 

Since I’m using jQuery, all validation processes are handled on the client-side, I’ve created rules for each input field and passed messages on failed validation. After successfully validating all login form fields, I forwarded the submitForm method to the submitHandler.

PHP Login Using MySQL and jQuery AJAX

I have created a MySQL database connection and integrated a jQuery validation plugin to validate the client-side bootstrap login form, Now I will create a login form using Bootstrap and defined input fields.

Step 1: Create login html view using bootstrap 3.

<div id="login-box" class="row box">
   <div class="col-md-6 col-md-offset-3">
      <div class="panel panel-login">
         <div class="panel-body">
            <div class="row">
               <div class="col-lg-12">
                  <div id="error" class="alert alert-danger" style="display: none;" role="alert">...</div>
                  <form id="login-form" style="display: block;" role="form" method="post" name="login_form">
                     <div class="form-group"><input id="username" class="form-control" tabindex="1" name="username" required="" type="email" value="" placeholder="Username"></div>
                     <div class="form-group"><input id="password" class="form-control" tabindex="2" name="password" required="" type="password" placeholder="Password"></div>
                     <div class="col-xs-12 form-group pull-right"><button id="login-submit" class="form-control btn btn-primary" tabindex="4" name="login-submit" type="submit"> <i id="spinner" class="icon-spin icon-refresh"></i> Log In </button></div>
                  </form>
               </div>
            </div>
         </div>
      </div>
   </div>
</div>

I have defined a div(#error) which will use to show an error message on the wrong credential supplied.I will use form id(#login-form) to validate input fields and button id for show sign-in process.

Step 2: Create submit login method into common.js file.

function submitForm() {  
  var data = $("#login-form").serialize();
  $.ajax({    
   type : 'POST',
   url  : 'response.php?action=login',
   data : data,
   beforeSend: function(){ 
    $("#error").fadeOut();
    $("#login_button").html('<span class="glyphicon glyphicon-transfer"></span> &nbsp; sending ...');
   },
   success : function(response){   
    if($.trim(response) === "1"){
     console.log('dddd');         
     $("#login-submit").html('Signing In ...');
     setTimeout(' window.location.href = "dashboard.php"; ',2000);
    } else {         
     $("#error").fadeIn(1000, function(){      
      $("#error").html(response).show();
     });
    }
   }
  });
  return false;
 }

This method using submitHandler method of jQuery validation, passed AJAX url 'response.php?action=login', So I need to create a method into response.php file to handle login request and send response.

Step 3: Created login method into response.php file.

//include connection file 
include_once("connect/db_cls_connect.php");
$db = new dbObj();
$connString =  $db->getConnstring();

$params = $_REQUEST;
$action = $params['action'] !='' ? $params['action'] : '';
$empCls = new Employee($connString);

switch($action) {
 case 'login':
 $empCls->login();
 break;
 default:
 return;
 class Employee {
 protected $conn;
 protected $data = array();
 function __construct($connString) {
  $this->conn = $connString;
 }
 
 function login() {
  if(isset($_POST['login-submit'])) {
   $user_email = trim($_POST['username']);
   $user_password = trim($_POST['password']);
   $sql = "SELECT id, user, password, email FROM tbl_users WHERE email='$user_email'";
   $resultset = mysqli_query($this->conn, $sql) or die("database error:". mysqli_error($this->conn));
   $row = mysqli_fetch_assoc($resultset);
   if(md5($user_password) == $row['password']){
    echo "1";
    $_SESSION['user_session'] = $row['user'];
   } else {
    echo "Ohhh ! Wrong Credential."; // wrong details
   }
  }
 }
}
}

PHP login() method get all passed login input fields params and search into MySQL table using given username and password.

If the credential is correct then we will redirect user to dashboard page otherwise send error message. I will create later dashboard.php into this login php tutorial.

PHP Logout Using PHP Session

We have created php login functionality using AJAX, now will create dashboard.php file that will show on the successful login of the user.The Logout() method will handle logout functionality using PHP Session.

We have stored the username at the time of sign-in into the session and destroyed the PHP session using logout functionality.

Step 1: Created dashboard.php file using bootstrap.

<?php
   include('partials/header.php');
   ?>
<div class="container">
   <div class="row">
      <h1>Creative User Profile 
         <small>#User-Interface #User #Profile #jquery #Social #contact #accordion</small>
      </h1>
      <!-- Container -->
      <ul id="accordion" class="accordion">
         <li>
            <div class="col col_4 iamgurdeep-pic">
               <img class="img-responsive iamgurdeeposahan" alt="iamgurdeeposahan" src="https://cdn-images-1.medium.com/max/2000/1*k_HvuYIVDODaXLxeFAs2uQ.jpeg">
            </div>
            <div class="username">
               <h2>
                  Hi <!--?php echo $_SESSION['user_session']; ?--><small><i class="fa fa-map-marker"></i> India </small>
               </h2>
            </div>
         </li>
      </ul>
   </div>
   <ul class="submenu">
      <li><a href="response.php?action=logout" class="btn btn-default btn-sm">
         Logout
         </a>
      </li>
   </ul>
</div>
<?php
   include('partials/footer.php')
   ?>

I have added 'response.php?action=logout' url into logout href property, So now we will create a method into response.php file to handle logout action.

Step 3: Created logout method into response.php file.

case 'logout':
 $empCls->logout();
 break;
 function logout() {
  unset($_SESSION['user_session']);
  if(session_destroy()) {
   header("Location: index.php");
  }
 }

The first three line will add into switch method of response.php and call logout method when the action value is ‘logout’.

Conclusion :

We have created a simple login UI using bootstrap 3 and HTML. Also created login and logout method to handle PHP login system functionality using PHP and MySQL. jQuery validation plugin used to handle client-side login page validation. PHP Session used to store user information and destroy sessions at the time of user logout. I will later on share the registration process of the login system.

You can download source code and Demo from below link.