User registration and login using stored procedure in PHP

by Anuj Kumar · May 14, 2018

Helloo 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 a html form for user registration .

<legend align="center" style="font-size: 35px;">Register</legend>

</div>

</div>

</div>

<label class="control-label" for="password">Password</label>

</div>

</div>

<p class="message">Already registered. <a href="login.php">login here</a></p>

</div>

</fieldset>

</form>

Jquery / Ajax for user email availability

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
Store procedure code :

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `checkavailbilty`(IN `email` VARCHAR(255))

NO SQL

SELECT EmailId FROM tblregistration WHERE EmailId=email$$

DELIMITER ;

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>

<head>

<title>Registration using Store Procedure</title>

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>

<legend align="center" style="font-size: 35px;">Register</legend>

</div>

</div>

</div>

<label class="control-label" for="password">Password</label>

</div>

</div>

<p class="message">Already registered. <a href="login.php">login here</a></p>

</div>

</fieldset>

</form>

</body>

</html>

login .php

Create a login form user login.

<button type="submit" name="login">login</button>

<p class="message">Not registered? <a href="index.php">Create an account</a></p>

</form>

</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):

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

<?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>

<head>

<title>Login Store Procedure</title>

@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>

</head>

<body>

<button type="submit" name="login">login</button>

<p class="message">Not registered? <a href="index.php">Create an account</a></p>

</form>

</div>

$('.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>

<head>

<title>Welcome Page</title>

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>