prepare($sql);
$stmt->bindParam(':bookid', $param, PDO::PARAM_INT);
//π_{BookID, TotalCopies - COUNT(LoanID)} (σ_{BookID = :bookid } (Book ⨝ (Loan ⨝ (π_{CopyID} (σ_{BookID = b.BookID} Book_Copies)))))
$sql2 = "SELECT
b.BookID,
b.TotalCopies - COALESCE(COUNT(l.LoanID), 0) AS AvailableCopies
FROM Book b
LEFT JOIN Loan l
ON l.BookCopyID IN (SELECT CopyID FROM Book_Copies WHERE BookID = b.BookID)
AND l.Status IN ('On Time', 'Soon', 'Overdue')
WHERE
b.BookID = :bookid
GROUP BY
b.BookID, b.TotalCopies;";
$stmt->execute();
$result = $stmt->fetch(PDO::FETCH_ASSOC);
if ($result) {
$bookid = $result['bookid'];
$book_name = $result['title'];
$book_isbn = $result['isbn'];
$book_genre = $result['genre'];
$book_year = $result['publishedyear'];
$book_image = $result['coverimage'];
$book_copies = $result['totalcopies'];
$book_description = $result['description'];
$author_name = $result['firstname'] . ' ' . $result['lastname'];
$author_bio = $result['author_description'];
$author_image = $result['author_image'];
$book_format = $result['format'];
$book_language = $result['language'];
$book_publisher = $result['publisher'];
$book_pages = $result['pages'];
}
else {
// ERROR HANDLING
}
$stmt = $conn->prepare($sql2);
$stmt->bindParam(':bookid', $param, PDO::PARAM_INT);
// Execute the query
$stmt->execute();
$result = $stmt->fetch(PDO::FETCH_ASSOC);
if ($result) {
$book_available = $result['availablecopies'];
}
else {
// ERROR HANDLING
}
//Similar Books
//π_{bookid, coverimage, title, genre, firstname, lastname} (σ_{genre = :bookgenre ∧ bookid ≠ :bookid } (book ⨝ book_author ⨝ author))
$stmt = $conn->prepare("SELECT book.bookid, coverimage, title, genre, 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 = :bookgenre AND book.bookid != :bookid LIMIT 5;
");
$stmt->bindParam(':bookgenre', $book_genre, PDO::PARAM_STR);
$stmt->bindParam(':bookid', $bookid, PDO::PARAM_INT);
// Execute the query
$stmt->execute();
$similar_books = [];
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$similar_books[] = $row;
}
//More Author
//π_{bookid, coverimage, title, genre, firstname, lastname} (σ_{authorid IN (π_{authorid} (σ_{bookid = :bookid } book_author)) ∧ bookid ≠ :bookid } (book ⨝ book_author ⨝ author))
$stmt = $conn->prepare("SELECT book.bookid, coverimage, title, genre, 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_author.authorid IN (
SELECT book_author.authorid
FROM book_author
WHERE book_author.bookid = :bookid
) AND book.bookid != :bookid
LIMIT 5;
");
$stmt->bindParam(':bookid', $bookid, PDO::PARAM_INT);
// Execute the query
$stmt->execute();
$author_books = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$author_books[] = $row;
}
}
?>
Book Details