prepare("SELECT book.bookid, coverimage, title, genre, publishedyear, firstname, lastname
FROM book
INNER JOIN book_author ON book.bookid = book_author.bookid
INNER JOIN author ON book_author.authorid = author.authorid
WHERE book.publishedyear BETWEEN 2020 AND 2024 LIMIT 20;
");
$stmt->execute();
$new_releases = [];
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$new_releases[] = $row;
}
/* Most Read Books */
$stmt = $conn->prepare("SELECT book.bookid, book.CoverImage, book.Title, book.Genre, author.FirstName, author.LastName, COUNT(loan.LoanID) AS loan_count FROM book
INNER JOIN book_copies ON book.BookID = book_copies.BookID
LEFT JOIN loan ON book_copies.CopyID = loan.BookCopyID
INNER JOIN book_author ON book.BookID = book_author.BookID
INNER JOIN author ON book_author.AuthorID = author.AuthorID
GROUP BY book.BookID, book.Title, book.CoverImage, book.Genre, author.FirstName, author.LastName
ORDER BY loan_count DESC LIMIT 20;
");
$stmt->execute();
$most_read_books = [];
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$most_read_books[] = $row;
}
/* Least Read Books */
$stmt = $conn->prepare("SELECT book.bookid, book.CoverImage, book.Title, book.Genre, author.FirstName, author.LastName, COUNT(loan.LoanID) AS loan_count FROM book
INNER JOIN book_copies ON book.BookID = book_copies.BookID
LEFT JOIN loan ON book_copies.CopyID = loan.BookCopyID
INNER JOIN book_author ON book.BookID = book_author.BookID
INNER JOIN author ON book_author.AuthorID = author.AuthorID
GROUP BY book.BookID, book.Title, book.CoverImage, book.Genre, author.FirstName, author.LastName
ORDER BY loan_count ASC LIMIT 20;
");
$stmt->execute();
$least_read_books = [];
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$least_read_books[] = $row;
}
/* Classics */
$stmt = $conn->prepare("SELECT book.bookid, coverimage, title, genre, publishedyear, firstname, lastname
FROM book
INNER JOIN book_author ON book.bookid = book_author.bookid
INNER JOIN author ON book_author.authorid = author.authorid
WHERE book.publishedyear BETWEEN 1000 AND 1970 LIMIT 20;
");
$stmt->execute();
$classics = [];
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$classics[] = $row;
}
/* Comics */
$stmt = $conn->prepare("SELECT book.bookid, coverimage, title, genre, publishedyear, firstname, lastname
FROM book
INNER JOIN book_author ON book.bookid = book_author.bookid
INNER JOIN author ON book_author.authorid = author.authorid
WHERE book.genre IN ('Manga', 'Graphic Novel')
LIMIT 20;
");
$stmt->execute();
$comics = [];
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$comics[] = $row;
}
?>
Home
