Bài tập PHP cơ bản phần 6

Library Management System using PHP

Semester: 1 Duration: 90 minutes Total Marks: 20

Database Setup

1. Create and use the database CREATE DATABASE library_db; USE library_db;
2. Create the books table CREATE TABLE books ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL UNIQUE, author VARCHAR(150) NOT NULL, published_year INT CHECK (published_year > 0), copies INT CHECK (copies >= 1) );
3. Create the loans table CREATE TABLE loans ( id INT AUTO_INCREMENT PRIMARY KEY, book_id INT NOT NULL, borrower VARCHAR(100) NOT NULL, loan_date DATE DEFAULT (CURDATE()), FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE RESTRICT );
4. Insert sample data INSERT INTO books (title, author, published_year, copies) VALUES ('1984', 'George Orwell', 1949, 5), ('Clean Code', 'Robert C. Martin', 2008, 3), ('The Pragmatic Programmer', 'Andrew Hunt', 1999, 4); INSERT INTO loans (book_id, borrower) VALUES (1, 'Nguyen Van A'), (3, 'Tran Thi B');

---

Question 1: Display Book List with Sorting (5 marks)

• (3 marks) Fetch and display all records from the books table, sorted by published_year in descending order.
• (1 mark) Include an “Add Book” button or link that navigates to add_book.php.
• (1 mark) Next to each book, provide an “Edit” link to edit_book.php passing the book’s id as a URL parameter (e.g., ?id=2).

Question 2: Adding a New Book (5 marks)

In add_book.php:

• (1.5 marks) Build an HTML form to input title, author, published_year, and copies.
• (0.5 marks) Validate that title is unique (no duplicates in the database).
• (0.5 marks) Validate that published_year is greater than 0.
• (0.5 marks) Validate that copies is at least 1.
• (2 marks) If all validations pass, insert the new record into the books table.

Question 3: Editing a Book (5 marks)

In edit_book.php:

• (2 marks) Load the existing book data based on the id parameter and pre-fill an HTML form with title, author, published_year, and copies.
• (0.5 marks) Validate that the updated title remains unique, excluding the current record.
• (0.5 marks) Validate that published_year > 0 and copies ≥ 1.
• (0.5 marks) Verify that the given id exists before attempting the update; if not, show an error or redirect.
• (1.5 marks) Update the record in the books table when all validations succeed.

Question 4: Deleting a Book (5 marks)

• (1 mark) On the main listing page, add a “Delete” button next to each book.
• (1 mark) Use JavaScript confirm("Are you sure you want to delete this book?") before deletion.
• (1 mark) If the book has existing entries in the loans table, prevent deletion and alert “Cannot delete because the book is on loan!”.
• (2 marks) If there are no related loans, delete the record from the books table.