CRUD stands for Create, Read, Update, and Delete. These four operations are used to manage records in a database. In this lesson, we will use PHP PDO and MySQL to manage student records.
CRUD represents the four basic database operations:
| Operation | SQL Statement | Example |
|---|---|---|
| Create | INSERT | Add a student |
| Read | SELECT | Display students |
| Update | UPDATE | Change a course |
| Delete | DELETE | Remove a student |
Before starting, create the school database
and the students table from the previous
PHP-MySQL lesson.
We will reuse the database connection file created in the previous lesson.
File: db.php
<?php
$host = "localhost";
$dbname = "school";
$username = "root";
$password = "";
$dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";
try {
$pdo = new PDO(
$dsn,
$username,
$password,
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]
);
} catch (PDOException $e) {
error_log($e->getMessage());
http_response_code(500);
exit("Database connection failed.");
}
?>Save this file in the same directory as your CRUD page, or update the include path if your project uses a different folder structure.
The examples assume that the PDO MySQL extension is enabled and the database credentials are correct.
The INSERT statement adds a new record. PDO prepared statements allow us to pass values separately from SQL.
<?php
require_once "db.php";
$name = "Rahul Kumar";
$email = "rahul@example.com";
$course = "PHP Programming";
$sql = "INSERT INTO students (name, email, course)
VALUES (:name, :email, :course)";
$stmt = $pdo->prepare($sql);
$stmt->execute([
"name" => $name,
"email" => $email,
"course" => $course
]);
echo "Student added successfully.";
?>Output: Student added successfully.
The query assumes the table contains the
name, email, and
course columns. The database generates the
student ID automatically if the table uses
AUTO_INCREMENT.
In a real registration form, validate all input before executing the INSERT statement.
The SELECT statement retrieves records. The
fetchAll() method returns all matching rows
as an array.
<?php
require_once "db.php";
$stmt = $pdo->query(
"SELECT id, name, email, course
FROM students
ORDER BY id DESC"
);
$students = $stmt->fetchAll();
?>
<table border="1" cellpadding="8">
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
<th>Course</th>
</tr>
<?php foreach ($students as $student): ?>
<tr>
<td><?= (int) $student["id"] ?></td>
<td><?= htmlspecialchars(
$student["name"],
ENT_QUOTES | ENT_SUBSTITUTE,
"UTF-8"
) ?></td>
<td><?= htmlspecialchars(
$student["email"],
ENT_QUOTES | ENT_SUBSTITUTE,
"UTF-8"
) ?></td>
<td><?= htmlspecialchars(
$student["course"],
ENT_QUOTES | ENT_SUBSTITUTE,
"UTF-8"
) ?></td>
</tr>
<?php endforeach; ?>
</table>The HTML escaping prevents database values from being interpreted as HTML markup. For large tables, use pagination instead of fetching every record at once.
The UPDATE statement changes an existing record. Always use a WHERE condition when you intend to update only one student.
<?php
require_once "db.php";
$id = 1;
$newCourse = "Full Stack Development";
$sql = "UPDATE students
SET course = :course
WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute([
"course" => $newCourse,
"id" => $id
]);
echo "Update query executed.";
?>
This executes an update for the student whose ID is
1, if that record exists.
The message means the query executed successfully;
it does not necessarily mean a record changed.
Check $stmt->rowCount() when you need to
know how many rows were affected. Depending on the driver,
unchanged values may affect the reported count.
The DELETE statement removes records from a database table.
<?php
require_once "db.php";
$id = 1;
$sql = "DELETE FROM students WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute([
"id" => $id
]);
if ($stmt->rowCount() > 0) {
echo "Student deleted successfully.";
} else {
echo "No matching student was found.";
}
?>
This example deletes the record with ID
1 if it exists.
In a real application, obtain the ID from a validated request, verify the user's permission, and consider requiring confirmation before deleting a record.
Prepared statements are also useful when searching for records using user input.
<?php
require_once "db.php";
$search = "Rahul";
$sql = "SELECT id, name, email, course
FROM students
WHERE name LIKE :search
ORDER BY name";
$stmt = $pdo->prepare($sql);
$stmt->execute([
"search" => "%" . $search . "%"
]);
$students = $stmt->fetchAll();
foreach ($students as $student) {
echo htmlspecialchars(
$student["name"],
ENT_QUOTES | ENT_SUBSTITUTE,
"UTF-8"
) . "<br>";
}
?>
The LIKE operator searches for names
containing the specified text. The percent sign
(%) matches any sequence of characters.
Prepared statements protect the query structure, but the percent and underscore characters still act as SQL LIKE wildcards. Escape them separately if you want users to search for those characters literally.
Database queries can fail because of invalid data, missing tables, connection problems, or database constraints. Use exception handling to handle failures.
<?php
require_once "db.php";
try {
$stmt = $pdo->prepare(
"INSERT INTO students (name, email, course)
VALUES (:name, :email, :course)"
);
$stmt->execute([
"name" => "Priya Singh",
"email" => "priya@example.com",
"course" => "Web Development"
]);
echo "Student inserted successfully.";
} catch (PDOException $e) {
error_log($e->getMessage());
echo "Unable to save student details.";
}
?>The technical error is recorded in the server log, while the visitor sees a generic message. Do not expose raw database errors or credentials on public pages.
In applications where multiple database changes must
succeed together, consider transactions using
beginTransaction(), commit(),
and rollBack().
This mini project inserts student details using a form
and displays the existing records. Save it as
student_crud.php in the same directory
as db.php.
<?php
require_once "db.php";
$message = "";
if ($_SERVER["REQUEST_METHOD"] === "POST") {
$name = trim($_POST["name"] ?? "");
$email = trim($_POST["email"] ?? "");
$course = trim($_POST["course"] ?? "");
if ($name === "" || $course === "") {
$message = "Name and course are required.";
} elseif (
filter_var($email, FILTER_VALIDATE_EMAIL) === false
) {
$message = "Enter a valid email address.";
} else {
try {
$stmt = $pdo->prepare(
"INSERT INTO students (name, email, course)
VALUES (:name, :email, :course)"
);
$stmt->execute([
"name" => $name,
"email" => $email,
"course" => $course
]);
$message = "Student added successfully.";
} catch (PDOException $e) {
error_log($e->getMessage());
$message = "Unable to add student.";
}
}
}
$stmt = $pdo->query(
"SELECT id, name, email, course
FROM students
ORDER BY id DESC"
);
$students = $stmt->fetchAll();
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Student CRUD</title>
</head>
<body>
<h2>Add Student</h2>
<form method="post">
<label for="name">Name:</label>
<input type="text" id="name" name="name" required>
<br><br>
<label for="email">Email:</label>
<input type="email" id="email" name="email" required>
<br><br>
<label for="course">Course:</label>
<input type="text" id="course" name="course" required>
<br><br>
<button type="submit">Add Student</button>
</form>
<p>
<?= htmlspecialchars(
$message,
ENT_QUOTES | ENT_SUBSTITUTE,
"UTF-8"
) ?>
</p>
<h2>Student Records</h2>
<table border="1" cellpadding="8">
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
<th>Course</th>
</tr>
<?php foreach ($students as $student): ?>
<tr>
<td><?= (int) $student["id"] ?></td>
<td><?= htmlspecialchars(
$student["name"],
ENT_QUOTES | ENT_SUBSTITUTE,
"UTF-8"
) ?></td>
<td><?= htmlspecialchars(
$student["email"],
ENT_QUOTES | ENT_SUBSTITUTE,
"UTF-8"
) ?></td>
<td><?= htmlspecialchars(
$student["course"],
ENT_QUOTES | ENT_SUBSTITUTE,
"UTF-8"
) ?></td>
</tr>
<?php endforeach; ?>
</table>
</body>
</html>This mini project demonstrates Create and Read operations. Extend it with separate edit and delete forms to complete all four CRUD operations.
For a production application, add CSRF protection, server-side length limits, authorization checks, and redirect-after-POST behavior to prevent accidental duplicate submissions when a page is refreshed.
1. What does CRUD stand for?
2. Which PDO method prepares an SQL statement?
3. Which SQL statement modifies existing records?