CHAPTER 22 Beginner

PHP CRUD Operations

Updated: May 12, 2026

35 min read

# Chapter 22: PHP CRUD Operations

1. Introduction

Welcome to Chapter 22! Every dynamic application on the internet—from a simple to-do list to massive platforms like Facebook—is built on four fundamental operations: Create, Read, Update, and Delete. This is known universally as CRUD. In this chapter, we will use our PDO database connection to execute SQL queries directly from PHP, allowing us to manage database records dynamically. We will also learn the critical concept of Prepared Statements to keep our data secure.

2. Learning Objectives

By the end of this chapter, you will be able to:

Write raw SQL queries inside PHP.

Execute basic Read operations (SELECT).

Create records securely using Prepared Statements (INSERT).

Update existing records (UPDATE).

Delete records from the database (DELETE).

3. Database Preparation

Before writing PHP, we need a table. Open phpMyAdmin, select your php_course database, go to the "SQL" tab, and run this to create a users table:

sql

12345

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

4. READ: Fetching Data

To read data from the database, we use the SELECT query. We call $pdo->query(), and then use fetchAll() or fetch() to retrieve the results as an associative array.

php

1234567891011121314

<?php
require &#039;db.php'; // Our connection from last chapter

// 1. Prepare and execute query
$stmt = $pdo->query("SELECT * FROM users");

// 2. Fetch all results into an array
$users = $stmt->fetchAll();

// 3. Loop through results
foreach ($users as $user) {
    echo "Name: " . $user[&#039;name'] . " - Email: " . $user['email'] . "<br>";
}
?>

5. CREATE: The Danger of SQL Injection

If we want to insert user data from an HTML form, we might think to write this: $pdo->query("INSERT INTO users (name) VALUES ('" . $_POST['name'] . "')"); NEVER DO THIS. If a hacker enters ' OR 1=1; DROP TABLE users; -- into the form, they will delete your entire database. This is SQL Injection.

6. CREATE: Prepared Statements (The Secure Way)

To prevent SQL injection, ALWAYS use Prepared Statements. We use placeholders (:name) instead of actual variables, and let PDO securely bind the data later.

php

123456789101112131415161718

<?php
require &#039;db.php';

$name = "Alice Jenkins";
$email = "alice@example.com";

// 1. Prepare the statement with placeholders
$sql = "INSERT INTO users (name, email) VALUES (:name, :email)";
$stmt = $pdo->prepare($sql);

// 2. Execute by passing an array of the actual data
$stmt->execute([
    &#039;:name' => $name,
    &#039;:email' => $email
]);

echo "New user created successfully!";
?>

7. UPDATE: Modifying Records

Updating requires the UPDATE SQL statement. Again, we use prepared statements to securely pass the ID of the user we want to change.

php

123456789101112131415

<?php
require &#039;db.php';

$new_email = "alice_new@example.com";
$user_id = 1; // Assuming Alice's ID is 1

$sql = "UPDATE users SET email = :email WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute([
    &#039;:email' => $new_email,
    &#039;:id' => $user_id
]);

echo "Record updated.";
?>

8. DELETE: Removing Records

Deleting uses the DELETE FROM SQL statement. Always include a WHERE clause, otherwise you will delete every record in the table!

php

1234567891011

<?php
require &#039;db.php';

$user_id = 1; // ID to delete

$sql = "DELETE FROM users WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute([&#039;:id' => $user_id]);

echo "User deleted.";
?>

9. Real-World Examples

In a real application, CRUD operations are tied to HTML forms and buttons. A "Delete" link will pass an ID via the URL (delete.php?id=5). The PHP script catches $_GET['id'], throws it into a Prepared Statement DELETE query, and removes the record.

10. Output Explanations

When $stmt->fetchAll() runs in the Read example, PDO takes the raw database rows and converts them into a massive PHP Multidimensional Array. Each row becomes an associative array where the database column names (id, name, email) become the Array Keys. This makes it incredibly easy to loop through and output data via HTML.

11. Common Mistakes

Forgetting the WHERE clause: Running UPDATE users SET role = 'admin'. This makes *everyone* an admin because you didn't specify WHERE id = 5.

String Interpolation in Queries: Writing query("SELECT * FROM users WHERE name = '$username'"). Always use prepare() and execute() for ANY query that involves a variable.

Not handling duplicates: In our table, email is UNIQUE. If you try to insert a duplicate email, PDO will throw a fatal Exception.

12. Best Practices

Never trust user input. Prepared statements automatically sanitize data against SQL injections, making them the absolute gold standard for database operations.

Check if execute() returns true/false to verify the operation succeeded before showing a success message to the user.

13. Exercises

1. Using the users table, insert a new record with your name and email.

2. Write a SELECT statement that fetches only the user where name = 'Your Name' and echo their email.

14. Mini Project: Student Management System

Task: Build a page that reads all students from the database and displays them in an HTML table. (Assuming you have created the table and run some INSERTS).

php

1234567891011121314151617181920212223242526272829303132333435363738394041424344

<?php
require &#039;db.php'; // Your PDO connection

// Fetch all users
$stmt = $pdo->query("SELECT id, name, email FROM users ORDER BY id DESC");
$users = $stmt->fetchAll();
?>

<!DOCTYPE html>
<html>
<head>
    <title>Student Manager</title>
    <style>
        table { border-collapse: collapse; width: 50%; }
        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
        th { background-color: #f2f2f2; }
    </style>
</head>
<body>
    <h2>Student Records</h2>
    <table>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Email</th>
            <th>Action</th>
        </tr>
        
        <?php foreach ($users as $user): ?>
        <tr>
            <td><?php echo $user[&#039;id']; ?></td>
            <td><?php echo htmlspecialchars($user[&#039;name']); ?></td>
            <td><?php echo htmlspecialchars($user[&#039;email']); ?></td>
            <td>
                <!-- Action links would go to separate PHP files -->
                <a href="edit.php?id=<?php echo $user[&#039;id']; ?>">Edit</a> |
                <a href="delete.php?id=<?php echo $user[&#039;id']; ?>" style="color:red;">Delete</a>
            </td>
        </tr>
        <?php endforeach; ?>
        
    </table>
</body>
</html>

15. Coding Challenges

Challenge 1: Write the code for delete.php. It should check if $_GET['id'] is set, prepare a DELETE statement, execute it using the ID from the URL, and then redirect the user back to the main page using header("Location: index.php");.

16. MCQs with Answers

1. What does CRUD stand for? A) Code, Run, Update, Debug B) Create, Read, Update, Delete C) Connect, Request, Upload, Download D) Create, Refresh, Update, Destroy *Answer: B*

2. Which method prevents SQL Injection attacks? A) htmlspecialchars() B) Using GET instead of POST C) Prepared Statements (prepare and execute) D) Encrypting the database *Answer: C*

3. What happens if you run an UPDATE query without a WHERE clause? A) The query fails. B) The database crashes. C) Only the first record is updated. D) EVERY record in the table is updated. *Answer: D*

17. Interview Questions

Q: Explain what SQL Injection is and how Prepared Statements prevent it. *A:* SQL Injection is an attack where malicious SQL code is inserted into input fields to manipulate the backend database. Prepared statements prevent this by completely separating the SQL logic from the data. The SQL query structure is sent to the database server first. The data is sent later in a separate packet. Because they are separated, the database treats the user input strictly as a string literal, not as executable code.

Q: Explain the difference between fetch() and fetchAll(). *A:* fetchAll() retrieves all rows from the database result and places them into a multidimensional array, which is great for looping. fetch() retrieves only a single row (the next available row) and moves the internal pointer forward, which is highly efficient if you only expect one result (like fetching a user by ID).

18. FAQs

Q: What if I need to fetch 1 million rows? Will fetchAll() work? *A:* Calling fetchAll() on 1 million rows will load massive amounts of data into PHP's RAM, likely crashing your server. For massive datasets, you should use a while($row = $stmt->fetch()) loop, which loads and processes only one row at a time.

19. Summary

You are now a true Backend Developer! You've learned how to interact with the database directly from PHP. You learned the four pillars of data management (CRUD), how to fetch data for display, how to use Prepared Statements to insert, update, and delete data securely, and how to prevent devastating SQL Injection attacks.

20. Next Chapter Recommendation

With CRUD mastered, we can finally build the Holy Grail of backend development. In Chapter 23: PHP Authentication System, we will combine Forms, Sessions, and Databases to build a complete Login and Registration system!

Browse All 21+ Subject Areas

Quick Links

Visual Algorithm Labs

Frontend Dev

The Future of Web Architecture in 2026

Practice Quizzes

1. Introduction #

2. Learning Objectives #

3. Database Preparation #

4. READ: Fetching Data #

5. CREATE: The Danger of SQL Injection #

6. CREATE: Prepared Statements (The Secure Way) #

7. UPDATE: Modifying Records #

8. DELETE: Removing Records #

9. Real-World Examples #

10. Output Explanations #

11. Common Mistakes #

12. Best Practices #

13. Exercises #

14. Mini Project: Student Management System #

15. Coding Challenges #

16. MCQs with Answers #

17. Interview Questions #

18. FAQs #

19. Summary #

20. Next Chapter Recommendation #

Finish this Chapter

Discussion

Explore More

📖 Related Tutorials 6

❓ Related Quizzes 6

🎥 Related Videos 1

Send Feedback / Bug

Feedback Submitted!