PHP CRUD Operation using PDO Extension

by Anuj Kumar

In the previous tutorial we learned about PDO Extension. In this tutorial we will learn CRUD operation using PDO Extension.
CRUD Stands for create, read, update and delete record in the database.

View Demo

File Structure for CRUD Operation

dbconfig.php- used for database connection
tblusers.sql– Contain the structure of the database table
insert.php- used for add a record in the database
index.php– Used for read the record from database .
update.php– Used for updating a record.

Step 1– Create a database

Open browser type http://localhost/phpmyadmin, create a database named ‘phpcrudpdo’ . After creating database run the sql script or import the sql file.

-- Table structure for table `tblusers`
--
CREATE TABLE IF NOT EXISTS `tblusers` (
  `id` int(11) NOT NULL,
  `FirstName` varchar(150) NOT NULL,
  `LastName` varchar(150) NOT NULL,
  `EmailId` varchar(120) NOT NULL,
  `ContactNumber` char(11) NOT NULL,
  `Address` varchar(255) NOT NULL,
  `PostingDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- Indexes for table `tblusers`
--
ALTER TABLE `tblusers`
  ADD PRIMARY KEY (`id`);
-- AUTO_INCREMENT for dumped tables
-- AUTO_INCREMENT for table `tblusers`
--
ALTER TABLE `tblusers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

-- Table structure for table `tblusers`

CREATE TABLE IF NOT EXISTS `tblusers` (

`id` int(11) NOT NULL,

`FirstName` varchar(150) NOT NULL,

`LastName` varchar(150) NOT NULL,

`EmailId` varchar(120) NOT NULL,

`ContactNumber` char(11) NOT NULL,

`Address` varchar(255) NOT NULL,

`PostingDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Indexes for table `tblusers`

ALTER TABLE `tblusers`

ADD PRIMARY KEY (`id`);

-- AUTO_INCREMENT for dumped tables

-- AUTO_INCREMENT for table `tblusers`

ALTER TABLE `tblusers`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

Step 2– Create a database connection file (dbconfig.php)

create a dbconfig.php file and place the code given below :

<?php
// DB credentials.
define('DB_HOST','localhost');
define('DB_USER','root');
define('DB_PASS','');
define('DB_NAME','phpcrudpdo');
// 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());
}
?>

<?php

// DB credentials.

define('DB_HOST','localhost');

define('DB_USER','root');

define('DB_PASS','');

define('DB_NAME','phpcrudpdo');

// 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 : Insert a record in database

Create a insert.php file for insert record in the database . This page include a HTML form with input field where we can fill the data.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>PHP CURD Operation using PDO Extension  </title>
        <meta name="viewport" content="width=device-width, initial-scale=1">
    <link href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/css/bootstrap.min.css" rel="stylesheet">
    <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="container">
<div class="row">
<div class="col-md-12">
<h3>Insert Record | PHP CRUD Operations using PDO Extension</h3>
<hr />
</div>
</div>
<form name="insertrecord" method="post">
<div class="row">
<div class="col-md-4"><b>First Name</b>
<input type="text" name="firstname" class="form-control" required>
</div>
<div class="col-md-4"><b>Last Name</b>
<input type="text" name="lastname" class="form-control" required>
</div>
</div>
<div class="row">
<div class="col-md-4"><b>Email id</b>
<input type="email" name="emailid" class="form-control" required>
</div>
<div class="col-md-4"><b>Contactno</b>
<input type="text" name="contactno" class="form-control" maxlength="10" required>
</div>
</div>
<div class="row">
<div class="col-md-8"><b>Address</b>
<textarea class="form-control" name="address" required></textarea>
</div>
</div>
<div class="row" style="margin-top:1%">
<div class="col-md-8">
<input type="submit" name="insert" value="Submit">
</div>
</div>
</form>
</div>
</div>
</body>
</html>

<!DOCTYPE html>

<head>

<title>PHP CURD Operation using PDO Extension </title>

</head>

<body>

<h3>Insert Record | PHP CRUD Operations using PDO Extension</h3>

<hr />

</div>

<div class="col-md-4"><b>First Name</b>

</div>

</div>

<div class="col-md-4"><b>Email id</b>

</div>

<div class="col-md-4"><b>Contactno</b>

</div>

<div class="col-md-8"><b>Address</b>

</div>

</div>

</form>

</div>

</body>

</html>

Code for insert a record in database. Once the user filled all the data and click on the submit button then data will be saved in the database using the below code:

<?php
// include database connection file
require_once'dbconfig.php';
if(isset($_POST['insert']))
{
// Posted Values
$fname=$_POST['firstname'];
$lname=$_POST['lastname'];
$emailid=$_POST['emailid'];
$contactno=$_POST['contactno'];
$address=$_POST['address'];
// Query for Insertion
$sql="INSERT INTO tblusers(FirstName,LastName,EmailId,ContactNumber,Address) VALUES(:fn,:ln,:eml,:cno,:adrss)";
//Prepare Query for Execution
$query = $dbh->prepare($sql);
// Bind the parameters
$query->bindParam(':fn',$fname,PDO::PARAM_STR);
$query->bindParam(':ln',$lname,PDO::PARAM_STR);
$query->bindParam(':eml',$emailid,PDO::PARAM_STR);
$query->bindParam(':cno',$contactno,PDO::PARAM_STR);
$query->bindParam(':adrss',$address,PDO::PARAM_STR);
// Query Execution
$query->execute();
// Check that the insertion really worked. If the last inserted id is greater than zero, the insertion worked.
$lastInsertId = $dbh->lastInsertId();
if($lastInsertId)
{
// Message for successfull insertion
echo "<script>alert('Record inserted successfully');</script>";
echo "<script>window.location.href='index.php'</script>";
}
else
{
// Message for unsuccessfull insertion
echo "<script>alert('Something went wrong. Please try again');</script>";
echo "<script>window.location.href='index.php'</script>";
}
}
?>

<?php

// include database connection file

require_once'dbconfig.php';

if(isset($_POST['insert']))

{

// Posted Values

$fname=$_POST['firstname'];

$lname=$_POST['lastname'];

$emailid=$_POST['emailid'];

$contactno=$_POST['contactno'];

$address=$_POST['address'];

// Query for Insertion

$sql="INSERT INTO tblusers(FirstName,LastName,EmailId,ContactNumber,Address) VALUES(:fn,:ln,:eml,:cno,:adrss)";

//Prepare Query for Execution

$query = $dbh->prepare($sql);

// Bind the parameters

$query->bindParam(':fn',$fname,PDO::PARAM_STR);

$query->bindParam(':ln',$lname,PDO::PARAM_STR);

$query->bindParam(':eml',$emailid,PDO::PARAM_STR);

$query->bindParam(':cno',$contactno,PDO::PARAM_STR);

$query->bindParam(':adrss',$address,PDO::PARAM_STR);

// Query Execution

$query->execute();

// Check that the insertion really worked. If the last inserted id is greater than zero, the insertion worked.

$lastInsertId = $dbh->lastInsertId();

if($lastInsertId)

{

// Message for successfull insertion

echo "<script>alert('Record inserted successfully');</script>";

echo "<script>window.location.href='index.php'</script>";

}

else

{

// Message for unsuccessfull insertion

echo "<script>alert('Something went wrong. Please try again');</script>";

echo "<script>window.location.href='index.php'</script>";

}

Step 4 : Read record from the database

Create a index.php file for read all records from database.

<?php
// include database connection file
require_once'dbconfig.php';
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>PHP CRUD Operations using PDO Extension </title>
        <meta name="viewport" content="width=device-width, initial-scale=1">
    <link href="http://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/css/bootstrap.min.css" rel="stylesheet">
    <style type="text/css">
    </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="container">
<div class="row">
<div class="col-md-12">
<h3>PHP CRUD Operations using PDO Extension</h3> <hr />
<a href="insert.php"><button class="btn btn-primary"> Insert Record</button></a>
<div class="table-responsive">
<table id="mytable" class="table table-bordred table-striped">
<thead>
<th>#</th>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Contact</th>
<th>Address</th>
<th>Posting Date</th>
<th>Edit</th>
<th>Delete</th>
</thead>
<tbody>
<?php
$sql = "SELECT FirstName,LastName,EmailId,ContactNumber,Address,PostingDate,id from tblusers";
//Prepare the query:
$query = $dbh->prepare($sql);
//Execute the query:
$query->execute();
//Assign the data which you pulled from the database (in the preceding step) to a variable.
$results=$query->fetchAll(PDO::FETCH_OBJ);
// For serial number initialization
$cnt=1;
if($query->rowCount() > 0)
{
//In case that the query returned at least one record, we can echo the records within a foreach loop:
foreach($results as $result)
{
?>
<!-- Display Records -->
    <tr>
    <td><?php echo htmlentities($cnt);?></td>
    <td><?php echo htmlentities($result->FirstName);?></td>
    <td><?php echo htmlentities($result->LastName);?></td>
    <td><?php echo htmlentities($result->EmailId);?></td>
    <td><?php echo htmlentities($result->ContactNumber);?></td>
    <td><?php echo htmlentities($result->Address);?></td>
    <td><?php echo htmlentities($result->PostingDate);?></td>
<td><a href="update.php?id=<?php echo htmlentities($result->id);?>"><button class="btn btn-primary btn-xs"><span class="glyphicon glyphicon-pencil"></span></button></a></td>
<td><a href="index.php?del=<?php echo htmlentities($result->id);?>"><button class="btn btn-danger btn-xs" onClick="return confirm('Do you really want to delete');"><span class="glyphicon glyphicon-trash"></span></button></a></td>
    </tr>
<?php
// for serial number increment
$cnt++;
}} ?>
</tbody>
</table>
</div>
</div>
</div>
</div>
</body>
</html>

<?php

// include database connection file

require_once'dbconfig.php';

<!DOCTYPE html>

<head>

<title>PHP CRUD Operations using PDO Extension </title>

</style>

</head>

<body>

<h3>PHP CRUD Operations using PDO Extension</h3> <hr />

<a href="insert.php"><button class="btn btn-primary"> Insert Record</button></a>

<thead>

<th>First Name</th>

<th>Email</th>

<th>Contact</th>

<th>Address</th>

<th>Posting Date</th>

<th>Delete</th>

</thead>

<tbody>

<?php

$sql = "SELECT FirstName,LastName,EmailId,ContactNumber,Address,PostingDate,id from tblusers";

//Prepare the query:

$query = $dbh->prepare($sql);

//Execute the query:

$query->execute();

//Assign the data which you pulled from the database (in the preceding step) to a variable.

$results=$query->fetchAll(PDO::FETCH_OBJ);

// For serial number initialization

$cnt=1;

if($query->rowCount() > 0)

{

//In case that the query returned at least one record, we can echo the records within a foreach loop:

foreach($results as $result)

{

<tr>

<td><?php echo htmlentities($result->FirstName);?></td>

<td><?php echo htmlentities($result->LastName);?></td>

<td><?php echo htmlentities($result->EmailId);?></td>

<td><?php echo htmlentities($result->ContactNumber);?></td>

<td><?php echo htmlentities($result->Address);?></td>

<td><?php echo htmlentities($result->PostingDate);?></td>

</tr>

<?php

// for serial number increment

$cnt++;

}} ?>

</tbody>

</table>

</div>

</body>

</html>

Step 5 : Update record in the database

Step 5.1 : Get data in HTML From

create update.php file. For updating a record we have to get the row id of that record and store in $id. We access the $_GET[‘id’] variable to do it.
Code for get a record based on the given id. Through this way we can get data autofill-data in HTML Form.

<?php
// Get the userid
$userid=intval($_GET['id']);
$sql = "SELECT FirstName,LastName,EmailId,ContactNumber,Address,PostingDate,id from tblusers where id=:uid";
//Prepare the query:
$query = $dbh->prepare($sql);
//Bind the parameters
$query->bindParam(':uid',$userid,PDO::PARAM_STR);
//Execute the query:
$query->execute();
//Assign the data which you pulled from the database (in the preceding step) to a variable.
$results=$query->fetchAll(PDO::FETCH_OBJ);
// For serial number initialization
$cnt=1;
if($query->rowCount() > 0)
{
//In case that the query returned at least one record, we can echo the records within a foreach loop:
foreach($results as $result)
{
?>
<form name="insertrecord" method="post">
<div class="row">
<div class="col-md-4"><b>First Name</b>
<input type="text" name="firstname" value="<?php echo htmlentities($result->FirstName);?>" class="form-control" required>
</div>
<div class="col-md-4"><b>Last Name</b>
<input type="text" name="lastname" value="<?php echo htmlentities($result->LastName);?>" class="form-control" required>
</div>
</div>
<div class="row">
<div class="col-md-4"><b>Email id</b>
<input type="email" name="emailid" value="<?php echo htmlentities($result->EmailId);?>" class="form-control" required>
</div>
<div class="col-md-4"><b>Contactno</b>
<input type="text" name="contactno" value="<?php echo htmlentities($result->ContactNumber);?>" class="form-control" maxlength="10" required>
</div>
</div>
<div class="row">
<div class="col-md-8"><b>Address</b>
<textarea class="form-control" name="address" required><?php echo htmlentities($result->Address);?></textarea>
</div>
</div>
<?php }} ?>
<div class="row" style="margin-top:1%">
<div class="col-md-8">
<input type="submit" name="update" value="Update">
</div>
</div>
</form>

<?php

// Get the userid

$userid=intval($_GET['id']);

$sql = "SELECT FirstName,LastName,EmailId,ContactNumber,Address,PostingDate,id from tblusers where id=:uid";

//Prepare the query:

$query = $dbh->prepare($sql);

//Bind the parameters

$query->bindParam(':uid',$userid,PDO::PARAM_STR);

//Execute the query:

$query->execute();

//Assign the data which you pulled from the database (in the preceding step) to a variable.

$results=$query->fetchAll(PDO::FETCH_OBJ);

// For serial number initialization

$cnt=1;

if($query->rowCount() > 0)

{

//In case that the query returned at least one record, we can echo the records within a foreach loop:

foreach($results as $result)

{

<div class="col-md-4"><b>First Name</b>

<input type="text" name="firstname" value="<?php echo htmlentities($result->FirstName);?>" class="form-control" required>

</div>

<input type="text" name="lastname" value="<?php echo htmlentities($result->LastName);?>" class="form-control" required>

</div>

<div class="col-md-4"><b>Email id</b>

<input type="email" name="emailid" value="<?php echo htmlentities($result->EmailId);?>" class="form-control" required>

</div>

<div class="col-md-4"><b>Contactno</b>

<input type="text" name="contactno" value="<?php echo htmlentities($result->ContactNumber);?>" class="form-control" maxlength="10" required>

</div>

<div class="col-md-8"><b>Address</b>

<textarea class="form-control" name="address" required><?php echo htmlentities($result->Address);?></textarea>

</div>

<?php }} ?>

</div>

</form>

Step 5.2 : Code for update the record

<?php
// include database connection file
require_once'dbconfig.php';
if(isset($_POST['update']))
{
// Get the userid
$userid=intval($_GET['id']);
// Posted Values
$fname=$_POST['firstname'];
$lname=$_POST['lastname'];
$emailid=$_POST['emailid'];
$contactno=$_POST['contactno'];
$address=$_POST['address'];
// Query for Updation
$sql="update tblusers set FirstName=:fn,LastName=:ln,EmailId=:eml,ContactNumber=:cno,Address=:adrss where id=:uid";
//Prepare Query for Execution
$query = $dbh->prepare($sql);
// Bind the parameters
$query->bindParam(':fn',$fname,PDO::PARAM_STR);
$query->bindParam(':ln',$lname,PDO::PARAM_STR);
$query->bindParam(':eml',$emailid,PDO::PARAM_STR);
$query->bindParam(':cno',$contactno,PDO::PARAM_STR);
$query->bindParam(':adrss',$address,PDO::PARAM_STR);
$query->bindParam(':uid',$userid,PDO::PARAM_STR);
// Query Execution
$query->execute();
// Mesage after updation
echo "<script>alert('Record Updated successfully');</script>";
// Code for redirection
echo "<script>window.location.href='index.php'</script>";
}
?>

<?php

// include database connection file

require_once'dbconfig.php';

if(isset($_POST['update']))

{

// Get the userid

$userid=intval($_GET['id']);

// Posted Values

$fname=$_POST['firstname'];

$lname=$_POST['lastname'];

$emailid=$_POST['emailid'];

$contactno=$_POST['contactno'];

$address=$_POST['address'];

// Query for Updation

$sql="update tblusers set FirstName=:fn,LastName=:ln,EmailId=:eml,ContactNumber=:cno,Address=:adrss where id=:uid";

//Prepare Query for Execution

$query = $dbh->prepare($sql);

// Bind the parameters

$query->bindParam(':fn',$fname,PDO::PARAM_STR);

$query->bindParam(':ln',$lname,PDO::PARAM_STR);

$query->bindParam(':eml',$emailid,PDO::PARAM_STR);

$query->bindParam(':cno',$contactno,PDO::PARAM_STR);

$query->bindParam(':adrss',$address,PDO::PARAM_STR);

$query->bindParam(':uid',$userid,PDO::PARAM_STR);

// Query Execution

$query->execute();

// Mesage after updation

echo "<script>alert('Record Updated successfully');</script>";

// Code for redirection

echo "<script>window.location.href='index.php'</script>";

}

Step 6 : Delete a record from the database

Place this code in the index.php file.

<?php
// include database connection file
require_once'dbconfig.php';
// Code for record deletion
if(isset($_REQUEST['del']))
{
//Get row id
$uid=intval($_GET['del']);
//Qyery for deletion
$sql = "delete from tblusers WHERE  id=:id";
// Prepare query for execution
$query = $dbh->prepare($sql);
// bind the parameters
$query-> bindParam(':id',$uid, PDO::PARAM_STR);
// Query Execution
$query -> execute();
// Mesage after updation
echo "<script>alert('Record Updated successfully');</script>";
// Code for redirection
echo "<script>window.location.href='index.php'</script>";
}
?>

<?php

// include database connection file

require_once'dbconfig.php';

// Code for record deletion

if(isset($_REQUEST['del']))

{

//Get row id

$uid=intval($_GET['del']);

//Qyery for deletion

$sql = "delete from tblusers WHERE id=:id";

// Prepare query for execution

$query = $dbh->prepare($sql);

// bind the parameters

$query-> bindParam(':id',$uid, PDO::PARAM_STR);

// Query Execution

$query -> execute();

// Mesage after updation

echo "<script>alert('Record Updated successfully');</script>";

// Code for redirection

echo "<script>window.location.href='index.php'</script>";

}

PHP CRUD Operation using PDO Extension

Recommended Tutorials for you

You may also like...

PHP Data Object Tutorials

PHP CRUD Operation using PDO Extension

File Structure for CRUD Operation

Step 1– Create a database

Step 2– Create a database connection file (dbconfig.php)

Step 3 : Insert a record in database

Step 4 : Read record from the database

Step 5 : Update record in the database

Step 5.1 : Get data in HTML From

Step 5.2 : Code for update the record

Step 6 : Delete a record from the database

Download Source Code (PHP CRUD Operation using PDO Extension)Size: 7 KBVersion: V 1.0Download Now!

Recommended Tutorials for you

You may also like...

Signup and Login operation using PDO

How to use PDO to update the database?

How to check Email and username availability live using jquery/ajax, PHP and PDO

PHP Data Object Tutorials

Download Source Code (PHP CRUD Operation using PDO Extension)
Size: 7 KB
Version: V 1.0
Download Now!