========================================
LIBRARY MANAGEMENT WEB APPLICATION - FULL SETUP GUIDE (PHP 8.2 / 8.3)
========================================

STUDENT INSTRUCTIONS
=====================

DESCRIPTION:
This project builds a simple library management web app where users can register, login, search/view books, borrow and return books, and leave feedback. It demonstrates sessions, prepared statements, simple inventory logic, and basic triggers/procedures.

TOOLS REQUIRED:
---------------
- Uniform Server (Apache, MySQL, PHP 8.2 or 8.3)
- Browser (Chrome, Firefox, etc.)

PROJECT FILES:
--------------
- db.php (Database connection)
- register.php (User registration)
- login.php (User login)
- books.php (View/search books)
- borrow.php (Borrow a book)
- return.php (Return a borrowed book)
- my_loans.php (View your borrowed books)
- contact.php (Submit feedback/comments)
- index.html (Landing page)
- style.css (Basic styling)
- library_site.sql (Database setup script)

========================================
STEP 1: INSTALL UNIFORM SERVER
========================================
1. Download from: https://www.uniformserver.com
2. Extract the zip file.
3. Run UniController.exe.
4. Start Apache and MySQL services.

========================================
STEP 2: SETUP DATABASE IN UNIFORM SERVER
========================================
1. Open MySQL Console in UniController.
2. Run the following SQL to create database and tables.

--- SQL START ---

CREATE DATABASE library_site;
USE library_site;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(150),
    isbn VARCHAR(20),
    total_copies INT DEFAULT 1,
    available_copies INT DEFAULT 1,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE loans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    book_id INT NOT NULL,
    borrowed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    due_date DATE,
    returned_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE
);

CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    book_id INT,
    comment TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (book_id) REFERENCES books(id) ON DELETE CASCADE
);

CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_book_title ON books(title);

INSERT INTO books (title, author, isbn, total_copies, available_copies, description) VALUES
('1984', 'George Orwell', '9780451524935', 3, 3, 'Dystopian novel about totalitarianism.'),
('To Kill a Mockingbird', 'Harper Lee', '9780061120084', 2, 2, 'Classic novel on racial injustice.'),
('The Hobbit', 'J. R. R. Tolkien', '9780618968633', 4, 4, 'Fantasy adventure prelude to Lord of the Rings.'),
('Clean Code', 'Robert C. Martin', '9780132350884', 1, 1, 'A handbook of agile software craftsmanship.'),
('Digital Minimalism', 'Cal Newport', '9780525536512', 1, 1, 'On technology use and attention management.');

--- SQL END ---

========================================
STEP 3: STORED PROCEDURE & TRIGGERS
========================================
Add a stored procedure to create a loan and update available copies, with a trigger to log returns.

DELIMITER //

CREATE PROCEDURE borrow_book(IN uid INT, IN bid INT, IN due DATE)
BEGIN
    DECLARE avail INT;
    SELECT available_copies INTO avail FROM books WHERE id = bid FOR UPDATE;
    IF avail > 0 THEN
        UPDATE books SET available_copies = available_copies - 1 WHERE id = bid;
        INSERT INTO loans (user_id, book_id, due_date) VALUES (uid, bid, due);
    ELSE
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No copies available';
    END IF;
END //

DELIMITER ;

DELIMITER //

CREATE PROCEDURE return_book(IN loanid INT)
BEGIN
    DECLARE b INT;
    UPDATE loans SET returned_at = NOW() WHERE id = loanid AND returned_at IS NULL;
    SELECT book_id INTO b FROM loans WHERE id = loanid;
    IF b IS NOT NULL THEN
        UPDATE books SET available_copies = available_copies + 1 WHERE id = b;
    END IF;
END //

DELIMITER ;

DELIMITER //

CREATE TRIGGER after_loan_return
AFTER UPDATE ON loans
FOR EACH ROW
BEGIN
    IF OLD.returned_at IS NULL AND NEW.returned_at IS NOT NULL THEN
        INSERT INTO comments (user_id, book_id, comment) VALUES (NEW.user_id, NEW.book_id, CONCAT('Returned on ', NEW.returned_at));
    END IF;
END //

DELIMITER ;

========================================
STEP 4: FOLDER SETUP
========================================
1. Navigate to: C:\UniServerZ\www
2. Create a folder: library
3. Place all files listed above into this folder.

========================================
STEP 5: PHP & HTML FILES
========================================
Create the following files in the folder:

---- db.php ----
<?php
$mysqli = new mysqli("localhost", "root", "", "library_site");
if ($mysqli->connect_error) { die("Connection failed: ".$mysqli->connect_error); }
$mysqli->set_charset('utf8mb4');
?>

---- register.php ----
<?php
include("db.php"); session_start();
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $username = trim($_POST['username'] ?? '');
    $password = $_POST['password'] ?? '';
    if ($username && $password) {
        $stmt = $mysqli->prepare("SELECT id FROM users WHERE username = ?");
        $stmt->bind_param("s",$username); $stmt->execute(); $stmt->store_result();
        if ($stmt->num_rows==0) {
            $hash=password_hash($password,PASSWORD_DEFAULT);
            $ins=$mysqli->prepare("INSERT INTO users(username,password) VALUES(?,?)");
            $ins->bind_param("ss",$username,$hash); $ins->execute();
            $_SESSION['user']=$username; header("Location: books.php"); exit;
        } else { $error="Username already taken."; }
    } else { $error="Enter username and password."; }
}
?>
<html><body>
<h2>Register</h2>
<?php if(!empty($error)) echo "<p style='color:red;'>$error</p>"; ?>
<form method="POST">Username:<input name="username"><br>Password:<input type="password" name="password"><br><input type="submit" value="Register"></form>
</body></html>

---- login.php ----
<?php
include("db.php"); session_start();
if ($_SERVER['REQUEST_METHOD']==='POST') {
    $username=$_POST['username']??''; $password=$_POST['password']??'';
    $stmt=$mysqli->prepare("SELECT password FROM users WHERE username=?"); $stmt->bind_param("s",$username); $stmt->execute(); $stmt->bind_result($hash);
    if($stmt->fetch() && password_verify($password,$hash)) { $_SESSION['user']=$username; header("Location: books.php"); exit; }
    else { $error="Invalid username or password."; }
}
?>
<html><body>
<h2>Login</h2>
<?php if(!empty($error)) echo "<p style='color:red;'>$error</p>"; ?>
<form method="POST">Username:<input name="username"><br>Password:<input type="password" name="password"><br><input type="submit" value="Login"></form>
</body></html>

---- books.php ----
<?php
include("db.php"); session_start();
$user=$_SESSION['user']??null; $search=trim($_GET['q']??'');
?>
<html><body>
<h1>Library Catalog</h1>
<p>Welcome <?php echo htmlspecialchars($user??'Guest');?> | <a href="my_loans.php">My Loans</a> | <a href="contact.php">Feedback</a></p>
<form method="GET">Search:<input name="q" value="<?php echo htmlspecialchars($search);?>"><input type="submit" value="Search"></form>
<?php
if($search!=='') {$stmt=$mysqli->prepare("SELECT * FROM books WHERE title LIKE CONCAT('%',?,'%') OR author LIKE CONCAT('%',?,'%')"); $stmt->bind_param("ss",$search,$search); $stmt->execute(); $res=$stmt->get_result();}
else { $res=$mysqli->query("SELECT * FROM books"); }
while($row=$res->fetch_assoc()){ echo "<div><h3>".htmlspecialchars($row['title'])."</h3><p>Author: ".htmlspecialchars($row['author'])." | Available: ".$row['available_copies']." / ".$row['total_copies']."</p><p>".nl2br(htmlspecialchars($row['description']))."</p>";
if($user){ echo "<form method='POST' action='borrow.php'><input type='hidden' name='book_id' value='".$row['id']."'><label>Days:<input name='days' value='14'></label><button>Borrow</button></form>"; } else { echo "<p><em>Login to borrow</em></p>"; } echo "</div>"; }
?>
</body></html>

---- borrow.php ----
<?php
include("db.php"); session_start();
if(empty($_SESSION['user'])){header("Location: login.php"); exit;}
if($_SERVER['REQUEST_METHOD']==='POST'){
$book_id=(int)($_POST['book_id']??0); $days=(int)($_POST['days']??14);
$stmt=$mysqli->prepare("SELECT id FROM users WHERE username=?"); $stmt->bind_param("s",$_SESSION['user']); $stmt->execute(); $stmt->bind_result($uid); $stmt->fetch(); $stmt->close();
$due=date('Y-m-d',strtotime("+$days days"));
if(!$mysqli->query("CALL borrow_book($uid,$book_id,'$due')")) { echo "Failed: ".$mysqli->error; } else { header("Location: my_loans.php"); exit; }
}
?>

---- my_loans.php ----
<?php
include("db.php"); session_start(); if(empty($_SESSION['user'])){header("Location: login.php"); exit;}
$stmt=$mysqli->prepare("SELECT id FROM users WHERE username=?"); $stmt->bind_param("s",$_SESSION['user']); $stmt->execute(); $stmt->bind_result($uid); $stmt->fetch(); $stmt->close();
$res=$mysqli->prepare("SELECT l.id as loan_id,b.title,b.author,l.borrowed_at,l.due_date,l.returned_at FROM loans l JOIN books b ON l.book_id=b.id WHERE l.user_id=?"); $res->bind_param("i",$uid); $res->execute(); $result=$res->get_result();
?>
<html><body><h2>My Loans</h2><a href="books.php">Back to catalog</a>
<?php while($row=$result->fetch_assoc()){ echo "<div><h4>".htmlspecialchars($row['title'])."</h4><p>Borrowed: ".htmlspecialchars($row['borrowed_at'])." | Due: ".htmlspecialchars($row['due_date'])."</p>";
if(empty($row['returned_at'])){ echo "<form method='POST' action='return.php'><input type='hidden' name='loan_id' value='".$row['loan_id']."'><button>Return</button></form>";} else {echo "<p><em>Returned: ".htmlspecialchars($row['returned_at'])."</em></p>";} echo "</div>";} ?>
</body></html>

---- contact.php ----
<?php
include("db.php"); session_start();
$msg='';
if($_SERVER['REQUEST_METHOD']==='POST'){
$comment=trim($_POST['comment']??''); $book_id=(int)($_POST['book_id']??0); $uid=null;
if(!empty($_SESSION['user'])){$q=$mysqli->prepare("SELECT id FROM users WHERE username=?");$q->bind_param("s",$_SESSION['user']);$q->execute();$q->bind_result($uid);$q->fetch();$q->close();}
$ins=$mysqli->prepare("INSERT INTO comments(user_id,book_id,comment) VALUES(?,?,?)"); $ins->bind_param("iis",$uid,$book_id,$comment); if($ins->execute()){$msg="Comment submitted.";} else{$msg="Failed.";}
}
?>
<html><body><h2>Feedback / Comment</h2><?php if($msg) echo "<p>$msg</p>"; ?>
<form method="POST">Book ID(optional):<input name="book_id"><br>Comment:<br><textarea name="comment" rows="5" required></textarea><br><input type="submit" value="Send"></form>
</body></html>

---- index.html ----
<html><body><h1>Community Library</h1><ul>
<li><a href="register.php">Register</a></li>
<li><a href="login.php">Login</a></li>
<li><a href="books.php">View Catalog</a></li>
<li><a href="my_loans.php">My Loans</a></li>
<li><a href="contact.php">Feedback</a></li>
</ul></body></html>

---- style.css ----
body{font-family:Arial,sans-serif;background:#f7fbff;padding:20px;}
h1,h2,h3{color:#1b3b6f;}
form{background:#fff;padding:12px;border-radius:6px;max-width:700px;}

========================================
STEP 6: RUN & TEST YOUR APPLICATION
========================================
1. Start Apache and MySQL.
2. Import `library_site.sql` in MySQL.
3. Open browser at http://localhost/library/index.html
4. Register a user, login, borrow books, view loans, return books, and leave comments.
5. Test edge cases (borrow when no copies, return already returned, etc.)

========================================
STEP 7: BACKUP YOUR PROJECT
========================================
- Export MySQL database: mysqldump -u root library_site > library_site.sql
- Zip all files (*.php, *.html, style.css, library_site.sql)

========================================

This project demonstrates sessions, prepared statements, stored procedures, triggers, and inventory logic in PHP & MySQL.