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
I am currently accepting programming work, IT projects, school and application development, programming projects, thesis and capstone projects, IT consulting work, computer tutorials, and web development work kindly contact me at the following email address for further details. If you want to advertise on my website kindly contact me also in my email address also. Thank you.
My email address is the following jakerpomperada@gmail.com, jakerpomperada@aol.com, and jakerpomperada@yahoo.com.
My mobile number here in the Philippines is 09173084360.
My telephone number at home here in Bacolod City, Negros Occidental Philippines is +63 (034) 4335675.
Here in Bacolod City I also accepting computer repair, networking, and Arduino Project development at a very affordable price. My website is www.jakerpomperada.blogspot.com and www.jakerpomperada.com
If you like this video please click the LIKE button, SHARE, and SUBSCRIBE to my channel.
Your support on my channel is highly appreciated.
Thank you very much.
Program Listing
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
}
}
}
?>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
}
}
}
?>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>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>Email Address</th>
<th>Mobile</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>
delete.php
<?php
include("connect.php");
if (isset($_GET['id'])) {
$id = $_GET['id'];
$checkid = $conn->prepare("select * from crud where id = '" . $id . "'"); // to check id
$checkid->execute();
if ($checkid->rowCount() > 0) {
$insert_query = $conn->prepare("delete from crud where id = :id"); //to insert data
try {
$conn->beginTransaction();
$insert_query->bindParam(":id", $id);
$count = $insert_query->execute();
if ($count> 0) {
header("Location: index.php?message=Record has been Deleted successfully"); //success data insertion
} else {
header("Location: index.php?message=Failed to Delete"); //failure data insertion
}
$conn->commit();
}
catch (PDOExecption $e) {
$dbh->rollback();
print "Error!: " . $conn->getMessage() . "</br>"; //exception
}
} else {
header("Location: index.php?message=Invalid Request");
}
}
?>
Download the Complete Source Code Here
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();
}
?>