SQL Prepared Statements for CRUD Operations

The Core Process: A Quick Recap

Remember the two-stage process for prepared statements:

1. Prepare: You send an SQL query template with placeholders (e.g., :name, :id) to the database. The database server compiles and optimizes this template.
   $stmt = $pdo->prepare("SQL QUERY HERE");
2. Execute: You send the actual data that will substitute the placeholders. The database safely combines the data with the compiled template.
   $stmt->execute(['name' => $name, 'id' => $id]);

We will now apply this process to INSERT, UPDATE, and DELETE queries.

1. Create: The INSERT Statement

The INSERT statement adds new rows to a table. When using it with PDO, you'll define placeholders for the values you want to insert.

A typical INSERT prepared statement looks like this:

<?php
// Assumes $pdo is your connected PDO object
$sql = "INSERT INTO users (full_name, email, status) VALUES (:full_name, :email, :status)";
$stmt= $pdo->prepare($sql);

$stmt->execute([
    'full_name' => 'John Doe',
    'email'     => 'john.doe@example.com',
    'status'    => 'active'
]);

echo "New user created successfully.";

The video below provides a complete walkthrough of creating an HTML form and the corresponding PHP script to handle an INSERT operation from start to finish.

Retrieving the Last Inserted ID

A very common requirement after creating a new record is to get the id that the database automatically assigned to it (if you're using an auto-incrementing primary key). You might need this ID to redirect the user to the new record's page or to create related records in other tables.

PDO makes this easy with the lastInsertId() method, which you call on your main PDO connection object after a successful execute().

// ... after the $stmt->execute([...]) call
$newUserId = $pdo->lastInsertId();
echo "The new user was created with ID: " . $newUserId;

// You could now use this ID
// header('Location: /user.php?id=' . $newUserId);
// exit();

2. Update: The UPDATE Statement

The UPDATE statement modifies existing rows in a table. Its structure is similar to INSERT, but it critically includes a WHERE clause to specify which row(s) to modify. Forgetting the WHERE clause is a disastrous mistake, as it would cause the UPDATE to apply to all rows in the table.

Here's a typical UPDATE prepared statement:

<?php
// Assumes $pdo is your connected PDO object
$sql = "UPDATE users SET email = :email, status = :status WHERE id = :id";
$stmt= $pdo->prepare($sql);

$stmt->execute([
    'email'  => 'john.doe.new@example.com',
    'status' => 'inactive',
    'id'     => 123 // The ID of the user to update
]);

echo "User updated successfully.";

Notice how the value for the WHERE clause (:id) is just another parameter passed to execute().

The following video demonstrates a full edit-and-update workflow. It shows how to first fetch a record into a form, allow the user to make changes, and then submit those changes to an UPDATE script.

Checking if an Update Occurred

After an UPDATE or DELETE query, you might want to know if any rows were actually affected. The execute() method will return true if the query ran without errors, but that doesn't mean it actually changed anything. For instance, an UPDATE query might run successfully but match no rows if the id in the WHERE clause doesn't exist.

To find out how many rows were changed, you can use the rowCount() method on the statement object.

// ... after the $stmt->execute([...]) call
$affectedRows = $stmt->rowCount();

if ($affectedRows > 0) {
    echo "Successfully updated " . $affectedRows . " record(s).";
} else {
    echo "Query was successful, but no records were updated. The user might not exist or the data was the same.";
}

3. Delete: The DELETE Statement

The DELETE statement removes rows from a table. Like UPDATE, it is almost always used with a WHERE clause to specify which record to delete.

This is the simplest of the three data-modification queries:

<?php
// Assumes $pdo is your connected PDO object
$sql = "DELETE FROM users WHERE id = :id";
$stmt= $pdo->prepare($sql);

$stmt->execute(['id' => 123]);

$affectedRows = $stmt->rowCount();
echo "Deleted " . $affectedRows . " record(s).";

Let's watch the final part of the CRUD video, which covers the DELETE operation.

For quick reference on the syntax for all three operations, as well as rowCount() and lastInsertId(), this article is an excellent resource.

Conclusion

You have now covered the full range of fundamental database operations in a secure and professional manner. By mastering the prepare() and execute() pattern, you can confidently and safely create, read, update, and delete data in your PHP applications.

Key Takeaways:

• The same secure pattern—prepare() with placeholders, then execute() with an array of data—applies to INSERT, UPDATE, and DELETE just as it does to SELECT.
• Always use a WHERE clause with UPDATE and DELETE statements to target specific records. The values for the WHERE clause are passed as parameters just like any other data.
• Use $pdo->lastInsertId() after an INSERT to get the primary key of the newly created row.
• Use $stmt->rowCount() after an UPDATE or DELETE to confirm how many rows were affected by the operation.

Preview of the Next Lesson:
Our code examples have focused on the "happy path" where everything works correctly. But what happens if the database connection fails, or if your SQL query has a syntax error? In the next lesson, we will focus on robust error management by learning how to handle database connection and query errors using PDO exceptions, ensuring your application can fail gracefully and securely.