Building CRUD Applications with PostgreSQL

# CHAPTER 27

Building CRUD Applications with PostgreSQL #

1. Introduction #

2. Learning Objectives #

• Map HTTP requests to database operations.

• Implement the "Create" operation using INSERT.

• Implement the "Read" operation using SELECT.

• Implement the "Update" operation using UPDATE.

• Implement the "Delete" operation using DELETE.

• Architect a clean, secure data flow.

3. The Architecture Map #

• Create: User submits HTML Form via POST -> PHP $pdo->prepare(INSERT).

• Read: User visits a URL via GET -> PHP $pdo->prepare(SELECT).

• Update: User submits Edit Form via POST -> PHP $pdo->prepare(UPDATE).

• Delete: User clicks Delete via POST -> PHP $pdo->prepare(DELETE).

4. Step 1: The Database Schema #

CREATE TABLE posts (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    author_id INT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

5. Step 2: C - CREATE (Adding a New Post) #

<?php
// Assume PDO connection ($pdo) is already established
$title = $_POST[&#039;title'];
$content = $_POST[&#039;content'];
$authorId = 1; // Assuming the logged-in user's ID is 1

$sql = "INSERT INTO posts (title, content, author_id) VALUES (?, ?, ?)";
$stmt = $pdo->prepare($sql);

try {
    $stmt->execute([$title, $content, $authorId]);
    echo "Blog post created successfully!";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

6. Step 3: R - READ (Displaying the Posts) #

<?php
$sql = "SELECT id, title, left(content, 100) AS excerpt, created_at 
        FROM posts ORDER BY created_at DESC";
$stmt = $pdo->query($sql);
$posts = $stmt->fetchAll(PDO::FETCH_ASSOC);

// In the HTML view:
foreach ($posts as $post) {
    echo "<h2>" . htmlspecialchars($post[&#039;title']) . "</h2>";
    echo "<p>" . htmlspecialchars($post[&#039;excerpt']) . "...</p>";
    echo "<a href=&#039;edit.php?id=" . $post['id'] . "'>Edit Post</a>";
}
?>

7. Step 4: U - UPDATE (Editing a Post) #

<?php
$newTitle = $_POST[&#039;title'];
$newContent = $_POST[&#039;content'];
$postId = $_POST[&#039;post_id'];

// CRITICAL: Always use WHERE to prevent updating the entire table!
$sql = "UPDATE posts SET title = ?, content = ? WHERE id = ?";
$stmt = $pdo->prepare($sql);

try {
    $stmt->execute([$newTitle, $newContent, $postId]);
    echo "Post updated successfully!";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

8. Step 5: D - DELETE (Removing a Post) #

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

// CRITICAL: Always use WHERE on a DELETE statement!
$sql = "DELETE FROM posts WHERE id = ?";
$stmt = $pdo->prepare($sql);

try {
    $stmt->execute([$postIdToDelete]);
    echo "Post permanently deleted.";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

9. Common Mistakes #

• Trusting User Input: The database is protected by Prepared Statements against SQL Injection. But what if a user inputs <script>alert('Hacked')</script> into the Blog Title? The database stores it safely, but when you READ it and print it onto the homepage, the browser executes the script (XSS Attack). Always wrap output in htmlspecialchars() when printing database data to HTML!

• Using GET for Deletions: Never create a link like <a href="delete.php?id=5">Delete</a>. Search engines (like Google) click every link they find. Googlebot will crawl your site and accidentally delete your entire database! DELETE operations must always be submitted via a POST form.

10. Best Practices #

• Soft Deletes: In enterprise applications, we rarely use the physical DELETE command. Instead, we add a boolean column: isdeleted BOOLEAN DEFAULT FALSE. The "Delete" script performs an UPDATE posts SET isdeleted = TRUE. The "Read" script is modified to SELECT * FROM posts WHERE is_deleted = FALSE. The data appears deleted to the user, but is preserved in the database for auditing!

11. Exercises #

1. 1. Which HTTP method (GET or POST) should be used when transmitting data to a PHP script intended to execute an UPDATE or DELETE query?

1. 2. Explain the architectural flow of data during a "Create" operation.

12. SQL Challenges #

$userId = 42;
$stmt = $pdo->prepare("UPDATE users SET is_active = FALSE WHERE id = ?");
$stmt->execute([$userId]);

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Explain the concept of a "Soft Delete" versus a "Hard Delete" in database architecture. Why do enterprise systems heavily favor soft deletes?

• Q: Describe a scenario where an application is perfectly immune to SQL Injection (because it uses Prepared Statements), but is still massively vulnerable to Cross-Site Scripting (XSS). How do you secure the "Read" portion of the CRUD cycle?

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #