MySQL is a popular choice as a backend database for PHP powered web applications. In this chapter, we shall learn to develop a login page for a PHP application that authenticates the given username and password.
You should have a web server having PHP and MySQL installed for experimenting with the example discussed in this chapter. The bundled binaries of Apache, PHP and MySQL (MariaDB) in the form of XAMPP for your operating system can be easily installed.
Before running the example code, you should have a MySQL database called mydb in which there must be a table called admin. You can use following SQL script to create the table and insert a test data
usemydb;CREATETABLEadmin(usernamevarchar(10)NOTNULL,passcodevarchar(10)NOTNULL)ENGINE=InnoDB DEFAULTCHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;INSERTINTOadmin(username,passcode)VALUES('guest','abc123'),('manager','secret'),('user','test');ALTERTABLEadminADDPRIMARYKEY(username);COMMIT;The first part of PHP login application is to establish database connection object. We use myqli API to obtain connection object. Save following code as “config.php”
Config.php
<?php
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '');
define('DB_DATABASE', 'mydb');
$db = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE);
?>This PHP script is called inside the login script. It presents the user with a HTML form to enter username and password. In case the form is submitted, PHP runs a SELECT query to retrieve a row in the admin table where the username and passcode matches with the user inputs.
$myusername=mysqli_real_escape_string($db,$_POST['username']);$mypassword=mysqli_real_escape_string($db,$_POST['password']);$sql="SELECT * FROM admin WHERE username = '$myusername' and passcode = '$mypassword'";$result=mysqli_query($db,$sql);$row=mysqli_num_rows($result);If the row count is one, it indicates that the username and the password entered matches. The username is save to the $_SESSION variable and the browser is directed to welcome.php script.
Login.php
Save the following code as “login.php” −
Open Compiler
<?php
include("config.php");
session_start();
$error='';
if($_SERVER["REQUEST_METHOD"] == "POST") {
// username and password sent from form
$myusername = mysqli_real_escape_string($db,$_POST['username']);
$mypassword = mysqli_real_escape_string($db,$_POST['password']);
$sql = "SELECT * FROM admin WHERE username = '$myusername' and passcode = '$mypassword'";
$result = mysqli_query($db,$sql);
$row = mysqli_num_rows($result);
$count = mysqli_num_rows($result);
if($count == 1) {
// session_register("myusername");
$_SESSION['login_user'] = $myusername;
header("location: welcome.php");
} else {
$error = "Your Login Name or Password is invalid";
}
}
?><html><head><title>Login Page</title><style type = "text/css">
body {
font-family:Arial, Helvetica, sans-serif;
font-size:14px;
}
label {
font-weight:bold;
width:100px;
font-size:14px;
}
.box {
border:#666666 solid 1px;
}
</style></head><body bgcolor = "#FFFFFF"><div align = "center"><div style = "width:300px; border: solid 1px #333333; " align = "left"><div style = "background-color:#333333; color:#FFFFFF; padding:3px;"><b>Login</b></div><div style = "margin:30px"><form action = "" method = "post"><label>UserName :</label><input type = "text" name = "username" class = "box"/><br /><br /><label>Password :</label><input type = "password" name = "password" class = "box" /><br/><br /><input type = "submit" value = " Submit "/><br /></form><div style = "font-size:11px; color:#cc0000; margin-top:10px"><?php echo $error; ?></div></div></div></div></body></html>Learn MySQL in-depth with real-world projects through our MySQL certification course. Enroll and become a certified expert to boost your career.
Session.php
The following is the session.php code file. It checks if the session variable is set; then the user credentials will be assigned to the $login_session variable. If not, the user is redirected back to the login.php file.
<?php
// Start the session
session_start();
if(!isset($_SESSION['login_user'])){
header("location: login.php");
die();
}
$login_session = $_SESSION['login_user'];
?>Welcome.php
The “welcome.php” script gets invoked when the user is authenticated. It reads the session variable to display a welcome message.
<?php
include('session.php');
?><html><head><title>Welcome </title></head><body><h1>Welcome <?php echo $login_session; ?></h1><h2><a href = "logout.php">Sign Out</a></h2></body></html>Logout.php
Finally, the logout script removes the destroys the session and redirects the user to the login page.
<?php
session_start();
if(session_destroy()) {
header("Location: login.php");
}
?>To start the login application, visit “http://localhost/login.php”
Enter the username and password. On pressing the submit button, these inputs are checked against the rows in admin table. On success, you get the following message −
If the query doesn’t fetch any matching row, the error message is displayed as follows −
