PHP PDO CRUD stands for Create, Read, Update, and Delete operations using PHP Data Objects (PDO) to interact with a database. This approach is secure and efficient because it uses prepared statements to prevent SQL injection attacks and also employs sanitization and filtering techniques to ensure data integrity.
This example demonstrates a basic PHP PDO CRUD application with Bootstrap 5 for styling and a teachers table as our database entity . It includes Create, Read, Update, and Delete operations with sanitization, filtering, and prepared statements to ensure security and data integrity. Remember to replace 'username' and 'password' with your actual MySQL credentials.
Step 1: Set Up the Database
First, create a database named school and a table named teachers with the following structure:
CREATE DATABASE school;
USE school;
CREATE TABLE teachers (
id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
subject VARCHAR(100)
);
Step 2: Create Connection and Bootstrap CSS
Create a file named index.php and include Bootstrap CSS in the head section:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>Teachers CRUD</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/5.0.0/css/bootstrap.min.css">
</head>
<body>
<!-- Your CRUD code will go here -->
</body>
</html>
Step 3: Create Connection and Helper Functions
Create a file named db.php to handle the database connection and some helper functions:
<?php
$servername = "localhost";
$username = "username"; // Your MySQL username
$password = "password"; // Your MySQL password
$dbname = "school";
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();
}
function sanitize_input($data)
{
return htmlspecialchars(stripslashes(trim($data)));
}
function fetch_teachers()
{
global $conn;
$stmt = $conn->prepare("SELECT * FROM teachers");
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
Step 4: Create Read Operation
Modify index.php to include the Read operation:
<?php
include 'db.php';
if ($_SERVER["REQUEST_METHOD"] == "GET") {
$teachers = fetch_teachers();
?>
<div class="container mt-5">
<h1>Teachers List</h1>
<table class="table">
<thead>
<tr>
<th>ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Subject</th>
</tr>
</thead>
<tbody>
<?php foreach ($teachers as $teacher) : ?>
<tr>
<td><?= $teacher['id']; ?></td>
<td><?= $teacher['first_name']; ?></td>
<td><?= $teacher['last_name']; ?></td>
<td><?= $teacher['subject']; ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</div>
<?php
}
?>
Step 5: Create Create Operation
Add a link to the Create operation in index.php:
<a href="create.php" class="btn btn-primary">Add Teacher</a>
Create a file named create.php:
<?php
include 'db.php';
if ($_SERVER["REQUEST_METHOD"] == "POST") {
$first_name = sanitize_input($_POST['first_name']);
$last_name = sanitize_input($_POST['last_name']);
$subject = sanitize_input($_POST['subject']);
$stmt = $conn->prepare("INSERT INTO teachers (first_name, last_name, subject) VALUES (:first_name, :last_name, :subject)");
$stmt->bindParam(':first_name', $first_name);
$stmt->bindParam(':last_name', $last_name);
$stmt->bindParam(':subject', $subject);
$stmt->execute();
header("Location: index.php");
}
?>
<div class="container mt-5">
<h1>Add Teacher</h1>
<form method="post">
<div class="mb-3">
<label for="first_name" class="form-label">First Name</label> <input
type="text" class="form-control" id="first_name" name="first_name"
required>
</div>
<div class="mb-3">
<label for="last_name" class="form-label">Last Name</label> <input
type="text" class="form-control" id="last_name" name="last_name"
required>
</div>
<div class="mb-3">
<label for="subject" class="form-label">Subject</label> <input
type="text" class="form-control" id="subject" name="subject"
required>
</div>
<button type="submit" class="btn btn-primary">Submit</button>
</form>
</div>
Step 6: Create Update Operation
Add a link to the Update operation in the teachers list in index.php:
<a href="edit.php?id=<?= $teacher['id']; ?>" class="btn btn-info">Edit</a>
Create a file named edit.php:
<?php
include 'db.php';
if ($_SERVER["REQUEST_METHOD"] == "GET") {
$id = sanitize_input($_GET['id']);
$stmt = $conn->prepare("SELECT * FROM teachers WHERE id = :id");
$stmt->bindParam(':id', $id);
$stmt->execute();
$teacher = $stmt->fetch(PDO::FETCH_ASSOC);
}
if ($_SERVER["REQUEST_METHOD"] == "POST") {
$id = sanitize_input($_POST['id']);
$first_name = sanitize_input($_POST['first_name']);
$last_name = sanitize_input($_POST['last_name']);
$subject = sanitize_input($_POST['subject']);
$stmt = $conn->prepare("UPDATE teachers SET first_name = :first_name, last_name = :last_name, subject = :subject WHERE id = :id");
$stmt->bindParam(':first_name', $first_name);
$stmt->bindParam(':last_name', $last_name);
$stmt->bindParam(':subject', $subject);
$stmt->bindParam(':id', $id);
$stmt->execute();
header("Location: index.php");
}
?>
<div class="container mt-5">
<h1>Edit Teacher</h1>
<form method="post">
<input type="hidden" name="id" value="<?= $teacher['id']; ?>">
<div class="mb-3">
<label for="first_name" class="form-label">First Name</label> <input
type="text" class="form-control" id="first_name" name="first_name"
value="<?= $teacher['first_name']; ?>" required>
</div>
<div class="mb-3">
<label for="last_name" class="form-label">Last Name</label> <input
type="text" class="form-control" id="last_name" name="last_name"
value="<?= $teacher['last_name']; ?>" required>
</div>
<div class="mb-3">
<label for="subject" class="form-label">Subject</label> <input
type="text" class="form-control" id="subject" name="subject"
value="<?= $teacher['subject']; ?>" required>
</div>
<button type="submit" class="btn btn-primary">Save Changes</button>
</form>
</div>
Step 7: Create Delete Operation
Add a form with a delete button in the teachers list in index.php:
<form action="delete.php" method="post">
<input type="hidden" name="id" value="<?= $teacher['id']; ?>">
<button type="submit" class="btn btn-danger">Delete</button>
</form>
Create a file named delete.php:
<?php
include 'db.php';
if ($_SERVER["REQUEST_METHOD"] == "POST") {
$id = sanitize_input($_POST['id']);
$stmt = $conn->prepare("DELETE FROM teachers WHERE id = :id");
$stmt->bindParam(':id', $id);
$stmt->execute();
header("Location: index.php");
}
?>
