PHP PDO CRUD
by Vincy. Last modified on July 10th, 2022.
In this tutorial we are creating an example to do database operations Create, Read, Update and Delete (CRUD) in PHP using PDO connection. In a previous tutorial, we have already learned about how to do database CRUD using MySQLi
In this example, we have taken a posts table to do CRUD via PDO. The SQL script for this table is given with the source code download. We create PDO connection in a common file and include this file wherever the database connection is required.
Creating PDO Connection
The following code shows how to create PDO connection in PHP.
<?php
$database_username = 'root';
$database_password = '';
$pdo_conn = new PDO( 'mysql:host=localhost;dbname=blog_samples', $database_username, $database_password );
?>
Create New Row in Database Table
The code shows the HTML for the add form and the PHP script to be executed to get PDO connection to perform Create operation. On submitting this form, the fields are added in a new row.
<?php
if(!empty($_POST["add_record"])) {
require_once("db.php");
$sql = "INSERT INTO posts ( post_title, description, post_at ) VALUES ( :post_title, :description, :post_at )";
$pdo_statement = $pdo_conn->prepare( $sql );
$result = $pdo_statement->execute( array( ':post_title'=>$_POST['post_title'], ':description'=>$_POST['description'], ':post_at'=>$_POST['post_at'] ) );
if (!empty($result) ){
header('location:index.php');
}
}
?>
<html>
<head>
<title>PHP PDO CRUD - Add New Record</title>
</head>
<body>
<div style="margin:20px 0px;text-align:right;"><a href="index.php" class="button_link">Back to List</a></div>
<div class="frm-add">
<h1 class="demo-form-heading">Add New Record</h1>
<form name="frmAdd" action="" method="POST">
<div class="demo-form-row">
<label>Title: </label><br>
<input type="text" name="post_title" class="demo-form-field" required />
</div>
<div class="demo-form-row">
<label>Description: </label><br>
<textarea name="description" class="demo-form-field" rows="5" required ></textarea>
</div>
<div class="demo-form-row">
<label>Date: </label><br>
<input type="date" name="post_at" class="demo-form-field" required />
</div>
<div class="demo-form-row">
<input name="add_record" type="submit" value="Add" class="demo-form-submit">
</div>
</form>
</div>
</body>
</html>
PHP PDO Read
After creating a new row in the database table, we are going to fetch all records via PDO connection and list them in a list page. The code is,
<?php
require_once("db.php");
?>
<html>
<head>
<title>PHP PDO CRUD</title>
<style>
body{width:615px;font-family:arial;letter-spacing:1px;line-height:20px;}
.tbl-qa{width: 100%;font-size:0.9em;background-color: #f5f5f5;}
.tbl-qa th.table-header {padding: 5px;text-align: left;padding:10px;}
.tbl-qa .table-row td {padding:10px;background-color: #FDFDFD;vertical-align:top;}
.button_link {color:#FFF;text-decoration:none; background-color:#428a8e;padding:10px;}
</style>
</head>
<body>
<?php
$pdo_statement = $pdo_conn->prepare("SELECT * FROM posts ORDER BY id DESC");
$pdo_statement->execute();
$result = $pdo_statement->fetchAll();
?>
<div style="text-align:right;margin:20px 0px;"><a href="add.php" class="button_link"><img src="crud-icon/add.png" title="Add New Record" style="vertical-align:bottom;" /> Create</a></div>
<table class="tbl-qa">
<thead>
<tr>
<th class="table-header" width="20%">Title</th>
<th class="table-header" width="40%">Description</th>
<th class="table-header" width="20%">Date</th>
<th class="table-header" width="5%">Actions</th>
</tr>
</thead>
<tbody id="table-body">
<?php
if(!empty($result)) {
foreach($result as $row) {
?>
<tr class="table-row">
<td><?php echo $row["post_title"]; ?></td>
<td><?php echo $row["description"]; ?></td>
<td><?php echo $row["post_at"]; ?></td>
<td><a class="ajax-action-links" href='edit.php?id=<?php echo $row['id']; ?>'><img src="crud-icon/edit.png" title="Edit" /></a> <a class="ajax-action-links" href='delete.php?id=<?php echo $row['id']; ?>'><img src="crud-icon/delete.png" title="Delete" /></a></td>
</tr>
<?php
}
}
?>
</tbody>
</table>
</body>
</html>
Update Database Row
In update form, we get values from the database for a particular record and populate those values in the form fields. The code shows update query to be executed using PDO connection to do database update.
<?php
require_once("db.php");
if(!empty($_POST["save_record"])) {
$pdo_statement=$pdo_conn->prepare("update posts set post_title='" . $_POST[ 'post_title' ] . "', description='" . $_POST[ 'description' ]. "', post_at='" . $_POST[ 'post_at' ]. "' where id=" . $_GET["id"]);
$result = $pdo_statement->execute();
if($result) {
header('location:index.php');
}
}
$pdo_statement = $pdo_conn->prepare("SELECT * FROM posts where id=" . $_GET["id"]);
$pdo_statement->execute();
$result = $pdo_statement->fetchAll();
?>
Deleting Row using PDO
The following PHP code shows how to fire delete query using PDO connection.
<?php
require_once("db.php");
$pdo_statement=$pdo_conn->prepare("delete from posts where id=" . $_GET['id']);
$pdo_statement->execute();
header('location:index.php');
?>
Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.
Hi, I'm Vincy. I help build websites, grow businesses, big and small. If that is what you want, contact me.
I'm currently available for freelance work.