helping build websites
PHP User Authentication with MySQL
by Vincy. Last modified on October 10th, 2021.
User authentication is a process of validating users with some keys, tokens, or any other credentials. If the user gives correct credentials then the authentication process will be successful.
After successful authentication, the users will be allowed to the system as authenticated users.
In this tutorial, we are going to create a PHP user authentication system with MySQL database. It will be easy to understand this example code. By reading this article, you will learn how to code PHP user authentication.
Also, I have added the downloadable source code at the end of this article.
About this example
In this user authentication example, it has a login panel to let users enter their login details. It requests users to enter their username and password to authenticate. By submitting these login credentials, it will be posted to a PHP page.
In PHP it verifies the entered detail with the registered users’ database. It shows how to prepare the database query and execute it to check and verify the user’s data with the database.
Once a match is found, the user will be considered authenticated and authorized. After successful login, the authenticated user will be allowed to enter into the application.
Steps to create a user login authentication system in PHP
- Create a MySQL database with users table.
- Create a user login panel to submit login details to PHP.
- Generate query to compare user login details with the MySQL user database.
1) Create a MySQL Database with Users Table.
For creating a database and the database tables you can run the MySQL CREATE statement. Instead, you can go with any database client like SQLYog, PHPMyAdmin. I am using PHPMyAdmin for creating the database and table structure that is used for the MySQL examples.
In this example, I created a database named as payload for creating users table. The following screenshot shows the users table structure with data.
2) Create User Login Panel.
The following HTML code is used to show the user login panel to the users to enter their authentication details. It has two input fields for getting username and password from the users.
Once the user entered their login details and submitted the form, the username and password data will be posted to the PHP to process authentication with MySQL database.
<form name="frmUser" method="post" action="">
<div class="message"><?php if($message!="") { echo $message; } ?></div>
<table border="0" cellpadding="10" cellspacing="1" width="500" align="center" class="tblLogin">
<tr class="tableheader">
<td align="center" colspan="2">Enter Login Details</td>
</tr>
<tr class="tablerow">
<td>
<input type="text" name="userName" placeholder="User Name" class="login-input"></td>
</tr>
<tr class="tablerow">
<td>
<input type="password" name="password" placeholder="Password" class="login-input"></td>
</tr>
<tr class="tableheader">
<td align="center" colspan="2"><input type="submit" name="submit" value="Submit" class="btnSubmit"></td>
</tr>
</table>
</form>
These styles are added for the user authentication form elements by including a CSS file. The CSS provides a minimal look and feel to the PHP user authentication UI. It is just a skeleton and could be changed easily for the application theme.
body{
font-family: calibri;
}
.tblLogin {
border: #95bee6 1px solid;
background: #d1e8ff;
border-radius: 4px;
}
.tableheader { font-size: 24px; }
.tableheader td { padding: 20px; }
.tablerow td { text-align:center; }
.message {
color: #FF0000;
font-weight: bold;
text-align: center;
width: 100%;
}
.login-input {
border: #CCC 1px solid;
padding: 10px 20px;
}
.btnSubmit {
padding: 10px 20px;
background: #2c7ac5;
border: #d1e8ff 1px solid;
color: #FFF;
}
3) Generate Query to Compare User Input with the Database.
In the following PHP code, it checks the $_POST global array length before executing the authentication code block. Once the user authentication form is submitted, then this global array will contain the values of the form input fields.
The PHP authentication code includes DataSource class at the beginning of the program. It connects the MySQL database by specifying the configurations to get the connection object. It uses MySQLi with prepared statements to execute authentication queries.
After receiving user authentication details in PHP, it compares the form data with the user database by executing a query by using the connection object.
The query binds the username entered by the user via HTML form. Then, it verifies the password hash with the entered password to return the authentication results.
If a match is found, it means the user is genuine who registered already with the system. So, the authentication code will allow the user to proceed further.
No matter whether the authentication is cleared or not. Anyhow this code will acknowledge the user by displaying success or warning based on the result of the authentication process.
<?php
namespace Phppot;
use \Phppot\DataSource;
$message = "";
if (count($_POST) > 0) {
$isSuccess = 0;
require_once __DIR__ . '/DataSource.php';
$conn = new DataSource();
$query = 'SELECT * FROM users WHERE userName= ?';
$paramType = 's';
$paramValue = array(
$_POST["userName"]
);
$result = $conn->select($query, $paramType, $paramValue);
if (! empty($result)) {
$hashedPassword = $result[0]["password"];
if (password_verify($_POST["password"], $hashedPassword)) {
$isSuccess = 1;
}
}
if ($isSuccess == 0) {
$message = "Invalid Username or Password!";
} else {
header("Location: ./success-message.php");
}
}
?>
DataSource.php
This is a common class that handles database connections and query execution processes. You can use this class in other code or applications.
It has the class constants to configure the database details. Also, it provides handlers to establishes connections and bind query params.
<?php
/**
* Copyright (C) Phppot
*
* Distributed under 'The MIT License (MIT)'
* In essense, you can do commercial use, modify, distribute and private use.
* Though not mandatory, you are requested to attribute Phppot URL in your code or website.
*/
namespace Phppot;
/**
* Generic datasource class for handling DB operations.
* Uses MySqli and PreparedStatements.
*
* @version 2.6 - recordCount function added
*/
class DataSource
{
const HOST = 'localhost';
const USERNAME = 'root';
const PASSWORD = '';
const DATABASENAME = 'blog_eg';
private $conn;
/**
* PHP implicitly takes care of cleanup for default connection types.
* So no need to worry about closing the connection.
*
* Singletons not required in PHP as there is no
* concept of shared memory.
* Every object lives only for a request.
*
* Keeping things simple and that works!
*/
function __construct()
{
$this->conn = $this->getConnection();
}
/**
* If connection object is needed use this method and get access to it.
* Otherwise, use the below methods for insert / update / etc.
*
* @return \mysqli
*/
public function getConnection()
{
$conn = new \mysqli(self::HOST, self::USERNAME, self::PASSWORD, self::DATABASENAME);
if (mysqli_connect_errno()) {
trigger_error("Problem with connecting to database.");
}
$conn->set_charset("utf8");
return $conn;
}
/**
* To get database results
*
* @param string $query
* @param string $paramType
* @param array $paramArray
* @return array
*/
public function select($query, $paramType = "", $paramArray = array())
{
$stmt = $this->conn->prepare($query);
if (! empty($paramType) && ! empty($paramArray)) {
$this->bindQueryParams($stmt, $paramType, $paramArray);
}
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$resultset[] = $row;
}
}
if (! empty($resultset)) {
return $resultset;
}
}
/**
* To insert
*
* @param string $query
* @param string $paramType
* @param array $paramArray
* @return int
*/
public function insert($query, $paramType, $paramArray)
{
$stmt = $this->conn->prepare($query);
$this->bindQueryParams($stmt, $paramType, $paramArray);
$stmt->execute();
$insertId = $stmt->insert_id;
return $insertId;
}
/**
* To execute query
*
* @param string $query
* @param string $paramType
* @param array $paramArray
*/
public function execute($query, $paramType = "", $paramArray = array())
{
$stmt = $this->conn->prepare($query);
if (! empty($paramType) && ! empty($paramArray)) {
$this->bindQueryParams($stmt, $paramType, $paramArray);
}
$stmt->execute();
}
/**
* 1.
* Prepares parameter binding
* 2. Bind prameters to the sql statement
*
* @param string $stmt
* @param string $paramType
* @param array $paramArray
*/
public function bindQueryParams($stmt, $paramType, $paramArray = array())
{
$paramValueReference[] = & $paramType;
for ($i = 0; $i < count($paramArray); $i ++) {
$paramValueReference[] = & $paramArray[$i];
}
call_user_func_array(array(
$stmt,
'bind_param'
), $paramValueReference);
}
/**
* To get database results
*
* @param string $query
* @param string $paramType
* @param array $paramArray
* @return array
*/
public function getRecordCount($query, $paramType = "", $paramArray = array())
{
$stmt = $this->conn->prepare($query);
if (! empty($paramType) && ! empty($paramArray)) {
$this->bindQueryParams($stmt, $paramType, $paramArray);
}
$stmt->execute();
$stmt->store_result();
$recordCount = $stmt->num_rows;
return $recordCount;
}
}
Database Script
Import this database script before running this example in your development environment.
This SQL script contains the user database structure. Additionally, it provides a sample record to test with valid authentication details.
Once you set up this example in your environment, run login.php and try below login details.
Username: admin
Password: admin
--
-- Table structure for table `users`
--
CREATE TABLE `users` (
`userId` int(8) NOT NULL,
`userName` varchar(55) NOT NULL,
`password` varchar(255) NOT NULL,
`displayName` varchar(55) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `users`
--
INSERT INTO `users` (`userId`, `userName`, `password`, `displayName`) VALUES
(1, 'admin', '$2a$10$0FHEQ5/cplO3eEKillHvh.y009Wsf4WCKvQHsZntLamTUToIBe.fG', 'Admin');
--
-- Indexes for table `users`
--
ALTER TABLE `users`
ADD PRIMARY KEY (`userId`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
MODIFY `userId` int(8) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
Hi, I’m Vincy. I
help build websites and I’m available for freelance work.
Can you show sql structure ?
Thanx!
Hi Vladimir,
Find sql structure and data with the download link.
Please provide the sample as zip file for download. Thanks for this nice tutorial.
Hi Pranav,
Thanks.
I have added download link with this article to get example source code zip.
Miss Connection doesn’t establish!please help me.
Thanks
nice tutorial ,really its very helpful…..
Thank you Rohit.
you have to modify “userName” in WHERE.
very helpfull
Thank you Sige.
This worked for me connecting AWS EC2 Apache web server to RDS MySQL. Thank you very much for the tutorial
Welcome Philips. Great to know.