In this tutorial I will show you how to create CRUD PDO application using PHP and MySQL
What is PDO?
PDO = PHP Data Objects. This is a PHP extension that defines a consistent and lightweight interface for accessing databases.
CRUD = Create/Read/Update/Delete. It means, any basic application that has ability to for creating, deleting, updating and reading the records in the database. Every database application like Inventory System, Accounting System, Point of Sale and other Business-related applications uses CRUD as basis of its database records manipulations.
Contents for PHP PDO CRUD
Creating a database having a table
Establishing the database connection
Creating and Reading records
Edit records
Delete a record
Program Listing
connect.php
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "crud_pdo;
";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
echo "Connection failed: " . $e->getMessage();
}
?>
index.php
<?php
include("connect.php");
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>CRUD using PDO in PHP and MySQL</title>
</head>
<body>
<h2>CRUD using PDO in PHP and MySQL</h2>
<h4>Add Record in the Database</h4>
<form action="create.php" method="post" enctype="application/x-www-form-urlencoded">
<label>Name</label>
<input type="text" name="name" size=50 required="required" /><br /><br />
<label>Telephone Number</label>
<input type="text" name="telephone" size=50 required="required" /><br /><br />
<label>Mobile Number</label>
<input type="text" name="mobile" size=50 required="required" /><br /><br />
<label>Email Address</label>
<input type="email" name="email" size=50 required="required" /><br /><br />
<input type="submit" name="submit" required="required" value="submit" />
</form><br><br>
<table border="1" width="900px" >
<tr>
<th>ID No.</th>
<th>Name</th>
<th>Telephone Number</th>
<th>Mobile Number</th>
<th>Email Address</th>
<th>Action</th>
</tr>
<?php
$get_datas = $conn->prepare("SELECT * FROM crud");
$get_datas->execute();
if($get_datas->rowCount()>0){
$i=1;
while($res=$get_datas->fetch(PDO::FETCH_ASSOC)){
?>
<tr>
<td align="center"><?php echo $i++; ?></td>
<td align="center"><?php echo $res['name']; ?></td>
<td align="center"><?php echo $res['telephone']; ?></td>
<td align="center"><?php echo $res['mobile']; ?></td>
<td align="center"><?php echo $res['email']; ?></td>
<td><a href="edit.php?id=<?php echo $res['id'];?>">Edit</a><br /><a href="delete.php?id=<?php echo $res['id'];?>">Delete</a></td>
</tr>
<?php } }else{
echo "<tr><td colspan='5'>Records not found</td></tr>";
} ?>
</table>
<script type="text/javascript">
<?php if($_GET['message']){ ?>
alert('<?php echo $_GET['message'];?>');
<?php } ?>
</script>
</body>
</html>
edit.php
<?php
include("connect.php");
$id = $_GET['id'];
$get_data = $conn->prepare("select * from crud where id = :id");
$get_data->bindParam(":id",$id);
$get_data->execute();
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>CRUD using PDO in PHP and MySQL</title>
</head>
<body>
<h2>Update Record in the Databae</h2>
<?php if($get_data->rowCount()>0){
$result = $get_data->fetch(PDO::FETCH_ASSOC);
?>
<form action="update.php?id=<?php echo $id; ?>" method="post" enctype="application/x-www-form-urlencoded">
<label>Name</label>
<input type="text" name="name" required="required" value="<?php if(isset($result['name'])){ echo $result['name']; } ?>" /><br /><br />
<label>Telephone Number</label>
<input type="text" name="telephone" required="required" value="<?php if(isset($result['telephone'])){ echo $result['telephone']; } ?>" /><br /><br />
<label>Mobile Number</label>
<input type="text" name="mobile" required="required" value="<?php if(isset($result['mobile'])){ echo $result['mobile']; } ?>" /><br /><br />
<label>Email Address</label>
<input type="email" name="email" required="required" value="<?php if(isset($result['email'])){ echo $result['email']; } ?>" /><br /><br />
<input type="submit" name="submit" required="required" value="submit" />
</form>
<?php } else {
echo "Invalid Request";
} ?>
<script type="text/javascript">
<?php if($_GET['message']){ ?>
alert('<?php echo $_GET['message'];?>');
<?php } ?>
</script>
</body>
</html>
update.php
<?php
include("connect.php");
$id = $_GET['id'];
$name = "";
$telephone="";
$email = "";
$mobile = "";
if (isset($_POST['submit'])) {
$name = filter_var($_POST['name'], FILTER_SANITIZE_STRING); // to filter string
$telephone = filter_var($_POST['telephone'], FILTER_SANITIZE_EMAIL); // to filter email
$email = filter_var($_POST['email'], FILTER_SANITIZE_EMAIL); // to filter email
$mobile = filter_var($_POST['mobile'], FILTER_SANITIZE_NUMBER_INT); // to filter number
$check_mobile = $conn->prepare("select * from crud where mobile = '" . $mobile . "' and id not in ('".$id."')"); // to check duplicate
$check_mobile->execute();
if ($check_mobile->rowCount() > 0) {
header("Location: index.php?message=Duplicate entry");
} else {
$insert_query = $conn->prepare("update crud set name = :name, telephone=:telephone, email=:email, mobile=:mobile where id = :id"); //to insert data
try {
$conn->beginTransaction();
$insert_query->bindParam(":name", $name);
$insert_query->bindParam(":telephone", $telephone);
$insert_query->bindParam(":email", $email);
$insert_query->bindParam(":mobile", $mobile);
$insert_query->bindParam(":id", $id);
$count = $insert_query->execute();
if ($count> 0) {
header("Location: edit.php?id=$id&message=Record has been updated successfully"); //success data insertion
header("location:index.php");
} else {
header("Location: index.php?id=$id&message=Failed to update"); //failure data insertion
}
$conn->commit();
}
catch (PDOExecption $e) {
$dbh->rollback();
print "Error!: " . $conn->getMessage() . "</br>"; //exception
}
}
}
?>
create.php
<?php
// create.php
// insert record in the table
include("connect.php");
$name = "";
$telephone = "";
$mobile = "";
$email = "";
if (isset($_POST['submit'])) {
$name = filter_var($_POST['name'], FILTER_SANITIZE_STRING); // to filter string
$telephone = filter_var($_POST['telephone'], FILTER_SANITIZE_EMAIL); // to filter email
$mobile = filter_var($_POST['mobile'], FILTER_SANITIZE_NUMBER_INT); // to filter number
$email = filter_var($_POST['email'], FILTER_SANITIZE_EMAIL); // to filter email
$check_mobile = $conn->prepare("SELECT * FROM crud WHERE mobile = '" . $mobile . "'"); // to check duplicate records
$check_mobile->execute();
if ($check_mobile->rowCount() > 0) {
header("Location: index.php?message=Duplicate entry");
} else {
$insert_query = $conn->prepare("INSERT INTO crud (name,telephone,mobile,email) VALUES (:name,:telephone,:mobile,:email)"); //to insert data in the table
try {
$conn->beginTransaction();
$insert_query->bindParam(":name", $name);
$insert_query->bindParam(":telephone", $telephone);
$insert_query->bindParam(":mobile", $mobile);
$insert_query->bindParam(":email", $email);
$insert_query->execute();
if ($conn->lastInsertId() > 0) {
header("Location: index.php?message=Record has been inserted successfully"); //success data insertion
} else {
header("Location: index.php?message=Failed to insert"); //failure data insertion
}
$conn->commit();
}
catch (PDOExecption $e) {
$dbh->rollback();
print "Error!: " . $conn->getMessage() . "</br>"; //exception
}
}
}
?>
crud_pdo.sql
/*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 10.4.14-MariaDB
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
create table `crud` (
`id` int (11),
`name` varchar (600),
`telephone` varchar (300),
`mobile` varchar (300),
`email` varchar (300)
);
insert into `crud` (`id`, `name`, `telephone`, `mobile`, `email`) values('3','Jacob Samuel Pomperada','4335091','09173084334','jacobsamuel@yahoo.com');
insert into `crud` (`id`, `name`, `telephone`, `mobile`, `email`) values('4','Jake Pomperada','4335081','09173084360','jakerpomperada@gmail.com');
