Lesson 12: Interacting with a Database (CRUD)

Making Data Permanent

While sessions are great for remembering a user temporarily, most applications need to store data permanently. This is where a database comes in. The four fundamental operations you can perform on data in a database are known by the acronym CRUD:

• Create: Add new data.
• Read: Retrieve existing data.
• Update: Modify existing data.
• Delete: Remove existing data.

In modern PHP, the standard way to interact with a database is by using PDO (PHP Data Objects), which provides a consistent and secure interface for multiple database types.

The Golden Rule: Use Prepared Statements

Before we write any queries, you must learn the most important rule of database security: Always use prepared statements. Directly putting user input into an SQL query is extremely dangerous and can lead to a devastating attack called SQL Injection.

A prepared statement is a template for an SQL query. You send the template to the database first, and then you send the user's data separately. The database engine then combines them safely, preventing any malicious code from being executed.

In PDO, you use placeholders (usually a question mark ?) in your SQL template.

1. CREATE - Inserting Data

To add a new record to a table, you use the INSERT INTO SQL statement. This is what you would use for a user registration form or adding a new blog post.

<?php
    // Assume $pdo is your database connection object from config.php
    
    $username = "jane.doe";
    $email = "jane@example.com";
    $password = "a_hashed_password"; // Never store plain text passwords!

    try {
        $sql = "INSERT INTO users (username, email, password) VALUES (?, ?, ?)";
        $stmt = $pdo->prepare($sql);
        $stmt->execute([$username, $email, $password]);
        
        echo "New user created successfully!";
        
    } catch (\PDOException $e) {
        die("Error creating user: " . $e->getMessage());
    }
?>

2. READ - Fetching Data

To retrieve data, you use the SELECT statement. You can fetch a single record or multiple records.

Fetching a Single Record

<?php
    $user_id = 1;
    
    $sql = "SELECT username, email FROM users WHERE id = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$user_id]);
    
    // fetch() retrieves the next row from the result set
    $user = $stmt->fetch();
    
    if ($user) {
        echo "Username: " . $user['username'];
    }
?>

Fetching Multiple Records

<?php
    $sql = "SELECT username, email FROM users ORDER BY username ASC";
    $stmt = $pdo->query($sql); // No need to prepare if there are no placeholders
    
    // fetchAll() retrieves all rows into an array
    $users = $stmt->fetchAll();
    
    foreach ($users as $user) {
        echo $user['username'] . "<br>";
    }
?>

3. UPDATE - Modifying Data

To change an existing record, you use the UPDATE statement, usually with a WHERE clause to specify which record to modify.

<?php
    $new_email = "jane.d@new-example.com";
    $user_id = 2; // The ID of the user we want to update

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

    echo "User record updated.";
?>

4. DELETE - Removing Data

To remove a record, you use the DELETE FROM statement, which also requires a WHERE clause.

<?php
    $user_id_to_delete = 3;
    
    $sql = "DELETE FROM users WHERE id = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$user_id_to_delete]);

    echo "User deleted.";
?>

Mastering these four CRUD operations with PDO and prepared statements is the most important technical skill for a backend PHP developer. With this, you can build almost any data-driven application.