| | 13 | {{[#!sql |
| | 14 | $sql = "SELECT book.*, book_details.*, firstname, lastname, author_description, author_image FROM book |
| | 15 | INNER JOIN book_author ON book.bookid = book_author.bookid |
| | 16 | INNER JOIN author ON book_author.authorid = author.authorid |
| | 17 | INNER JOIN book_details ON book.bookid = book_details.bookid |
| | 18 | WHERE book.bookid = :bookid; |
| | 19 | "; |
| | 20 | $stmt = $conn->prepare($sql); |
| | 21 | $stmt->bindParam(':bookid', $param, PDO::PARAM_INT); |
| | 22 | |
| | 23 | $sql2 = "SELECT |
| | 24 | b.BookID, |
| | 25 | b.TotalCopies - COALESCE(COUNT(l.LoanID), 0) AS AvailableCopies |
| | 26 | FROM Book b |
| | 27 | LEFT JOIN Loan l |
| | 28 | ON l.BookCopyID IN (SELECT CopyID FROM Book_Copies WHERE BookID = b.BookID) |
| | 29 | AND l.Status IN ('On Time', 'Soon', 'Overdue') |
| | 30 | WHERE |
| | 31 | b.BookID = :bookid |
| | 32 | GROUP BY |
| | 33 | b.BookID, b.TotalCopies;"; |
| | 34 | }}} |
| | 35 | |
| | 36 | |
