Signup and Login operation using PDO
In the last PDO tutorial we about PHP CRUD operation using PDO Extension. In this tutorial, we will learn about Signup and login operations using PHP.
File structure for Sign up and Login operation
signup.php– For user signup or user registration
check_availability.php– For username and email-id availability
index.php– For User login
welcome.php– After login user redirect to this page
logout.php– Logout page
config.php– Database connection file
pdosignup.sql– Sql table structure for signup
Step 1– Create a database
Open browser type http://localhost/phpmyadmin, create a database named ‘pdosignup’ . After creating database run the sql script or import the sql file given inside the package.
Structure for sql table userdata
CREATE TABLE IF NOT EXISTS `userdata` ( `id` int(11) NOT NULL, `FullName` varchar(200) DEFAULT NULL, `UserName` varchar(12) DEFAULT NULL, `UserEmail` varchar(200) DEFAULT NULL, `UserMobileNumber` varchar(10) DEFAULT NULL, `LoginPassword` varchar(255) DEFAULT NULL, `RegDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1; -- Indexes for table `userdata` -- ALTER TABLE `userdata` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `userdata` -- ALTER TABLE `userdata` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=8; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Step 2– Create a database configuration file config.php
<?php
// DB credentials.
define('DB_HOST','localhost'); // Host name
define('DB_USER','root'); // db user name
define('DB_PASS',''); // db user password name
define('DB_NAME','pdosignup'); // db name
// Establish database connection.
try
{
$dbh = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME,DB_USER, DB_PASS);
}
catch (PDOException $e)
{
exit("Error: " . $e->getMessage());
}
?>
Step 3– Create a Signup or Registration form signup.php
step3.1 HTML form with html5 validation pattern. In this form every input filed valid with HTML5 pattern validation
Fullname can contain any letters only with spaces
pattern=”[a-zA-Z\s]+”
Username can contain any letters or numbers, without spaces 6 to 12 chars
pattern=”^[a-zA-Z][a-zA-Z0-9-_.]{5,12}$”
email accepts valid email address
type=“email”
Mobile Number Contain only 10 digit numeric values
pattern=”[0-9]{10}” maxlength=”10″
Password should be at least 4 characters
pattern=”^\S{4,}$”
onchange=”this.setCustomValidity(this.validity.patternMismatch ? ‘Must have at least 4 characters’ : ”); if(this.checkValidity()) form.password_two.pattern = this.value;”
Confirm Password should be same as password
pattern=”^\S{4,}$”
onchange=”this.setCustomValidity(this.validity.patternMismatch ? ‘Please enter the same Password as above’ : ”)”
<form class="form-horizontal" action='' method="post">
<div class="control-group">
<!-- Full name -->
<label class="control-label" for="fullname">Full Name</label>
<div class="controls">
<input type="text" id="fname" name="fname" pattern="[a-zA-Z\s]+" title="Full name must contain letters only" class="input-xlarge" required>
<p class="help-block">Username can contain any letters or numbers, without spaces</p>
</div>
</div>
<div class="control-group">
<!-- Username -->
<label class="control-label" for="username">Username</label>
<div class="controls">
<input type="text" id="username" name="username" onBlur="checkUsernameAvailability()" pattern="^[a-zA-Z][a-zA-Z0-9-_.]{5,12}$" title="User must be alphanumeric without spaces 6 to 12 chars" class="input-xlarge" required>
<!-- Message for username availability-->
<span id="username-availability-status" style="font-size:12px;"></span>
<p class="help-block">Username can contain any letters or numbers, without spaces</p>
</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="" onBlur="checkEmailAvailability()" class="input-xlarge" required>
<!-- Message for Email availability-->
<span id="email-availability-status" style="font-size:12px;"></span>
<p class="help-block">Please provide your E-mail</p>
</div>
</div>
<div class="control-group">
<!-- Mobile Number -->
<label class="control-label" for="mobilenumber">Mobile Number </label>
<div class="controls">
<input type="text" id="mobilenumber" name="mobilenumber" pattern="[0-9]{10}" maxlength="10" title="10 numeric digits only" class="input-xlarge" required>
<p class="help-block">Mobile Number Contain only numeric values,net</p>
</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" pattern="^\S{4,}$" onchange="this.setCustomValidity(this.validity.patternMismatch ? 'Must have at least 4 characters' : ''); if(this.checkValidity()) form.password_two.pattern = this.value;" required class="input-xlarge">
<p class="help-block">Password should be at least 4 characters</p>
</div>
</div>
<div class="control-group">
<!-- Confirm Password -->
<label class="control-label" for="password_confirm">Password (Confirm)</label>
<div class="controls">
<input type="password" id="password_confirm" name="password_confirm" pattern="^\S{4,}$" onchange="this.setCustomValidity(this.validity.patternMismatch ? 'Please enter the same Password as above' : '')"" class="input-xlarge">
<p class="help-block">Please confirm password</p>
</div>
</div>
<div class="control-group">
<!-- Button -->
<div class="controls">
<button class="btn btn-success" type="submit" name="signup">Signup </button>
</div>
</form>
step3.2 Check the username and email availability in the data using j-query
j-query code
<!--Javascript for check username availability-->
<script>
function checkUsernameAvailability() {
$("#loaderIcon").show();
jQuery.ajax({
url: "check_availability.php",
data:'username='+$("#username").val(),
type: "POST",
success:function(data){
$("#username-availability-status").html(data);
$("#loaderIcon").hide();
},
error:function (){
}
});
}
</script>
<!--Javascript for check email availability-->
<script>
function checkEmailAvailability() {
$("#loaderIcon").show();
jQuery.ajax({
url: "check_availability.php",
data:'email='+$("#email").val(),
type: "POST",
success:function(data){
$("#email-availability-status").html(data);
$("#loaderIcon").hide();
},
error:function (){
event.preventDefault();
}
});
}
</script>
PHP Code for checking availability in the database (check_availability.php)
<?php
require_once("config.php");
// Code for checking username availabilty
if(!empty($_POST["username"])) {
$uname= $_POST["username"];
$sql ="SELECT UserName FROM userdata WHERE UserName=:uname";
$query= $dbh -> prepare($sql);
$query-> bindParam(':uname', $uname, PDO::PARAM_STR);
$query-> execute();
$results = $query -> fetchAll(PDO::FETCH_OBJ);
if($query -> rowCount() > 0)
{
echo "<span style='color:red'> Username already exists.</span>";
} else{
echo "<span style='color:green'> Username available for Registration.</span>";
}
}
// Code for checking email availabilty
if(!empty($_POST["email"])) {
$email= $_POST["email"];
$sql ="SELECT UserEmail FROM userdata WHERE UserEmail=:email";
$query= $dbh -> prepare($sql);
$query-> bindParam(':email', $email, PDO::PARAM_STR);
$query-> execute();
$results = $query -> fetchAll(PDO::FETCH_OBJ);
if($query -> rowCount() > 0)
{
echo "<span style='color:red'>Email-id already exists.</span>";
} else{
echo "<span style='color:green'>Email-id available for Registration.</span>";
}
}
?>
step3.3 code for inserting sign up values in the database
<?php
//Database Configuration File
include('config.php');
error_reporting(0);
if(isset($_POST['signup']))
{
//Getting Post Values
$fullname=$_POST['fname'];
$username=$_POST['username'];
$email=$_POST['email'];
$mobile=$_POST['mobilenumber'];
$password=md5($_POST['password']);
// Query for validation of username and email-id
$ret="SELECT * FROM userdata where (UserName=:uname || UserEmail=:uemail)";
$queryt = $dbh -> prepare($ret);
$queryt->bindParam(':uemail',$email,PDO::PARAM_STR);
$queryt->bindParam(':uname',$username,PDO::PARAM_STR);
$queryt -> execute();
$results = $queryt -> fetchAll(PDO::FETCH_OBJ);
if($queryt -> rowCount() == 0)
{
// Query for Insertion
$sql="INSERT INTO userdata(FullName,UserName,UserEmail,UserMobileNumber,LoginPassword) VALUES(:fname,:uname,:uemail,:umobile,:upassword)";
$query = $dbh->prepare($sql);
// Binding Post Values
$query->bindParam(':fname',$fullname,PDO::PARAM_STR);
$query->bindParam(':uname',$username,PDO::PARAM_STR);
$query->bindParam(':uemail',$email,PDO::PARAM_STR);
$query->bindParam(':umobile',$mobile,PDO::PARAM_INT);
$query->bindParam(':upassword',$password,PDO::PARAM_STR);
$query->execute();
$lastInsertId = $dbh->lastInsertId();
if($lastInsertId)
{
$msg="You have signup Scuccessfully";
}
else
{
$error="Something went wrong.Please try again";
}
}
else
{
$error="Username or Email-id already exist. Please try again";
}
}
?>
Here is the full code that we have written for signup(signup.php):
<?php
//Database Configuration File
include('config.php');
error_reporting(0);
if(isset($_POST['signup']))
{
//Getting Post Values
$fullname=$_POST['fname'];
$username=$_POST['username'];
$email=$_POST['email'];
$mobile=$_POST['mobilenumber'];
$password=md5($_POST['password']);
// Query for validation of username and email-id
$ret="SELECT * FROM userdata where (UserName=:uname || UserEmail=:uemail)";
$queryt = $dbh -> prepare($ret);
$queryt->bindParam(':uemail',$email,PDO::PARAM_STR);
$queryt->bindParam(':uname',$username,PDO::PARAM_STR);
$queryt -> execute();
$results = $queryt -> fetchAll(PDO::FETCH_OBJ);
if($queryt -> rowCount() == 0)
{
// Query for Insertion
$sql="INSERT INTO userdata(FullName,UserName,UserEmail,UserMobileNumber,LoginPassword) VALUES(:fname,:uname,:uemail,:umobile,:upassword)";
$query = $dbh->prepare($sql);
// Binding Post Values
$query->bindParam(':fname',$fullname,PDO::PARAM_STR);
$query->bindParam(':uname',$username,PDO::PARAM_STR);
$query->bindParam(':uemail',$email,PDO::PARAM_STR);
$query->bindParam(':umobile',$mobile,PDO::PARAM_INT);
$query->bindParam(':upassword',$password,PDO::PARAM_STR);
$query->execute();
$lastInsertId = $dbh->lastInsertId();
if($lastInsertId)
{
$msg="You have signup Scuccessfully";
}
else
{
$error="Something went wrong.Please try again";
}
}
else
{
$error="Username or Email-id already exist. Please try again";
}
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>PDO | Registration Form</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>
<style>
.errorWrap {
padding: 10px;
margin: 0 0 20px 0;
background: #fff;
border-left: 4px solid #dd3d36;
-webkit-box-shadow: 0 1px 1px 0 rgba(0,0,0,.1);
box-shadow: 0 1px 1px 0 rgba(0,0,0,.1);
}
.succWrap{
padding: 10px;
margin: 0 0 20px 0;
background: #fff;
border-left: 4px solid #5cb85c;
-webkit-box-shadow: 0 1px 1px 0 rgba(0,0,0,.1);
box-shadow: 0 1px 1px 0 rgba(0,0,0,.1);
}
</style>
<!--Javascript for check username availability-->
<script>
function checkUsernameAvailability() {
$("#loaderIcon").show();
jQuery.ajax({
url: "check_availability.php",
data:'username='+$("#username").val(),
type: "POST",
success:function(data){
$("#username-availability-status").html(data);
$("#loaderIcon").hide();
},
error:function (){
}
});
}
</script>
<!--Javascript for check email availability-->
<script>
function checkEmailAvailability() {
$("#loaderIcon").show();
jQuery.ajax({
url: "check_availability.php",
data:'email='+$("#email").val(),
type: "POST",
success:function(data){
$("#email-availability-status").html(data);
$("#loaderIcon").hide();
},
error:function (){
event.preventDefault();
}
});
}
</script>
</head>
<body>
<form class="form-horizontal" action='' method="post">
<fieldset>
<div id="legend" style="padding-left:4%">
<legend class="">Register | <a href="index.php">Sign in</a></legend>
</div>
<!--Error Message-->
<?php if($error){ ?><div class="errorWrap">
<strong>Error </strong> : <?php echo htmlentities($error);?></div>
<?php } ?>
<!--Success Message-->
<?php if($msg){ ?><div class="succWrap">
<strong>Well Done </strong> : <?php echo htmlentities($msg);?></div>
<?php } ?>
<div class="control-group">
<!-- Full name -->
<label class="control-label" for="fullname">Full Name</label>
<div class="controls">
<input type="text" id="fname" name="fname" pattern="[a-zA-Z\s]+" title="Full name must contain letters only" class="input-xlarge" required>
<p class="help-block">Full can contain any letters only</p>
</div>
</div>
<div class="control-group">
<!-- Username -->
<label class="control-label" for="username">Username</label>
<div class="controls">
<input type="text" id="username" name="username" onBlur="checkUsernameAvailability()" pattern="^[a-zA-Z][a-zA-Z0-9-_.]{5,12}$" title="User must be alphanumeric without spaces 6 to 12 chars" class="input-xlarge" required>
<span id="username-availability-status" style="font-size:12px;"></span>
<p class="help-block">Username can contain any letters or numbers, without spaces 6 to 12 chars </p>
</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="" onBlur="checkEmailAvailability()" class="input-xlarge" required>
<span id="email-availability-status" style="font-size:12px;"></span>
<p class="help-block">Please provide your E-mail</p>
</div>
</div>
<div class="control-group">
<!-- Mobile Number -->
<label class="control-label" for="mobilenumber">Mobile Number </label>
<div class="controls">
<input type="text" id="mobilenumber" name="mobilenumber" pattern="[0-9]{10}" maxlength="10" title="10 numeric digits only" class="input-xlarge" required>
<p class="help-block">Mobile Number Contain only 10 digit numeric values</p>
</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" pattern="^\S{4,}$" onchange="this.setCustomValidity(this.validity.patternMismatch ? 'Must have at least 4 characters' : ''); if(this.checkValidity()) form.password_two.pattern = this.value;" required class="input-xlarge">
<p class="help-block">Password should be at least 4 characters</p>
</div>
</div>
<div class="control-group">
<!-- Confirm Password -->
<label class="control-label" for="password_confirm">Password (Confirm)</label>
<div class="controls">
<input type="password" id="password_confirm" name="password_confirm" pattern="^\S{4,}$" onchange="this.setCustomValidity(this.validity.patternMismatch ? 'Please enter the same Password as above' : '')"" class="input-xlarge">
<p class="help-block">Please confirm password</p>
</div>
</div>
<div class="control-group">
<!-- Button -->
<div class="controls">
<button class="btn btn-success" type="submit" name="signup">Signup </button>
</div>
</div>
</fieldset>
</form>
<script >
</script>
</body>
</html>
Step 4– Signin or Login
Create a HTML sign form
<form id="loginForm" method="post"> div class="form-group"> <label for="username" class="control-label">Username / Email id</label> <input type="text" class="form-control" id="username" name="username" required="" title="Please enter you username or Email-id" placeholder="email or username" > <span class="help-block"></span> </div> <div class="form-group"> <label for="password" class="control-label">Password</label> <input type="password" class="form-control" id="password" name="password" placeholder="Password" value="" required="" title="Please enter your password"> <span class="help-block"></span> </div> <button type="submit" class="btn btn-success btn-block" name="login">Login</button> </form>
User can login through valid username/email and password.
PHP Code for Signin or Login
<?php
session_start();
//Database Configuration File
include('config.php');
error_reporting(0);
if(isset($_POST['login']))
{
// Getting username/ email and password
$uname=$_POST['username'];
$password=md5($_POST['password']);
// Fetch data from database on the basis of username/email and password
$sql ="SELECT UserName,UserEmail,LoginPassword FROM userdata WHERE (UserName=:usname || UserEmail=:usname) and (LoginPassword=:usrpassword)";
$query= $dbh -> prepare($sql);
$query-> bindParam(':usname', $uname, PDO::PARAM_STR);
$query-> bindParam(':usrpassword', $password, PDO::PARAM_STR);
$query-> execute();
$results=$query->fetchAll(PDO::FETCH_OBJ);
if($query->rowCount() > 0)
{
$_SESSION['userlogin']=$_POST['username'];
echo "<script > document.location = 'welcome.php'; </script>";
} else{
echo "<script>alert('Invalid Details');</script>";
}
}
?>
Step 5– Create a welcome page.
After login user will redirect to this page. Validate this page using user session if anyone want to access this page without login will redirect to index.php automatically.
Code for welcome.php
<?php
session_start();
include('config.php');
// Validating Session
if(strlen($_SESSION['userlogin'])==0)
{
header('location:index.php');
}
else{
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>PDO | Welcome Page</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">
<style >
.center {text-align: center; margin-left: auto; margin-right: auto; margin-bottom: auto; margin-top: auto;}
</style>
<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>
</head>
<body>
<div class="container">
<div class="row">
<div class="span12">
<div class="hero-unit center">
<?php
// Code for fecthing user full name on the bassis of username or email.
$username=$_SESSION['userlogin'];
$query=$dbh->prepare("SELECT FullName FROM userdata WHERE (UserName=:username || UserEmail=:username)");
$query->execute(array(':username'=> $username));
while($row=$query->fetch(PDO::FETCH_ASSOC)){
$username=$row['FullName'];
}
?>
<h1>Welcome Back <font face="Tahoma" color="red"><?php echo $username;?> ! </font></h1>
<br />
<p>Lorem ipsum dolor sit amet, sit veniam senserit mediocritatem et, melius aperiam complectitur an qui. Ut numquam vocibus accumsan mel. Per ei etiam vituperatoribus, ne quot mandamus conceptam has, pri molestiae constituam quaerendum an. In molestiae torquatos eam.
</p>
<a href="logout.php" class="btn btn-large btn-info"><i class="icon-home icon-white"></i> Log me out</a>
</div>
<br />
</div>
<br />
<!-- By ConnerT HTML & CSS Enthusiast -->
</div>
</div>
</div>
<script >
</script>
</body>
</html>
<?php } ?>
Step 6– logout Page.
Logout page used for destroy the session or unset the session variable.
code for logout.php page
<?php
session_start();
unset($_SESSION['userlogin']); // unset session variable
session_destroy(); // destroy session
header("location:index.php");
?>
Download Full source Code(Signup and Login operation using PDO)
Version: V 1.0
