Create Book Table
CREATE TABLE tblBooks (
pmkBookID INT AUTO_INCREMENT PRIMARY KEY,
fldTitle VARCHAR(60),
fldLastName VARCHAR(18),
fldFirstName VARCHAR(18),
fldYear INT,
fldGenre VARCHAR(18),
fldSummary VARCHAR(5000),
varRating INT
)
Create Review Table
CREATE TABLE tblReviews (
pmkReviewID INT AUTO_INCREMENT PRIMARY KEY,
fkBookID INT,
fldReview VARCHAR(5000),
fldScore INT,
fldEmail VARCHAR(50),
FOREIGN KEY (fkBookID) REFERENCES tblBooks(pmkBookID)
)
Fetch Top 5 Books
SELECT fldTitle, fldLastName, fldFirstName, fldYear, varRating FROM tblBooks
ORDER BY varRating DESC
LIMIT 5
Fetch 5 Recently Added Books
SELECT fldTitle, fldLastName, fldFirstName, fldYear, varRating FROM tblBooks
ORDER BY pmkBookID DESC
LIMIT 5
Submit Review
INSERT INTO tblReviews(fkBookID, fldReview, fldScore, fldEmail)
VALUES (?,?,?,?)
Insert Form Data
INSERT INTO tblBooks(fldTitle, fldLastName, fldFirstName, fldYear, fldGenre, fldSummary, varRating)
VALUES (?,?,?,?,?,?,?)
Fetch Book Reviews
SELECT fldReview, fldScore, fldEmail FROM tblReviews
WHERE fkBookID = ?
ORDER BY pmkReviewID DESC
Fetch Book Data
SELECT fldTitle, fldLastName, fldFirstName, fldYear, fldGenre, fldSummary, varRating FROM tblBooks
WHERE pmkBookID = ?
Set New Score for Book
UPDATE tblBooks SET varRating = ?
WHERE pmkBookID = ?
Fetch Scores For Book
SELECT fldScore FROM tblReviews
WHERE fkBookID = ?