source: BookView.php

<?php
session_start();

require 'connect.php'; 

if (isset($_GET['bookid'])) {
    
    $param = $_GET['bookid'];

    //π_{book., book_details., firstname, lastname, ...} (σ_{book.bookid = :bookid } (book ⨝ book_author ⨝ author ⨝ book_details))    
    //QUERY TREE?
    $sql = "SELECT book.*, book_details.*, firstname, lastname, author_description, author_image FROM book
            INNER JOIN book_author ON book.bookid =  book_author.bookid
            INNER JOIN author ON book_author.authorid = author.authorid
            INNER JOIN book_details ON book.bookid = book_details.bookid
            WHERE book.bookid = :bookid;
            ";
    $stmt = $conn->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;
    }


}

?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Book Details</title>
    <link rel="stylesheet" href="CSS/BookView.css">
</head>
<body>
<?php include 'Components/Header.html'; ?>

    <div class="container">
    <div class="breadcrumb">
        <a href="./HomePage.php">Home</a> / 
        <a href="./Books.php?">Books</a> / 
        <a href="./Books.php?genres[]=<?php echo $book_genre ?>"> <?php echo $book_genre ?></a> / 
        <?php echo $book_name ?>
    </div>


        <div class="book-details">
            <div class="book-image">
                <img src="./BookImages/<?php echo $book_image?>" alt="Book Cover" class="book-cover">
            </div>
            <div class="book-info">
                <h1><?php echo $book_name ?></h1>
                <div class="book-meta">
                    <p>By <?php echo $author_name ?> | Published: <?php echo $book_year ?></p>
                    <p>Genre: <?php echo $book_genre ?></p>
                    <p>ISBN: <?php echo $book_isbn ?></p>
                </div>
                <div class="availability available">
                    Available (<?php echo $book_available ?> copies)
                </div>
                <div class="actions">
                    <form action="./Books.php" method="GET">
                        <input type="hidden" name="bookid" value="<?php echo $bookid ?>" >
                        <button class="btn btn-primary" type="submit" value="add-to-cart" name="submit">Add to cart</button>
                    </form>
                </div>
                <div class="book-description">
                    <h2 class="section-title">Description</h2>
                    <p><?php echo $book_description ?></p>
                </div>
                <div class="book-details-meta">
                    <h2 class="section-title">Details</h2>
                    <p><strong>Format: </strong><?php echo $book_format ?></p>
                    <p><strong>Language: </strong><?php echo $book_language ?></p>
                    <p><strong>Publisher: </strong><?php echo $book_publisher ?></p>
                    <p><strong>Pages: </strong><?php echo $book_pages ?></p>
                </div>
            </div>
        </div>

        <div class="author-section">
            <h2 class="section-title">About the Author</h2>
            <div class="author-info">
                <img src="./AuthorImages/<?php echo $author_image ?>" alt="Author Photo" class="author-photo">
                <div>
                    <h3><?php echo $author_name ?></h3>
                    <p><?php echo $author_bio ?></p>
                </div>
            </div>
        </div>

        <div class="recommendations">
            <h2 class="section-title">You May Also Like</h2>
            <div class="book-grid" id="recommendations">
                <!-- Recommendations will be populated by JavaScript -->
            </div>
        </div>

        <div class="recommendations-author">
            <h2 class="section-title">More From The Author</h2>
            <div class="book-grid" id="recommendations-author">
                <!-- Recommendations will be populated by JavaScript -->
            </div>
        </div>
    </div>

    <script>
       const recommendedBooks = [
        <?php
          foreach($similar_books as $book) {
            echo "{title: '" . $book['title'] . "', 
            author: '" . $book['firstname'] . " " . $book['lastname'] . "', 
            image: '" . $book['coverimage'] . "', 
            genre: '" . $book['genre'] . "', 
            bookid: '" . $book['bookid'] . "'},";
          }
        ?>
       ];

       const authorBooks = [
        <?php
          foreach($author_books as $author_book) {
            echo "{title: '" . $author_book['title'] . "', 
            author: '" . $author_book['firstname'] . " " . $author_book['lastname'] . "', 
            image: '" . $author_book['coverimage'] . "', 
            genre: '" . $author_book['genre'] . "', 
            bookid: '" . $author_book['bookid'] . "'},";
          }
        ?>
       ];

        // Function to populate recommended books
        function populateRecommendations() {
            const recommendationsContainer = document.getElementById('recommendations');
            
            recommendedBooks.forEach(book => {
                const bookCard = document.createElement('div');
                bookCard.className = 'book-card';
                
                bookCard.innerHTML = `
                    <img src="./BookImages/${book.image}" alt="${book.title}">
                    <a href='./BookView.php?bookid=${book.bookid}' style="text-decoration: none"><h3 class="book-title" style="color: green;">${book.title}</h3></a>
                    <p>${book.author}</p>
                    <p>${book.genre}</p>
                `;
                
                recommendationsContainer.appendChild(bookCard);
            });
        }

        function populateMoreAuthor(){
            const recommendationsAuthorContainer = document.getElementById('recommendations-author');
            
            authorBooks.forEach(book => {
                const bookCard = document.createElement('div');
                bookCard.className = 'book-card';
                
                bookCard.innerHTML = `
                    <img src="./BookImages/${book.image}" alt="${book.title}">
                    <a href='./BookView.php?bookid=${book.bookid}' style="text-decoration: none"><h3 class="book-title" style="color: green;">${book.title}</h3></a>
                    <p>${book.author}</p>
                    <p>${book.genre}</p>
                `;
                
                recommendationsAuthorContainer.appendChild(bookCard);
            });
        }

        // Initialize the page
        document.addEventListener('DOMContentLoaded', () => {
            populateRecommendations();
            populateMoreAuthor();

        });
    </script>

<?php include 'Components/Footer.html'; ?>

</body>
</html>