php crud operation using pdo extension

June 4, 2018 XpertPhp

In this article, We will create a PHP crud operation using PDO extension. The CRUD means Create, edit, update and delete. so here we will give you a simple example of insert, update, delete using PHP PDO with MySQL.

PDO stands for PHP Data Objects. PDO is a simple and consistent way to access databases. This means that developers can write portable code much more easily. PDO is not an abstraction layer like PearDB. PDO is just more like a data access layer that uses a unified API.

So you can see our example for PHP crud operation using PDO extension.

Create Database and Tables

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(64) NOT NULL,
  `last_name` varchar(64) NOT NULL,
  `address` text NOT NULL,
  `email` varchar(64) NOT NULL,
  `mobile` varchar(12) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;

CREATE TABLE IF NOT EXISTS `users` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`first_name` varchar(64) NOT NULL,

`last_name` varchar(64) NOT NULL,

`address` text NOT NULL,

`email` varchar(64) NOT NULL,

`mobile` varchar(12) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;

connect to database in mysql

Create a new connect.php file. The code below is to provide how to connect PDO with MySQL.
The following code is used to connect MySQL from PHP. It requires hostname, database username, database password, and database name.

<?php
// DB credentials.
$db_host = 'localhost';
$db_user = 'root';
$db_pass = '';
$db_name = 'php_crud_pdo';
try
{
	$db = new PDO("mysql:host=".$db_host.";dbname=".$db_name,$db_user, $db_pass);
}
catch (PDOException $e)
{
	exit("Error: " . $e->getMessage());
}
?>

<?php

// DB credentials.

$db_host = 'localhost';

$db_user = 'root';

$db_pass = '';

$db_name = 'php_crud_pdo';

try

{

$db = new PDO("mysql:host=".$db_host.";dbname=".$db_name,$db_user, $db_pass);

}

catch (PDOException $e)

{

exit("Error: " . $e->getMessage());

}

Creating New Record in MySQL Database

Create a PHP file add.php in this file in add the below code.
The code below in the INSERT query through creates a new record to the database table. This HTML form contains input fields to enter user data to be inserted into the table.

<?php
if(isset($_POST['btnadd']))
{
    $first_name  = $_POST['txtFname'];
    $last_name   = $_POST['txtLname'];
    $address     = $_POST['txtAddress'];
    $email       = $_POST['txtEmail'];
    $mobile      = $_POST['txtMobile'];

    include('connect.php');
	
	$sql="insert into users (first_name,last_name,address,email,mobile) values(:first_name,:last_name,:address,:email,:mobile)";
	$query = $db->prepare($sql);
	$query->bindParam(':first_name',$first_name,PDO::PARAM_STR);
	$query->bindParam(':last_name',$last_name,PDO::PARAM_STR);
	$query->bindParam(':address',$address,PDO::PARAM_STR);
	$query->bindParam(':email',$email,PDO::PARAM_STR);
	$query->bindParam(':mobile',$mobile,PDO::PARAM_STR);
	$query->execute();
	$lastInsertId = $db->lastInsertId();
    header('location:index.php');

}
?>
<!DOCTYPE html>
<html lang="en">
<head>
  <title></title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>
	<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
	<script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>		
	<link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
</head>
<body>
	<div class="container">
		<div class="row" style="margin-bottom:10px;">
			<div class="col-lg-10"></div>
			<div class="col-lg-2"><a href="index.php" class="btn btn-primary">Back</a></div>
		</div>
		<form method="post" name="frmAdd">
		  <div class="form-group">
			<label for="txtFname">First Name:</label>
			<input type="text" class="form-control" name="txtFname" required  id="txtFname">
		  </div>
		  <div class="form-group">
			<label for="txtLname">Last Name:</label>
			<input type="text" class="form-control" required name="txtLname" id="txtLname">
		  </div>
		   <div class="form-group">
			<label for="txtEmail">Email:</label>
			<input type="email" class="form-control" required name="txtEmail" id="txtEmail">
		  </div>
		  <div class="form-group">
			<label for="txtMobile">Mobile:</label>
			<input type="text" class="form-control" required name="txtMobile" id="txtMobile">
		  </div>
		  <div class="form-group">
			<label for="txtAddress">Address:</label>
			<textarea name="txtAddress" class="form-control" required id="txtAddress"></textarea>
		  </div>
		  <button type="submit" class="btn btn-success" name="btnadd">Add</button>
		</form>
	</div>
</body>
</html>

<?php

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

{

$first_name = $_POST['txtFname'];

$last_name = $_POST['txtLname'];

$address = $_POST['txtAddress'];

$email = $_POST['txtEmail'];

$mobile = $_POST['txtMobile'];

include('connect.php');

$sql="insert into users (first_name,last_name,address,email,mobile) values(:first_name,:last_name,:address,:email,:mobile)";

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

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

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

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

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

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

$query->execute();

$lastInsertId = $db->lastInsertId();

header('location:index.php');

}

<!DOCTYPE html>

<head>

</head>

<body>

</div>

<label for="txtFname">First Name:</label>

</div>

</div>

<label for="txtEmail">Email:</label>

</div>

<label for="txtMobile">Mobile:</label>

</div>

<label for="txtAddress">Address:</label>

</div>

</form>

</div>

</body>

</html>

PHP MySQL Read

Create a new PHP file index.php in this file in add the below code.
The following code shows how to fetch all the records from the database in the index file.

<!DOCTYPE html>
<html lang="en">
<head>
  <title></title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>
	<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
	<script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>		
	<link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
</head>
<body>
	<div class="container">
		<div class="row" style="margin-bottom:10px;">
			<div class="col-lg-10"></div>
			<div class="col-lg-2"><a href="add.php" class="btn btn-primary">Add</a></div>
		</div>
		<table class='display dataTable table table-bordered table-striped  no-footer' align="center">
			<thead>
			<tr>
				<th>First Name</th>
				<th>Last Name</th>
				<th>Address</th>
				<th>Email</th>
				<th>Mobile</th>
				<th>Action</th>
			</tr>
			</thead>
			<?php
			include('connect.php');
			$slt="select * from users";
			$query=$db->prepare($slt);
			$query->execute();
			while($row=$query->fetch(PDO::FETCH_ASSOC))
			{
				?>
				<tr>
					<td><?php echo $row['first_name']; ?></td>
					<td><?php echo $row['last_name']; ?></td>
					<td><?php echo $row['address']; ?></td>
					<td><?php echo $row['email']; ?></td>
					<td><?php echo $row['mobile']; ?></td>
					<td><a class="btn btn-primary" href="edit.php?edit_id=<?php echo $row['id'];?>">Edit</a>&nbsp;&nbsp;<a class="btn btn-danger" href="delete.php?delete_id=<?php echo $row['id'];?>" onclick="return confirm('Are you sure you want to delete this data?');">Delete</a> </td>
				</tr>
				<?php
			}
			?>
		   
		</table>
	</div>
</body>
</html>

<!DOCTYPE html>

<head>

</head>

<body>

</div>

<thead>

<tr>

<th>First Name</th>

<th>Address</th>

<th>Email</th>

<th>Mobile</th>

<th>Action</th>

</tr>

</thead>

<?php

include('connect.php');

$slt="select * from users";

$query=$db->prepare($slt);

$query->execute();

while($row=$query->fetch(PDO::FETCH_ASSOC))

{

<tr>

<td><a class="btn btn-primary" href="edit.php?edit_id=<?php echo $row['id'];?>">Edit</a>  <a class="btn btn-danger" href="delete.php?delete_id=<?php echo $row['id'];?>" onclick="return confirm('Are you sure you want to delete this data?');">Delete</a> </td>

</tr>

<?php

}

</table>

</div>

</body>

</html>

Php MySQL Update Record

Create a new edit.php file in this file to add the below code.
First, we will fetch a particular data record in the update form. On submitting edited user information we form an update query to edit the record with the reference of its edit_id.

<?php
$edit_id = $_REQUEST['edit_id'];
include('connect.php');
$slt="select * from users where id=:id";
$query=$db->prepare($slt);
$query->bindParam(':id',$edit_id,PDO::PARAM_STR);
$query->execute();
$row=$query->fetch(PDO::FETCH_ASSOC);
?>
<?php
if(isset($_POST['btnUpdate']))
{
    $first_name  = $_POST['txtFname'];
    $last_name   = $_POST['txtLname'];
    $address     = $_POST['txtAddress'];
    $email       = $_POST['txtEmail'];
    $mobile      = $_POST['txtMobile'];

    include('connect.php');
    $sql="update users set first_name=:first_name,last_name=:last_name,address=:address,email=:email,mobile=:mobile where id=:id";
	$query = $db->prepare($sql);

	$query->bindParam(':first_name',$first_name,PDO::PARAM_STR);
	$query->bindParam(':last_name',$last_name,PDO::PARAM_STR);
	$query->bindParam(':address',$address,PDO::PARAM_STR);
	$query->bindParam(':email',$email,PDO::PARAM_STR);
	$query->bindParam(':mobile',$mobile,PDO::PARAM_STR);
	$query->bindParam(':id',$edit_id,PDO::PARAM_STR);
	$query->execute();
    header('location:index.php');
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
  <title></title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>
	<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
	<script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>		
	<link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
</head>
<body>
	<div class="container">
		<div class="row" style="margin-bottom:10px;">
			<div class="col-lg-10"></div>
			<div class="col-lg-2"><a href="index.php" class="btn btn-primary">Back</a></div>
		</div>
		<form method="post" name="frmUpdate">
		  <div class="form-group">
			<label for="txtFname">First Name:</label>
			<input type="text" class="form-control" name="txtFname" required id="txtFname" value="<?php echo $row['first_name'];?>">
		  </div>
		  <div class="form-group">
			<label for="txtLname">Last Name:</label>
			<input type="text" class="form-control" name="txtLname" required id="txtLname" value="<?php echo $row['last_name'];?>">
		  </div>
		   <div class="form-group">
			<label for="txtEmail">Email:</label>
			<input type="email" class="form-control" name="txtEmail" required id="txtEmail" value="<?php echo $row['email'];?>">
		  </div>
		  <div class="form-group">
			<label for="txtMobile">Mobile:</label>
			<input type="text" class="form-control" name="txtMobile" required id="txtMobile" value="<?php echo $row['mobile'];?>">
		  </div>
		  <div class="form-group">
			<label for="txtAddress">Address:</label>
			<textarea name="txtAddress" class="form-control" required id="txtAddress"><?php echo $row['address'];?></textarea>
		  </div>
		  <button type="submit" class="btn btn-success" value="" name="btnUpdate">Update</button>
		</form>
	</div>
</body>
</html>

<?php

$edit_id = $_REQUEST['edit_id'];

include('connect.php');

$slt="select * from users where id=:id";

$query=$db->prepare($slt);

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

$query->execute();

$row=$query->fetch(PDO::FETCH_ASSOC);

<?php

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

{

$first_name = $_POST['txtFname'];

$last_name = $_POST['txtLname'];

$address = $_POST['txtAddress'];

$email = $_POST['txtEmail'];

$mobile = $_POST['txtMobile'];

include('connect.php');

$sql="update users set first_name=:first_name,last_name=:last_name,address=:address,email=:email,mobile=:mobile where id=:id";

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

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

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

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

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

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

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

$query->execute();

header('location:index.php');

}

<!DOCTYPE html>

<head>

</head>

<body>

</div>

<label for="txtFname">First Name:</label>

</div>

</div>

<label for="txtEmail">Email:</label>

</div>

<label for="txtMobile">Mobile:</label>

</div>

<label for="txtAddress">Address:</label>

</div>

<button type="submit" class="btn btn-success" value="" name="btnUpdate">Update</button>

</form>

</div>

</body>

</html>

Delete Record from MySQL Database

Create a new delete.php file in this file to add the below code.
The below code is used to delete a particular record from the database.

<?php
include('connect.php');
$sql = "delete from users WHERE id=:id";
$query = $db->prepare($sql);
$query->bindParam(':id',$_REQUEST['delete_id'], PDO::PARAM_STR);
$query->execute();
header("location:index.php");
?>

<?php

include('connect.php');

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

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

$query->bindParam(':id',$_REQUEST['delete_id'], PDO::PARAM_STR);

$query->execute();

header("location:index.php");

We think would you like this article, so you can click on “Show Demo” button and you can see this demo article.

Show Demo

Download

Please follow and like us: