User registration and login using stored procedure in PHP
Hello, friends in this tutorial we will learn user registration and login using PHP stored procedure.
File structure for this tutorial
- config.php
- index.php
- check_availability.php
- login.php
- welcome.php
- logout.php
Structure of SQL table tblregistration
CREATE TABLE `tblregistration` ( `id` int(11) NOT NULL, `FullName` varchar(200) NOT NULL, `EmailId` varchar(200) NOT NULL, `Password` varchar(255) NOT NULL, `RegDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
config.php
Create DB configuration file using MySQLi extension. Provide credential as per your configuration
<?php
$con = mysqli_connect("localhost","root","","storeprocedure");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
?>
index.php
Create an HTML form for user registration.
<form class="form-horizontal" method="post">
<fieldset>
<div id="legend">
<legend align="center" style="font-size: 35px;">Register</legend>
</div>
<div class="control-group">
<!-- Fullname -->
<label class="control-label" for="fname">Full Name</label>
<div class="controls">
<input type="text" id="name" name="fname" placeholder="" class="input-xlarge" required>
</div>
</div>
<div class="control-group">
<!-- E-mail -->
<label class="control-label" for="email">E-mail</label>
<div class="controls">
<input type="email" id="email" name="email" placeholder="" class="input-xlarge" onBlur="checkAvailability()" required>
<span id="user-availability-status" style="font-size:12px;"></span>
</div>
</div>
<div class="control-group">
<!-- Password-->
<label class="control-label" for="password">Password</label>
<div class="controls">
<input type="password" id="password" name="password" placeholder="" class="input-xlarge" required>
</div>
</div>
<div class="control-group">
<!-- Button -->
<div class="controls">
<input class="btn btn-success" id="submit" type="submit" value='register' name="register">
</div>
</div>
<div class="control-group">
<div class="controls">
<p class="message">Already registered. <a href="login.php">login here</a></p>
</div>
</div>
</fieldset>
</form>
Jquery / Ajax for user email availability
<script>
function checkAvailability() {
$("#loaderIcon").show();
jQuery.ajax({
url: "check_availability.php",
data:'emailid='+$("#email").val(),
type: "POST",
success:function(data){
$("#user-availability-status").html(data);
$("#loaderIcon").hide();
},
error:function (){}
});
}
</script>
check_availability.php
In this page, we will check the user email availability. Create a store procedure with name check availability
Stored procedure code :
DELIMITER $
CREATE DEFINER=`root`@`localhost` PROCEDURE `checkavailbilty`(IN `email` VARCHAR(255))
NO SQL
SELECT EmailId FROM tblregistration WHERE EmailId=email$
DELIMITER ;
PHP Code for check_availability.php
<?php
require_once("config.php");
if(!empty($_POST["emailid"])) {
$email= $_POST["emailid"];
$result =mysqli_query($con,"call checkavailbilty('$email')");
$count=mysqli_num_rows($result);
if($count>0)
{
echo "<span style='color:red'> Email already exists .</span>";
echo "<script>$('#submit').prop('disabled',true);</script>";
} else{
echo "<span style='color:green'> Email available for Registration .</span>";
echo "<script>$('#submit').prop('disabled',false);</script>";
}
}
?>
Now create a store procedure for user registration.
Store procedure for user registration
DELIMITER $
CREATE DEFINER=`root`@`localhost` PROCEDURE `registration`(IN `fname` VARCHAR(200), IN `emailid` VARCHAR(200), IN `password` VARCHAR(255))
NO SQL
insert into tblregistration(FullName,EmailId,Password) VALUES(fname,emailid,password)$
DELIMITER ;
After creation of store procedure execute the store procedure.
<?php
include('config.php');
if(isset($_POST['register']))
{
$fname=$_POST['fname'];
$email=$_POST['email'];
$password=md5($_POST['password']);
// Excute the procedure
$query=mysqli_query($con,"call registration('$fname','$email','$password')");
if($query)
{
echo "<script>alert('Registration Successfull');</script>";
}
else
{
echo "<script>alert('Something went wrong. Please try again.');</script>";
}
}
?>
Here is the full code that we have written for registration (index.php):
<?php
include('config.php');
if(isset($_POST['register']))
{
$fname=$_POST['fname'];
$email=$_POST['email'];
$password=md5($_POST['password']);
$query=mysqli_query($con,"call registration('$fname','$email','$password')");
if($query)
{
echo "<script>alert('Registration Successfull');</script>";
}
else
{
echo "<script>alert('Something went wrong. Please try again.');</script>";
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<!-- This file has been downloaded from Bootsnipp.com. Enjoy! -->
<title>Registration using Store Procedure</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="https://netdna.bootstrapcdn.com/twitter-bootstrap/2.3.2/css/bootstrap-combined.min.css" rel="stylesheet">
<script src="https://code.jquery.com/jquery-1.11.1.min.js"></script>
<script src="https://netdna.bootstrapcdn.com/twitter-bootstrap/2.3.2/js/bootstrap.min.js"></script>
<script>
function checkAvailability() {
$("#loaderIcon").show();
jQuery.ajax({
url: "check_availability.php",
data:'emailid='+$("#email").val(),
type: "POST",
success:function(data){
$("#user-availability-status").html(data);
$("#loaderIcon").hide();
},
error:function (){}
});
}
</script>
</head>
<body>
<form class="form-horizontal" method="post">
<fieldset>
<div id="legend">
<legend align="center" style="font-size: 35px;">Register</legend>
</div>
<div class="control-group">
<!-- Fullname -->
<label class="control-label" for="fname">Full Name</label>
<div class="controls">
<input type="text" id="name" name="fname" placeholder="" class="input-xlarge" required>
</div>
</div>
<div class="control-group">
<!-- E-mail -->
<label class="control-label" for="email">E-mail</label>
<div class="controls">
<input type="email" id="email" name="email" placeholder="" class="input-xlarge" onBlur="checkAvailability()" required>
<span id="user-availability-status" style="font-size:12px;"></span>
</div>
</div>
<div class="control-group">
<!-- Password-->
<label class="control-label" for="password">Password</label>
<div class="controls">
<input type="password" id="password" name="password" placeholder="" class="input-xlarge" required>
</div>
</div>
<div class="control-group">
<!-- Button -->
<div class="controls">
<input class="btn btn-success" id="submit" type="submit" value='register' name="register">
</div>
</div>
<div class="control-group">
<div class="controls">
<p class="message">Already registered. <a href="login.php">login here</a></p>
</div>
</div>
</fieldset>
</form>
</body>
</html>
login .php
Create a login form user login.
<div class="login-page">
<div class="form">
<form class="login-form" method="post">
<input type="text" placeholder="Email id" name="useremail" required />
<input type="password" placeholder="password" name="password" required />
<button type="submit" name="login">login</button>
<p class="message">Not registered? <a href="index.php">Create an account</a></p>
</form>
</div>
</div>
Now create a store procedure for login with name login.
Login store procedure :
DELIMITER $
CREATE DEFINER=`root`@`localhost` PROCEDURE `login`(IN `useremail` VARCHAR(255), IN `password` VARCHAR(255))
NO SQL
SELECT EmailId,Password from tblregistration where EmailId=useremail and Password=password$
DELIMITER ;
Now execute the login store procedure
<?php
session_start();
include('config.php');
if(isset($_POST['login']))
{
$email=$_POST['useremail'];
$password=md5($_POST['password']);
$query=mysqli_query($con,"call login('$email','$password')");
$num=mysqli_fetch_array($query);
if($num>0)
{
$_SESSION['login']=$_POST['useremail'];
header("location:welcome.php");
}
else
{
$_SESSION['login']=$_POST['useremail'];
echo "<script>alert('Invalid login details');</script>";
$extra="login.php";
}
}
?>
Here is the full code that we have written for login(login.php):
<?php
session_start();
include('config.php');
if(isset($_POST['login']))
{
$email=$_POST['useremail'];
$password=md5($_POST['password']);
$query=mysqli_query($con,"call login('$email','$password')");
$num=mysqli_fetch_array($query);
if($num>0)
{
$_SESSION['login']=$_POST['useremail'];
header("location:welcome.php");
}
else
{
$_SESSION['login']=$_POST['useremail'];
echo "<script>alert('Invalid login details');</script>";
$extra="login.php";
}
}
?>
sasa
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Login Store Procedure</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/css/bootstrap.min.css" rel="stylesheet">
<style >
@import url(https://fonts.googleapis.com/css?family=Roboto:300);
.login-page {
width: 360px;
padding: 8% 0 0;
margin: auto;
}
.form {
position: relative;
z-index: 1;
background: #FFFFFF;
max-width: 360px;
margin: 0 auto 100px;
padding: 45px;
text-align: center;
box-shadow: 0 0 20px 0 rgba(0, 0, 0, 0.2), 0 5px 5px 0 rgba(0, 0, 0, 0.24);
}
.form input {
font-family: "Roboto", sans-serif;
outline: 0;
background: #f2f2f2;
width: 100%;
border: 0;
margin: 0 0 15px;
padding: 15px;
box-sizing: border-box;
font-size: 14px;
}
.form button {
font-family: "Roboto", sans-serif;
text-transform: uppercase;
outline: 0;
background: #4CAF50;
width: 100%;
border: 0;
padding: 15px;
color: #FFFFFF;
font-size: 14px;
-webkit-transition: all 0.3 ease;
transition: all 0.3 ease;
cursor: pointer;
}
.form button:hover,.form button:active,.form button:focus {
background: #43A047;
}
.form .message {
margin: 15px 0 0;
color: #b3b3b3;
font-size: 12px;
}
.form .message a {
color: #4CAF50;
text-decoration: none;
}
.form .register-form {
display: none;
}
.container {
position: relative;
z-index: 1;
max-width: 300px;
margin: 0 auto;
}
.container:before, .container:after {
content: "";
display: block;
clear: both;
}
.container .info {
margin: 50px auto;
text-align: center;
}
.container .info h1 {
margin: 0 0 15px;
padding: 0;
font-size: 36px;
font-weight: 300;
color: #1a1a1a;
}
.container .info span {
color: #4d4d4d;
font-size: 12px;
}
.container .info span a {
color: #000000;
text-decoration: none;
}
.container .info span .fa {
color: #EF3B3A;
}
body {
background: #76b852; /* fallback for old browsers */
background: -webkit-linear-gradient(right, #76b852, #8DC26F);
background: -moz-linear-gradient(right, #76b852, #8DC26F);
background: -o-linear-gradient(right, #76b852, #8DC26F);
background: linear-gradient(to left, #76b852, #8DC26F);
font-family: "Roboto", sans-serif;
-webkit-font-smoothing: antialiased;
-moz-osx-font-smoothing: grayscale;
}
</style>
<script src="http://code.jquery.com/jquery-1.11.1.min.js"></script>
<script src="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/js/bootstrap.min.js"></script>
</head>
<body>
<div class="login-page">
<div class="form">
<form class="login-form" method="post">
<input type="text" placeholder="Email id" name="useremail" required />
<input type="password" placeholder="password" name="password" required />
<button type="submit" name="login">login</button>
<p class="message">Not registered? <a href="index.php">Create an account</a></p>
</form>
</div>
</div>
<script >
$('.message a').click(function(){
$('form').animate({height: "toggle", opacity: "toggle"}, "slow");
});
</script>
</body>
</html>
welcome.php
After login user will redirect to welcome.php page. We will validate this page with the session if any user try to access this page(welcome.php) directly then user will redirect to login.php page.
<?php
session_start();
include('config.php');
//validating session
if(strlen($_SESSION['login'])==0)
{
header('location:login.php');
}
else{
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<!-- This file has been downloaded from Bootsnipp.com. Enjoy! -->
<title>Welcome Page</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="http://netdna.bootstrapcdn.com/bootstrap/3.0.0/css/bootstrap.min.css" rel="stylesheet">
<style >
body { background-image: url(data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABoAAAAaCAYAAACpSkzOAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAABZ0RVh0Q3JlYXRpb24gVGltZQAxMC8yOS8xMiKqq3kAAAAcdEVYdFNvZnR3YXJlAEFkb2JlIEZpcmV3b3JrcyBDUzVxteM2AAABHklEQVRIib2Vyw6EIAxFW5idr///Qx9sfG3pLEyJ3tAwi5EmBqRo7vHawiEEERHS6x7MTMxMVv6+z3tPMUYSkfTM/R0fEaG2bbMv+Gc4nZzn+dN4HAcREa3r+hi3bcuu68jLskhVIlW073tWaYlQ9+F9IpqmSfq+fwskhdO/AwmUTJXrOuaRQNeRkOd5lq7rXmS5InmERKoER/QMvUAPlZDHcZRhGN4CSeGY+aHMqgcks5RrHv/eeh455x5KrMq2yHQdibDO6ncG/KZWL7M8xDyS1/MIO0NJqdULLS81X6/X6aR0nqBSJcPeZnlZrzN477NKURn2Nus8sjzmEII0TfMiyxUuxphVWjpJkbx0btUnshRihVv70Bv8ItXq6Asoi/ZiCbU6YgAAAABJRU5ErkJggg==);}
.error-template {padding: 40px 15px;text-align: center;}
.error-actions {margin-top:15px;margin-bottom:15px;}
.error-actions .btn { margin-right:10px; }
</style>
<script src="http://code.jquery.com/jquery-1.11.1.min.js"></script>
<script src="http://netdna.bootstrapcdn.com/bootstrap/3.0.0/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<div class="error-template">
<?php
$userid=$_SESSION['login'];
$query=mysqli_query($con,"select FullName from tblregistration where EmailId='$userid'");
while($row=mysqli_fetch_array($query))
{?>
<h1>Welcome : <?php echo htmlentities($row['FullName']);?></h1>
<?php } ?>
<div class="error-actions">
<a href="logout.php" class="btn btn-primary btn-lg"><span class="glyphicon glyphicon-log-out"></span>
Logout </a>
</div>
</div>
</div>
</div>
</div>
</body>
</html>
<?php } ?>
logout.php
For destroying login session. session_destroy will destroy all the active sessions.
<?php session_start(); session_destroy(); ?> <script language="javascript"> document.location="login.php"; </script>
Download source (User Signup and Login using Stored Procedure in PHP)Size: 7.05Version: V 1.0
Download source (User Signup and Login using Stored Procedure in PHP)
Size: 7.05
Version: V 1.0
