BORROW CRUD

The borrow table contains:

code text

borrow_id
student_id
book_id
borrow_date
borrow_return_date

Unlike students and books, the borrow table uses foreign keys.

code text

borrow.student_id
        |
        v
students.student_id

and:

code text

borrow.book_id
        |
        v
books.book_id

For this application, the CRUD operations are:

code text

CREATE → Borrow a book
READ   → View borrow records
UPDATE → Return a book
DELETE → Delete borrow record

---

4.1 CREATE Borrow

Use Case

The user selects:

code text

Student
Book

and clicks:

code text

Borrow

---

Step 1: Get Students

The form needs a list of students.

SQL

code sql

SELECT
    student_id,
    student_first_name,
    student_last_name
FROM students
ORDER BY student_last_name, student_first_name;

PHP

code php

$stmt = $pdo->query("
    SELECT
        student_id,
        student_first_name,
        student_last_name
    FROM students
    ORDER BY student_last_name, student_first_name
");

$students = $stmt->fetchAll();

The result is used to create the student <select>. ---

Step 2: Get Books

The form also needs a list of books.

SQL

code sql

SELECT
    book_id,
    book_title,
    book_author
FROM books
ORDER BY book_title;

PHP

code php

$stmt = $pdo->query("
    SELECT
        book_id,
        book_title,
        book_author
    FROM books
    ORDER BY book_title
");

$books = $stmt->fetchAll();

---

Step 3: Get Selected IDs

The form submits:

code php

$studentId = (int) ($_POST['student_id'] ?? 0);
$bookId    = (int) ($_POST['book_id'] ?? 0);

For example:

code text

student_id = 1
book_id = 2

means:

code text

Student #1
    |
    +---- borrowed ----> Book #2

---

Step 4: INSERT Borrow Record

SQL

code sql

INSERT INTO borrow (
    student_id,
    book_id
)
VALUES (?, ?);

PHP

code php

$stmt = $pdo->prepare("
    INSERT INTO borrow (
        student_id,
        book_id
    )
    VALUES (?, ?)
");

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

The database automatically generates:

code text

borrow_id
borrow_date

because:

code sql

borrow_id INT AUTO_INCREMENT

and:

code sql

borrow_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP

---

4.2 READ Borrow Records

Use Case

Display the borrow history. A basic:

code sql

SELECT *
FROM borrow;

would only return IDs:

code text

borrow_id | student_id | book_id

That is not useful to the user. Instead, use JOIN. ---

SQL Query

code sql

SELECT
    borrow.borrow_id,
    borrow.borrow_date,
    borrow.borrow_return_date,

    students.student_first_name,
    students.student_last_name,

    books.book_title,
    books.book_author

FROM borrow

INNER JOIN students
    ON borrow.student_id = students.student_id

INNER JOIN books
    ON borrow.book_id = books.book_id

ORDER BY borrow.borrow_id DESC;

---

PHP PDO

code php

$stmt = $pdo->query("
    SELECT
        borrow.borrow_id,
        borrow.borrow_date,
        borrow.borrow_return_date,

        students.student_first_name,
        students.student_last_name,

        books.book_title,
        books.book_author

    FROM borrow

    INNER JOIN students
        ON borrow.student_id = students.student_id

    INNER JOIN books
        ON borrow.book_id = books.book_id

    ORDER BY borrow.borrow_id DESC
");

$borrowRecords = $stmt->fetchAll();

---

Understanding the JOIN

The first relationship is:

code sql

INNER JOIN students
    ON borrow.student_id = students.student_id

This connects:

code text

borrow.student_id
        ↓
students.student_id

The second relationship:

code sql

INNER JOIN books
    ON borrow.book_id = books.book_id

connects:

code text

borrow.book_id
        ↓
books.book_id

The result can then display:

code text

Borrow ID | Student             | Book
----------|---------------------|------------------
1         | CLIFF EVANGELIO     | Jurassic Park
2         | JAN EVANGELIO       | Project Hail Mary
3         | RON EVANGELIO       | 1984

---

4.3 UPDATE Borrow — Return Book

For the borrow table, the Update operation is used to mark a book as returned. Initially:

code text

borrow_return_date = NULL

After returning:

code text

borrow_return_date = current timestamp

---

Get Borrow ID

code php

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

Example:

code text

index.php?section=borrow&action=return&id=1

---

SQL Query

code sql

UPDATE borrow
SET borrow_return_date = CURRENT_TIMESTAMP
WHERE borrow_id = ?;

---

PHP PDO

code php

$stmt = $pdo->prepare("
    UPDATE borrow
    SET borrow_return_date = CURRENT_TIMESTAMP
    WHERE borrow_id = ?
");

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

---

Before Return

code text

borrow_id: 1
borrow_date: 2026-09-15 10:00:00
borrow_return_date: NULL

Status:

code text

Borrowed

---

After Return

code text

borrow_id: 1
borrow_date: 2026-09-15 10:00:00
borrow_return_date: 2026-09-15 15:30:00

Status:

code text

Returned

---

4.4 DELETE Borrow Record

Use Case

The user deletes a borrow record. ---

SQL Query

code sql

DELETE FROM borrow
WHERE borrow_id = ?;

---

PHP PDO

code php

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

$stmt = $pdo->prepare("
    DELETE FROM borrow
    WHERE borrow_id = ?
");

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

This permanently removes the borrow record. ---

4.5 Determine Borrow Status

The application does not need a separate status column. Instead, it checks:

code php

if ($borrow['borrow_return_date']) {

    echo "Returned";

} else {

    echo "Borrowed";

}

The database logic is:

code text

borrow_return_date
        |
        +---- NULL
        |       |
        |       v
        |    Borrowed
        |
        +---- Has timestamp
                |
                v
             Returned

---

4.6 Borrow CRUD Summary

code text

| CRUD | Operation | SQL |
|---|---|---|
| Create | Borrow book | `INSERT` |
| Read | View borrow history | `SELECT + JOIN` |
| Update | Return book | `UPDATE` |
| Delete | Delete borrow record | `DELETE` |

format_list_numbered Step 5 of 9

chevron_left Previous Next chevron_right