Build a PHP SQL CRUD Application | Secure Database Workflow

# CHAPTER 28

Building CRUD Applications with SQL #

1. Introduction #

2. Learning Objectives #

• Map HTTP Form submissions to SQL operations.

• Execute secure INSERT operations (Create).

• Execute parameterized SELECT operations (Read).

• Execute secure UPDATE operations (Update).

• Execute secure DELETE operations (Delete).

• Completely immunize your application against SQL Injection.

3. Architecture Setup #

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

*(Assume the $pdo connection variable from Chapter 27 is already established).*

4. CREATE (Inserting Data Safely) #

<?php
// 1. Data arrives from the frontend form
$name = $_POST[&#039;student_name'];
$email = $_POST[&#039;student_email'];

// 2. Prepare the SQL structure (Using '?' as placeholders)
$sql = "INSERT INTO students (name, email) VALUES (?, ?)";
$stmt = $pdo->prepare($sql);

// 3. Execute the statement, passing the user data securely inside an array!
try {
    $stmt->execute([$name, $email]);
    echo "Student created successfully!";
} catch (\PDOException $e) {
    echo "Error: Email might already exist!";
}
?>

5. READ (Fetching Specific Data) #

<?php
$search_id = $_GET[&#039;id']; // ID from the URL

$sql = "SELECT * FROM students WHERE id = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$search_id]);

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

if ($student) {
    echo "Found: " . $student[&#039;name'];
} else {
    echo "Student not found.";
}
?>

6. UPDATE (Modifying Data Safely) #

<?php
$new_email = $_POST[&#039;new_email'];
$student_id = $_POST[&#039;student_id'];

// The first '?' is the email. The second '?' is the ID.
$sql = "UPDATE students SET email = ? WHERE id = ?";
$stmt = $pdo->prepare($sql);

// The array order must match the '?' order!
$stmt->execute([$new_email, $student_id]);

echo "Student updated successfully!";
?>

7. DELETE (Removing Data Safely) #

<?php
$delete_id = $_POST[&#039;delete_id'];

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

echo "Student permanently removed.";
?>

8. Alternative: Named Parameters #

<?php
// Using :name and :email instead of ?
$sql = "INSERT INTO students (name, email) VALUES (:name, :email)";
$stmt = $pdo->prepare($sql);

// Pass an associative array. The order no longer matters!
$stmt->execute([
    &#039;:email' => $email,
    &#039;:name'  => $name
]);
?>

9. Common Mistakes #

• Mixing PDO methods: A common beginner mistake is calling $pdo->query() when dealing with user input. The query() method executes raw SQL instantly and bypasses all security. If there is *any* user variable in your SQL, you MUST use $pdo->prepare() followed by execute().

10. Best Practices #

• Redirect After Post (PRG Pattern): When a user successfully creates a student (INSERT), your PHP code should immediately redirect them back to the main page using header("Location: index.php");. If you don't do this, and the user hits the "Refresh" button on their browser, the browser will resubmit the form and insert a duplicate student!

11. Exercises #

1. 1. What two PDO methods must be called sequentially to securely execute a query containing user input?

1. 2. If you use standard ? placeholders, how does PDO know which variable belongs to which placeholder?

12. SQL Challenges #

$sql = "UPDATE products SET price = :price WHERE product_id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute([':price' => 50.00, ':id' => 10]);

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Explain the complete CRUD data flow, detailing how a web application maps the four HTTP methods (POST, GET, PUT, DELETE) to their corresponding SQL commands.

• Q: A junior developer struggles with a 15-column INSERT statement because they keep messing up the array order for the ? placeholders. What specific feature of PDO would you instruct them to use to solve this problem permanently?

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #