Updating and Deleting Data: Essential PHP CRUD Operations

Previously in this course, we covered Creating Data with CRUD: Mastering MySQL INSERT in PHP. Now that you can insert new records, the next logical step is managing the lifecycle of that data. In this lesson, we will implement the UPDATE and DELETE functionalities to manage records in your MVC application.

The Power of the WHERE Clause

In SQL, both UPDATE and DELETE operations are dangerous if you don't use a WHERE clause. Without one, you will modify or destroy every single row in your database table.

The WHERE clause acts as a filter. It tells the database engine exactly which record(s) the operation should affect. When building features to edit or remove a specific item (like a task in a todo list), we almost always filter by the unique id of that row.

Writing UPDATE Queries

An UPDATE query modifies existing column values. The syntax follows this pattern: UPDATE table_name SET column1 = value1, column2 = value2 WHERE id = :id

Just like when we performed inserts, we must use Executing Prepared Statements to prevent SQL injection. Never concatenate user input directly into your query string.

PHP
#6A9955">// Assuming $pdo is your PDO instance
$id = 1;
$newName = "Updated Task Name";

$sql = "UPDATE tasks SET title = :title WHERE id = :id";
$stmt = $pdo->prepare($sql);

#6A9955">// Bind the values
$stmt->execute([
    'title' => $newName,
    'id' => $id
]);

Writing DELETE Queries

The DELETE query removes rows entirely. The syntax is simpler, but the risk is higher: DELETE FROM table_name WHERE id = :id

If you forget the WHERE clause, you will wipe your entire table. Always double-check your logic before executing a delete.

PHP
$id = 1;

$sql = "DELETE FROM tasks WHERE id = :id";
$stmt = $pdo->prepare($sql);

#6A9955">// Always execute with parameters
$stmt->execute(['id' => $id]);

Processing Row-Specific Requests

In our MVC project, an "Edit" or "Delete" request usually comes from a URL like /edit?id=5 or a form submission. You must retrieve this ID using the techniques we discussed in Processing GET Requests.

Here is how you might handle a delete request in your controller:

1. Capture the ID from $_GET or $_POST.
2. Validate that the ID is numeric (using is_numeric or filter_var).
3. Prepare and execute the DELETE statement.
4. Redirect the user back to the list view to confirm the action.

Hands-on Exercise: Implement "Delete"

1. Create a delete.php file in your project.
2. Ensure it accepts an id parameter from the URL.
3. Write a script that connects to your database via PDO, prepares a DELETE statement, and removes the row matching that ID.
4. After the execution, redirect the user back to your main page using header('Location: /index.php').

Common Pitfalls

• Forgetting the WHERE clause: This is the most common and destructive mistake. Always write your WHERE clause first when drafting a query.
• Trusting the ID: Even if an ID comes from a button on your page, a malicious user can manually change the URL parameters. Always validate that the ID is an integer before passing it to your database.
• Ignoring feedback: If a delete fails (e.g., the record doesn't exist), the user won't know. Consider checking $stmt->rowCount() to see if any rows were actually affected.

Frequently Asked Questions

Q: Can I update multiple rows at once? A: Yes, if your WHERE clause matches multiple rows, UPDATE will change all of them. Use caution!

Q: How do I know if an update actually happened? A: You can call $stmt->rowCount() on your prepared statement object after calling execute(). It returns the number of rows affected by the last SQL statement.

Q: Is it better to use DELETE or a "soft delete"? A: A "soft delete" involves adding a deleted_at timestamp column to your table. Instead of running DELETE, you run an UPDATE to set that timestamp. This allows you to restore data later, which is a common practice in professional applications.

Recap

We have now closed the loop on CRUD. You can create, read, update, and delete data, which covers the vast majority of backend development tasks. You've learned that prepared statements are mandatory for security and that the WHERE clause is your most important tool for ensuring operations target the correct data.

Up next: We will begin organizing our code into professional structures by Connecting Models to the Database.