PHP CRUD Operations

Easy PHP CRUD Operations Guide: 4 Secure Database Actions

Master backend database management with this complete PHP CRUD operations guide. Learn Create, Read, Update, and Delete using PDO prepared statements safely.

Estimated Read Time: 21 Minutes | Category: PHP Web Development

Overview: Understanding PHP CRUD Operations & Data Management

Quick PHP CRUD Operations Summary:

1. Core Data Lifecycle: CRUD stands for Create, Read, Update, and Delete—the four foundational database operations required by full-stack web applications.
2. CREATE (INSERT): Inserts new records into database tables using PDO prepared statements with bound parameter arrays.
3. READ (SELECT): Fetches existing records from database tables using fetch() for single rows or fetchAll() for dataset lists.
4. UPDATE (UPDATE): Modifies existing database record fields safely using conditional WHERE clauses.
5. DELETE (DELETE): Removes unwanted records from database tables permanently using parameterized identifier checks.

Welcome to Lesson 21 of our structured web development course. Following our previous tutorial on Easy PHP MySQL PDO Guide: 3 Secure Database Steps, you now understand how to establish secure database connections using PDO and DSN connection strings. The next vital step in building dynamic backend applications is executing PHP CRUD operations.

Whether you are building a user management dashboard, an e-commerce inventory portal, a blogging platform, or a REST API, almost every web application feature revolves around CRUD workflows. Users register new accounts (Create), view their profile settings (Read), update their delivery addresses (Update), and cancel unwanted orders (Delete).

In this comprehensive PHP CRUD operations tutorial, we will explore each CRUD component step-by-step using PDO prepared statements, input sanitization, dynamic HTML table rendering, error handling, and complete single-file CRUD application scripts.

Prerequisites Before Executing Database Operations

To test the hands-on code examples in this PHP CRUD operations guide, verify that your development environment meets these basic requirements:

• Active Local Database Server: Running installation of Apache and MySQL/MariaDB (configured via XAMPP, Homebrew, or Terminal).
• Database Management Tool: phpMyAdmin or MySQL shell access.
• PDO Connection Foundations: Understanding of PDO connection setup, prepared statements, and try-catch exception blocks.

If you need to review how PDO handles database connections securely, visit our previous guide on Easy PHP MySQL PDO Guide: 3 Secure Database Steps.

Database Schema Setup for Our CRUD Tutorial

Before executing PHP CRUD operations, create a sample database table named students inside your MySQL server using phpMyAdmin or command-line terminal:

-- SQL Schema Script to initialize our CRUD demonstration table
CREATE DATABASE IF NOT EXISTS phponline_demo;
USE phponline_demo;

CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    course VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1. CREATE Action: Inserting Records Safely

The Create operation handles adding new rows into a database table. In SQL, this is performed using the INSERT INTO statement. Always bind submitted form inputs as named parameters (:name) to block SQL Injection completely.

Create Action Code Snippet

<?php
require_once "db_connection.php"; // Loads PDO $pdo connection object

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action_create'])) {
    $fullName = trim($_POST['full_name'] ?? '');
    $email = trim($_POST['email'] ?? '');
    $course = trim($_POST['course'] ?? '');

    if (!empty($fullName) && filter_var($email, FILTER_VALIDATE_EMAIL) && !empty($course)) {
        try {
            // Prepared SQL Statement with Named Placeholders
            $sql = "INSERT INTO students (full_name, email, course) VALUES (:full_name, :email, :course)";
            $stmt = $pdo->prepare($sql);

            // Bind parameters and execute
            $stmt->execute([
                'full_name' => $fullName,
                'email'     => $email,
                'course'    => $course
            ]);

            echo "<p style='color: green;'>New student record inserted successfully! Assigned ID: " . $pdo->lastInsertId() . "</p>";
        } catch (PDOException $e) {
            echo "<p style='color: red;'>Error inserting record: " . htmlspecialchars($e->getMessage(), ENT_QUOTES, 'UTF-8') . "</p>";
        }
    } else {
        echo "<p style='color: red;'>Invalid or missing form fields!</p>";
    }
}
?>

Want to test this code live? Try running it in our PHP Online Compiler.

2. READ Action: Fetching and Rendering Records

The Read operation fetches stored records from database tables using SQL SELECT statements. Use fetchAll() when retrieving multiple records, or fetch() when retrieving a single row by ID.

Read Action Code Snippet (Displaying HTML Table)

<?php
try {
    // Fetch all student records sorted by latest addition
    $sql = "SELECT id, full_name, email, course, created_at FROM students ORDER BY id DESC";
    $stmt = $pdo->query($sql);
    $students = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    die("Database Read Error: " . $e->getMessage());
}
?>

<!-- Rendering Student Records inside an HTML Table -->
<h3>Registered Students List</h3>
<table border="1" cellpadding="10" cellspacing="0" style="width: 100%; border-collapse: collapse;">
    <thead style="background-color: #f2f2f2;">
        <tr>
            <th>ID</th>
            <th>Full Name</th>
            <th>Email Address</th>
            <th>Enrolled Course</th>
            <th>Date Joined</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        <?php if (!empty($students)): ?>
            <?php foreach ($students as $student): ?>
                <tr>
                    <td><?= $student['id']; ?></td>
                    <td><?= htmlspecialchars($student['full_name'], ENT_QUOTES, 'UTF-8'); ?></td>
                    <td><?= htmlspecialchars($student['email'], ENT_QUOTES, 'UTF-8'); ?></td>
                    <td><?= htmlspecialchars($student['course'], ENT_QUOTES, 'UTF-8'); ?></td>
                    <td><?= $student['created_at']; ?></td>
                    <td>
                        <a href="?edit_id=<?= $student['id']; ?>">Edit</a> | 
                        <a href="?delete_id=<?= $student['id']; ?>" onclick="return confirm('Are you sure you want to delete this record?');" style="color: red;">Delete</a>
                    </td>
                </tr>
            <?php endforeach; ?>
        <?php else: ?>
            <tr><td colspan="6" style="text-align: center;">No student records found in database.</td></tr>
        <?php endif; ?>
    </tbody>
</table>

Want to test this code live? Try running it in our PHP Online Compiler.

3. UPDATE Action: Modifying Existing Records

The Update operation modifies existing table field values using SQL UPDATE ... SET statements. Always include a parameterized WHERE id = :id clause; omitting the WHERE clause will overwrite every single row in your database table!

Update Action Code Snippet

<?php
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['action_update'])) {
    $studentId = filter_var($_POST['student_id'] ?? 0, FILTER_VALIDATE_INT);
    $fullName  = trim($_POST['full_name'] ?? '');
    $email     = trim($_POST['email'] ?? '');
    $course    = trim($_POST['course'] ?? '');

    if ($studentId && !empty($fullName) && filter_var($email, FILTER_VALIDATE_EMAIL) && !empty($course)) {
        try {
            // Prepared Update Query with WHERE clause check
            $sql = "UPDATE students SET full_name = :full_name, email = :email, course = :course WHERE id = :id";
            $stmt = $pdo->prepare($sql);

            $stmt->execute([
                'full_name' => $fullName,
                'email'     => $email,
                'course'    => $course,
                'id'        => $studentId
            ]);

            echo "<p style='color: green;'>Student Record #{$studentId} updated successfully!</p>";
        } catch (PDOException $e) {
            echo "<p style='color: red;'>Update Error: " . htmlspecialchars($e->getMessage(), ENT_QUOTES, 'UTF-8') . "</p>";
        }
    }
}
?>

Want to test this code live? Try running it in our PHP Online Compiler.

4. DELETE Action: Removing Records Securely

The Delete operation permanently removes a specific row from a database table using the SQL DELETE FROM ... WHERE id = :id statement. Always validate the target record ID and use prepared statements to prevent unauthorized record deletion.

Delete Action Code Snippet

<?php
if (isset($_GET['delete_id'])) {
    $deleteId = filter_var($_GET['delete_id'], FILTER_VALIDATE_INT);

    if ($deleteId) {
        try {
            // Prepared Delete Query
            $sql = "DELETE FROM students WHERE id = :id";
            $stmt = $pdo->prepare($sql);
            $stmt->execute(['id' => $deleteId]);

            echo "<p style='color: red;'>Student Record #{$deleteId} deleted from database!</p>";
        } catch (PDOException $e) {
            echo "<p style='color: red;'>Delete Error: " . htmlspecialchars($e->getMessage(), ENT_QUOTES, 'UTF-8') . "</p>";
        }
    }
}
?>

Want to test this code live? Try running it in our PHP Online Compiler.

Summary Comparison of 4 CRUD Operations

Troubleshooting Common PHP CRUD Errors

Frequently Asked Questions (FAQ)

Q1: What are PHP CRUD operations and why are they fundamental?

PHP CRUD operations represent the four core database functions—Create, Read, Update, and Delete—needed to manage dynamic data lifecycles in web applications. Mastering CRUD operations allows developers to build functional, database-backed websites.

Q2: Why must PDO prepared statements be used for all CRUD actions?

PDO prepared statements separate SQL templates from user-supplied data inputs. This forces the database server to treat user inputs strictly as literal values, completely neutralizing SQL Injection vulnerabilities across all Create, Read, Update, and Delete actions.

Q3: What is the purpose of the lastInsertId() method in PDO?

The $pdo->lastInsertId() method returns the auto-incremented primary key ID integer generated by the database for the most recently inserted row. It is useful for retrieving generated IDs right after executing a CREATE action.

Q4: How do you prevent XSS vulnerabilities when displaying database records?

Prevent Cross-Site Scripting (XSS) by wrapping all string values fetched from the database inside htmlspecialchars($value, ENT_QUOTES, 'UTF-8') before rendering them in HTML table cells or form fields.

Next Steps & Official References

Consult official technical standards on the PHP Official Prepared Statements Manual (php.net).

Ready for the final lesson in sequence? Proceed directly to the final lesson in Module 6: Next Lesson: Object-Oriented Programming (OOP) Fundamentals in PHP →

# Summary

Here is what you've learned in this lesson:

• Easy PHP CRUD Operations Guide: 4 Secure Database Actions
• Overview: Understanding PHP CRUD Operations & Data Management
• Prerequisites Before Executing Database Operations
• Database Schema Setup for Our CRUD Tutorial
• 1. CREATE Action: Inserting Records Safely
• 2. READ Action: Fetching and Rendering Records
• 3. UPDATE Action: Modifying Existing Records
• 4. DELETE Action: Removing Records Securely
• Summary Comparison of 4 CRUD Operations
• Troubleshooting Common PHP CRUD Errors
• Frequently Asked Questions (FAQ)
• Next Steps & Official References