Building CRUD Applications in PHP

# CHAPTER 8

Building CRUD Applications in PHP #

1. Introduction #

2. Learning Objectives #

• Define the CRUD paradigm.

• Write SQL queries for INSERT, SELECT, UPDATE, and DELETE.

• Use PDO Prepared Statements to absolutely prevent SQL Injection.

• Build a functional backend admin dashboard.

3. Beginner-Friendly Explanation #

• Create: Adding a new friend's phone number to the book.

• Read: Looking up a friend's phone number to call them.

• Update: Changing a friend's number when they get a new phone.

• Delete: Erasing a friend's number from the book.

4. The Hacker Threat: SQL Injection #

5. The Solution: Prepared Statements #

6. CREATE (Insert Data) #

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

// Assume form was submitted via POST
$name = $_POST[&#039;name'];
$email = $_POST[&#039;email'];

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

// 2. Execute and pass the user data securely into the placeholders
$stmt->execute([$name, $email]);

echo "User created successfully!";
?>

7. READ (Fetch Data) #

<?php
$user_id = 5; // e.g., $_GET['id']

$sql = "SELECT name, email FROM users WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$user_id]);

// Fetch a single row as an associative array
$user = $stmt->fetch(PDO::FETCH_ASSOC);

echo "Name: " . $user[&#039;name'];
?>

8. UPDATE (Modify Data) #

<?php
$new_email = "new_email@test.com";
$user_id = 5;

$sql = "UPDATE users SET email = ? WHERE id = ?";
$stmt = $pdo->prepare($sql);

// Order of variables in the array MUST match the order of the ? placeholders
$stmt->execute([$new_email, $user_id]);

echo "Profile updated!";
?>

9. DELETE (Remove Data) #

<?php
$user_id_to_delete = 12;

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

echo "User deleted permanently.";
?>

10. Best Practices #

• Soft Deletes: In enterprise applications, we rarely use the DELETE command. Instead, we add a column to the database called isdeleted (set to 0). When a user deletes an item, we use an UPDATE command to set isdeleted = 1. The item disappears from the website, but the data is safely preserved in the database for legal/recovery reasons.

11. Common Mistakes #

• Trusting the UI: A developer might build an "Admin Dashboard" with a "Delete User" button, assuming only Admins can see the button. However, if a hacker figures out the URL (delete.php?id=5), they can bypass the dashboard and trigger the script directly. You must always verify authorization *inside* the PHP script before executing CRUD actions.

12. Exercises #

1. 1. Explain how a Prepared Statement completely prevents an SQL Injection attack.

13. MCQs with Answers #

14. Interview Questions #

• Q: Walk me through the four operations of CRUD and provide the corresponding SQL keyword for each.

• Q: Explain the mechanics of a SQL Injection attack. How do Prepared Statements separate the SQL logic from the user data to neutralize this threat?

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #