source: BorrowBook.php

<?php

session_start();
require './connect.php';

if (!isset($_SESSION['userid'])) {
    header("Location: ./Sign&Log.php");
    die();
}

try {
    $conn->beginTransaction();
    
    //π_{users.*, member.memberid} (σ_{users.userid = :userid } (users ⨝ member))
    $stmt = $conn->prepare("SELECT users.*, member.memberid 
                           FROM users 
                           JOIN member ON users.userid = member.userid 
                           WHERE users.userid = :userid");
    $stmt->bindParam(':userid', $_SESSION['userid'], PDO::PARAM_INT);
    $stmt->execute();
    
    if ($stmt->rowCount() <= 0) {
        header("Location: ./EditProfile.php");
        exit();
    }
    
    $personal_data = $stmt->fetch(PDO::FETCH_ASSOC);
    $memberid = $personal_data['memberid']; // Get memberid here
    
    // items from cart
    //σ_{memberid = :memberid } (cart)
    $cart_query = $conn->prepare("SELECT * FROM cart WHERE memberid = :memberid");
    $cart_query->bindParam(':memberid', $memberid);
    $cart_query->execute();
    $order_items = $cart_query->fetchAll(PDO::FETCH_ASSOC);

    if (empty($order_items)) {
        echo "Cart is empty";
        exit();
    }

    // into loan table
    //π_{copyid} (σ_{bookid = :bookid ∧ (condition = 'Good' ∨ condition = 'New')} (book_copies - π_{copyid} (σ_{status IN ('On Time', 'Soon', 'Overdue')} (loan)))
    $loan_stmt = $conn->prepare("INSERT INTO loan (loandate, returndate, status, memberid, bookcopyid) 
                                VALUES (CURRENT_DATE, :return_date, :status, :memberid, :bookcopyid)");

    $returnDate = date('Y-m-d', strtotime("+14 days"));
    $status = 'On Time';

    foreach ($order_items as $item) {
        // Find available copy
        $copy_stmt = $conn->prepare("SELECT copyid 
                                   FROM book_copies 
                                   WHERE bookid = :bookid 
                                   AND (condition = 'Good' OR condition = 'New')
                                   AND copyid NOT IN (
                                       SELECT bookcopyid 
                                       FROM loan 
                                       WHERE status IN ('On Time', 'Soon', 'Overdue')
                                   )
                                   LIMIT 1");
        
        $copy_stmt->bindParam(':bookid', $item['bookid']);
        $copy_stmt->execute();
        
        if ($copy = $copy_stmt->fetch(PDO::FETCH_ASSOC)) {
            // loan record
            $loan_stmt->execute([
                ':return_date' => $returnDate,
                ':status' => $status,
                ':memberid' => $memberid,
                ':bookcopyid' => $copy['copyid']
            ]);
            
            //echo "Loan created for book copy: " . $copy['copyid'] . "<br>";
        } else {
            echo "No available copy for book ID: " . $item['bookid'] . "<br>";
            $conn->rollBack();
            exit();
        }
    }

    // Clear cart
    $clear_cart = $conn->prepare("DELETE FROM cart WHERE memberid = :memberid");
    $clear_cart->execute([':memberid' => $memberid]);

    $conn->commit();
    //echo "All loans created successfully!";
    header("Location: ./Profile.php");

} catch (PDOException $e) {
    $conn->rollBack();
    echo "Error: " . $e->getMessage();
}


?>