PHP PDO Prepared Statement Crud CRUD operations in PHP
Updated on
...
11th September 2023
In this tutorial, I am going to show you how can perform CRUD Operations using PHP PDO Prepared statement. Implementing PDO-prepared statements has benefits on several levels, including improved performance and High security. In the previous tutorial, we learned about how we can perform CRUD operations using PHP MySQLi Prepared Statement
PDO is a database access layer that helps PHP programs connect and work with databases quickly and reliably. To use PDO with different types of databases, you need to install specific PDO drivers.
To perform the CRUD operations using PDO in this example, I am creating a student table in the database. In order to perform CRUD operations, First we have to connect the database and then construct the statement for the INSERT, UPDATE, DELETE, and SELECT queries.
Let's see the folder structure:
Create Database and table:
Now we will create our table after creating the database just past below SQL code below in the PHP MY ADMIN SQL tab
CREATE TABLE `students` (
`id` int(11) NOT NULL,
`name` varchar(100) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
`phone` varchar(20) DEFAULT NULL,
`gender` varchar(20) DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- Indexes for table `students`
--
ALTER TABLE `students`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `students`
--
ALTER TABLE `students`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=44;
COMMIT;
Create dbconn.php for creating the database connection
Create an include folder inside the project directory and then create dbconn.php inside that folder and paste the code.
<?php
session_start();
date_default_timezone_set('Asia/Kolkata');
try {
$server = 'localhost';
$user = 'root';
$pass = '';
$db = 'php_pdo_crud';
$conn = new PDO("mysql:host=$server; dbname=$db", $user, $pass);
// Set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo 'Error: ' . $e->getMessage();
$conn->close();
}
Create index.php file
In the main project directory create a file index.php and paste the below code.
This code shows the HTML table for showing the record and adds, edit, and delete buttons.
<?php
require_once('include/dbconn.php');
?>
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.2/css/all.min.css" integrity="sha512-z3gLpd7yknf1YoNbCzqRKc4qyor8gaKU1qmn+CShxbuBusANI9QpRohGBreCFkKxLhei6S9CQXFEbbKuqLg0DA==" crossorigin="anonymous" referrerpolicy="no-referrer" />
<link rel="stylesheet" href="assets/css/style.css">
</head>
<body>
<div class="ath_container">
<?php if (isset($_SESSION['success_message'])) { ?>
<div class="success_msg">
<?php echo $_SESSION['success_message']; ?>
</div>
<?php } ?>
<div class="table_head">
<h2>PHP PDO CRUD Operations</h2>
<a href="create.php" class="add_btn"><i class="fa-solid fa-plus"></i> Add New</a>
</div>
<div style="overflow-x:auto;">
<table class="custom_table">
<tr>
<th>#</th>
<th>Name</th>
<th>Email</th>
<th>Phone</th>
<th>Gender</th>
<th>Action</th>
</tr>
<?php
$query = "SELECT * FROM students ORDER BY id DESC";
$stmt = $conn->prepare($query);
$stmt->execute();
if ($stmt->execute()) {
$result = $stmt->fetchAll(PDO::FETCH_OBJ);
// $row = $result->fetch_assoc();
//print_r($row);
// Using while loop to fetch results
$num = 1;
foreach ($result as $row) {
?>
<tr>
<td><?php echo $num++ ?></td>
<td><?php echo $row->name; ?></td>
<td><?php echo $row->email; ?></td>
<td><?php echo $row->phone; ?></td>
<td><?php echo $row->gender; ?></td>
<td>
<div class="action_btn">
<a href="edit.php?id=<?php echo $row->id; ?>" class="edit"><i class="fa-solid fa-pen-to-square"></i></a>
<a href="php_action/delete.php?id=<?php echo $row->id; ?>" class="delete"><i class="fa-solid fa-trash"></i></a>
</div>
</td>
</tr>
<?php
}
}
?>
</table>
</div>
</div>
</body>
</html>
<?php
unset($_SESSION['success_message']);
//unset($_SESSION['flash_message']);
?>
Create create.php and create_record.php file
Create create.php file:
In the main project directory create a file create.php and paste the below code.
This code shows the HTML form for creating a new record in the database.
<?php
require_once('include/dbconn.php');
if (isset($_SESSION['form_errors'])) {
$errors = $_SESSION['form_errors'];
//print_r($errors);
}
?>
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.2/css/all.min.css" integrity="sha512-z3gLpd7yknf1YoNbCzqRKc4qyor8gaKU1qmn+CShxbuBusANI9QpRohGBreCFkKxLhei6S9CQXFEbbKuqLg0DA==" crossorigin="anonymous" referrerpolicy="no-referrer" />
<link rel="stylesheet" href="assets/css/style.css">
</head>
<body>
<div class="ath_container">
<div class="table_head">
<h2>Add record</h2>
<a href="index.php" class="add_btn"><i class="fa-solid fa-plus"></i> Go back to list</a>
</div>
<form action="php_action/create_record.php" class="ath_form" method="post">
<label for="name">Name</label>
<input type="text" name="name" id="name" class="c_input" value="<?php echo isset($_SESSION['submitted_values']['name']) ? $_SESSION['submitted_values']['name'] : ''; ?>">
<span class="error"><?php echo isset($errors['name']) ? $errors['name'] : ''; ?></span>
<label for="email">Email</label>
<input type="email" name="email" id="email" class="c_input" value="<?php echo isset($_SESSION['submitted_values']['email']) ? $_SESSION['submitted_values']['email'] : ''; ?>">
<span class="error"><?php echo isset($errors['email']) ? $errors['email'] : ''; ?></span>
<label for="phone">Phone</label>
<input type="text" name="phone" class="c_input" value="<?php echo isset($_SESSION['submitted_values']['phone']) ? $_SESSION['submitted_values']['phone'] : ''; ?>">
<span class="error"><?php echo isset($errors['phone']) ? $errors['phone'] : ''; ?></span>
<label for="gender">Select Gender</label>
<select name="gender" id="gender" class="c_input">
<option value="">-- Select --</option>
<option value="Male" <?php echo isset($_SESSION['submitted_values']['gender']) && $_SESSION['submitted_values']['gender'] == 'Male' ? 'selected' : ''; ?>>Male</option>
<option value="Female" <?php echo isset($_SESSION['submitted_values']['gender']) && $_SESSION['submitted_values']['gender'] == 'Female' ? 'selected' : ''; ?>>Female</option>
</select>
<span class="error"><?php echo isset($errors['gender']) ? $errors['gender'] : ''; ?></span>
<button type="submit" class="submit_btn">Submit</button>
</form>
</div>
</body>
</html>
<?php
unset($_SESSION['form_errors']);
unset($_SESSION['submitted_values']);
//unset($_SESSION['flash_message']);
?>
create create_record.php file:
In the main project directory create a file php_actions/create create_record.php and paste the below code.
This PHP code will insert the new record in the database and then return back to the index.php page. and it will return back with an error message if is there any form of validation fails.
<?php
require_once('../include/dbconn.php');
$errors = array(); // To store validation errors
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$name = trim($_POST['name']);
$email = trim($_POST['email']);
$phone = trim($_POST['phone']);
$gender = trim($_POST['gender']);
// Store submitted values in session
$_SESSION['submitted_values'] = [
'name' => $name,
'email' => $email,
'phone' => $phone,
'gender' => $gender
];
// Validation
if (empty($name)) {
$errors['name'] = "Name is required.";
}
if (empty($email)) {
$errors['email'] = "Email is required.";
} elseif (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
$errors['email'] = "Invalid email format.";
}
if (empty($phone)) {
$errors['phone'] = "Mobile Number is required.";
}
if (empty($gender)) {
$errors['gender'] = "Gender is required.";
}
// Perform additional validation for other fields
// If there are no validation errors, proceed to insert
if (empty($errors)) {
$query = "INSERT INTO students (name, email, phone, gender) VALUES (:name, :email, :phone, :gender)";
$stmt = $conn->prepare($query);
//$stmt->execute([':name' => $name, ':email' => $email, ':phone' => $phone, ':gender' => $gender]);
$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':phone', $phone);
$stmt->bindParam(':gender', $gender);
if ($stmt->execute()) {
$_SESSION['success_message'] = "Record added successfully!";
header("Location: ../index.php"); // Redirect with success message
exit;
} else {
$_SESSION['error_message'] = "Error while adding record.";
}
} else {
// Errors were found, store them in session and redirect back
$_SESSION['form_errors'] = $errors;
header("Location: ../create.php"); // Redirect with success message
exit;
}
}
Create edit.php and update_record.php file
Create edit.php file:
In the main project create a file edit.php and paste the below code.
This code shows the HTML form with the existing record for editing existing records in the database.
<?php
require_once('include/dbconn.php');
if (isset($_SESSION['form_errors'])) {
$errors = $_SESSION['form_errors'];
// print_r($errors);
}
if ($_SERVER['REQUEST_METHOD'] === 'GET' && isset($_GET['id'])) {
$id = $_GET['id'];
$query = "SELECT * FROM students WHERE id = ?";
$stmt = $conn->prepare($query);
$stmt->bindParam(1, $id);
if ($stmt->execute()) {
$result = $stmt->fetch(PDO::FETCH_OBJ);
//print_r($result);
} else {
echo "Error executing the query.";
}
}
?>
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.2/css/all.min.css" integrity="sha512-z3gLpd7yknf1YoNbCzqRKc4qyor8gaKU1qmn+CShxbuBusANI9QpRohGBreCFkKxLhei6S9CQXFEbbKuqLg0DA==" crossorigin="anonymous" referrerpolicy="no-referrer" />
<link rel="stylesheet" href="assets/css/style.css">
</head>
<body>
<div class="ath_container">
<div class="table_head">
<h2>Add record</h2>
<a href="index.php" class="add_btn"><i class="fa-solid fa-plus"></i> Go back to list</a>
</div>
<form action="php_action/update_record.php?id=<?php echo $result->id; ?>" class="ath_form" method="post" targget="_self">
<label for="name">Name</label>
<input type="text" name="name" id="name" class="c_input" value="<?php echo !empty($result->name) ? $result->name : ''; ?>">
<span class="error"><?php echo isset($errors['name']) ? $errors['name'] : ''; ?></span>
<label for="email">Email</label>
<input type="email" name="email" id="email" class="c_input" value="<?php echo !empty($result->email) ? $result->email : ''; ?>">
<span class="error"><?php echo isset($errors['email']) ? $errors['email'] : ''; ?></span>
<label for="phone">Phone</label>
<input type="text" name="phone" class="c_input" value="<?php echo !empty($result->phone) ? $result->phone : ''; ?>">
<span class="error"><?php echo isset($errors['phone']) ? $errors['phone'] : ''; ?></span>
<label for="gender">Select Gender</label>
<select name="gender" id="gender" class="c_input">
<option value="">-- Select --</option>
<option value="Male" <?php echo ($result->gender == 'Male') ? 'selected' : ''; ?>>Male</option>
<option value="Female" <?php echo ($result->gender == 'Female') ? 'selected' : ''; ?>>Female</option>
</select>
<span class="error"><?php echo isset($errors['gender']) ? $errors['gender'] : ''; ?></span>
<button type="submit" class="submit_btn">Submit</button>
</form>
</div>
</body>
</html>
<?php
unset($_SESSION['form_errors']);
unset($_SESSION['submitted_values']);
//unset($_SESSION['flash_message']);
?>
create update_record.php file:
In the main project directory create a file php_actions/create create_record.php and paste the below code.
This PHP code will Update the existing on the basis of the record ID record in the database and then return back to the index.php page. and it will return back with an error message if is there any form of validation fails.
<?php
require_once('../include/dbconn.php');
$errors = array(); // To store validation errors
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_GET['id'])) {
$id = trim($_GET['id']);
$name = trim($_POST['name']);
$email = trim($_POST['email']);
$phone = trim($_POST['phone']);
$gender = trim($_POST['gender']);
// Store submitted values in session
$_SESSION['submitted_values'] = [
'name' => $name,
'email' => $email,
'phone' => $phone,
'gender' => $gender
];
// Validation
if (empty($name)) {
$errors['name'] = "Name is required.";
}
if (empty($email)) {
$errors['email'] = "Email is required.";
} elseif (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
$errors['email'] = "Invalid email format.";
}
if (empty($phone)) {
$errors['phone'] = "Mobile Number is required.";
}
if (empty($gender)) {
$errors['gender'] = "Gender is required.";
}
// Perform additional validation for other fields
// If there are no validation errors, proceed to insert
if (empty($errors)) {
$query = "UPDATE students SET name=:name, email=:email, phone=:phone, gender=:gender WHERE id=:id";
$stmt = $conn->prepare($query);
$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':phone', $phone);
$stmt->bindParam(':gender', $gender);
$stmt->bindParam(':id', $id);
if ($stmt->execute()) {
$_SESSION['success_message'] = "Record updated successfully!";
header("Location: ../index.php"); // Redirect with success message
exit;
} else {
$_SESSION['error_message'] = "Error while updateding record.";
}
} else {
// Errors were found, store them in session and redirect back
$_SESSION['form_errors'] = $errors;
header("Location: ../edit.php?id=$id"); // Redirect with success message
exit;
}
}
Create delete.php file
Create delete.php inside the php_action folder for handling delete requests. just copy and paste the below code.
<?php
require_once('../include/dbconn.php');
if ($_SERVER['REQUEST_METHOD'] === 'GET' && isset($_GET['id'])) {
$id = trim($_GET['id']);
// Perform additional validation for other fields
$query = "DELETE FROM students WHERE id =:id";
$stmt = $conn->prepare($query);
$stmt->bindParam(':id', $id);
if ($stmt->execute()) {
$_SESSION['success_message'] = "Record deleted successfully!";
header("Location: ../index.php"); // Redirect with success message
exit;
} else {
$_SESSION['error_message'] = "Error while deleting record.";
echo "Error";
}
}
Finally, create a style.css
Now finally we are going to create a style.css file inside assets/css folder for making some good UI.
* {
margin: 0px;
padding: 0px;
box-sizing: border-box;
}
body {
font-family: Arial, Helvetica, sans-serif;
}
.ath_container {
width: 700px;
max-width: 100%;
margin: auto;
box-shadow: 0 0 5px 0 rgba(0, 0, 0, .3);
padding: 15px;
margin-top: 20px;
}
h2 {
margin-bottom: 20px;
}
.custom_table {
border-collapse: collapse;
border-spacing: 0;
width: 100%;
border: 1px solid #ddd;
}
.custom_table th,
.custom_table td {
text-align: left;
padding: 8px;
}
.custom_table tr:nth-child(even) {
background-color: #f2f2f2
}
.action_btn a {
margin-right: 15px;
display: inline-block;
}
.action_btn a:hover {
color: green;
}
.action_btn a.delete:hover {
color: red;
}
.add_btn {
background: #4e59f1;
padding: 8px 15px;
display: inline-block;
border-radius: 5px;
color: #fff;
text-decoration: none;
}
.table_head {
display: flex;
justify-content: space-between;
align-items: center;
}
.ath_form label,
.ath_form input,
.ath_form select {
display: block;
width: 100%;
}
.ath_form label {
margin-bottom: 10px;
margin-top: 15px;
}
.ath_form .c_input {
padding: 8px;
border: 1px solid #ccc;
border-radius: 4px;
}
.ath_form .c_input:focus {
border: 1px solid #6890ff;
outline: none;
}
.ath_form .submit_btn {
background: #4e59f1;
padding: 10px 20px;
display: inline-block;
border-radius: 5px;
color: #fff;
text-decoration: none;
outline: none;
box-shadow: none;
border: none;
cursor: pointer;
margin-top: 15px;
}
.ath_form .submit_btn:hover {
background: #1120f1;
}
span.error {
color: red;
}
.success_msg {
color: #44c30f;
background: #ededed;
padding: 10px;
border-radius: 4px;
margin-bottom: 15px;
}
