BOOKS CRUD

The books table contains:

code text

book_id
book_title
book_author
book_category
book_created_at

The CRUD operations are:

code text

CREATE → INSERT
READ   → SELECT
UPDATE → UPDATE
DELETE → DELETE

---

3.1 CREATE Book

Use Case

The user enters:

code text

Book Title
Author
Category

---

Get Form Data

code php

$title    = trim($_POST['book_title'] ?? '');
$author   = trim($_POST['book_author'] ?? '');
$category = trim($_POST['book_category'] ?? '');

---

SQL Query

code sql

INSERT INTO books (
    book_title,
    book_author,
    book_category
)
VALUES (?, ?, ?);

---

PHP PDO

code php

$stmt = $pdo->prepare("
    INSERT INTO books (
        book_title,
        book_author,
        book_category
    )
    VALUES (?, ?, ?)
");

$stmt->execute([
    $title,
    $author,
    $category
]);

The database automatically generates:

code text

book_id
book_created_at

---

3.2 READ Books

Use Case

Display all books. ---

SQL Query

code sql

SELECT *
FROM books
ORDER BY book_id DESC;

---

PHP PDO

code php

$stmt = $pdo->query("
    SELECT *
    FROM books
    ORDER BY book_id DESC
");

$books = $stmt->fetchAll();

---

Display Books

code php

<?php foreach ($books as $book): ?>

    <?= htmlspecialchars($book['book_id']) ?>

    <?= htmlspecialchars($book['book_title']) ?>

    <?= htmlspecialchars($book['book_author']) ?>

    <?= htmlspecialchars($book['book_category']) ?>

<?php endforeach; ?>

---

3.3 READ One Book

When editing a book:

code text

index.php?section=books&action=update&id=1

PHP gets the ID:

code php

$bookId = (int) ($_GET['id'] ?? 0);

SQL:

code sql

SELECT *
FROM books
WHERE book_id = ?;

PHP:

code php

$stmt = $pdo->prepare("
    SELECT *
    FROM books
    WHERE book_id = ?
");

$stmt->execute([
    $bookId
]);

$book = $stmt->fetch();

---

3.4 UPDATE Book

Use Case

The user changes the book information. ---

SQL Query

code sql

UPDATE books
SET
    book_title = ?,
    book_author = ?,
    book_category = ?
WHERE book_id = ?;

---

PHP PDO

code php

$stmt = $pdo->prepare("
    UPDATE books
    SET
        book_title = ?,
        book_author = ?,
        book_category = ?
    WHERE book_id = ?
");

$stmt->execute([
    $title,
    $author,
    $category,
    $bookId
]);

The WHERE book_id = ? ensures that only the selected book is changed. ---

3.5 DELETE Book

Use Case

The user clicks Delete. ---

SQL Query

code sql

DELETE FROM books
WHERE book_id = ?;

---

PHP PDO

code php

$bookId = (int) ($_GET['id'] ?? 0);

$stmt = $pdo->prepare("
    DELETE FROM books
    WHERE book_id = ?
");

$stmt->execute([
    $bookId
]);

---

Important: Foreign Key Restriction

The borrow table contains:

code sql

FOREIGN KEY (book_id)
REFERENCES books(book_id)
ON DELETE RESTRICT

Therefore:

code text

Book
 |
 v
Borrow Record

If the book is being referenced by a borrow record, MySQL prevents its deletion. ---

3.6 Books CRUD Summary

code text

| CRUD | SQL | PHP Method |
|---|---|---|
| Create | `INSERT` | `prepare()` + `execute()` |
| Read | `SELECT` | `query()` + `fetchAll()` |
| Read One | `SELECT WHERE` | `prepare()` + `fetch()` |
| Update | `UPDATE` | `prepare()` + `execute()` |
| Delete | `DELETE` | `prepare()` + `execute()` |

format_list_numbered Step 4 of 9

chevron_left Previous Next chevron_right