PHP MySQL mysqli for Book Record Management System Example
From w3cyberlearnings
Contents |
Requirements
- Install MySQL, PHP, Apache Web Server, and Netbean or Eclipse IDE
- SQL database and MySQL
- Apache Web Server Configuration
- Understand the basic of PHP
Page Structures
mybookrecord/add.php //for adding book record mybookrecord/delete.php //for deleting book record mybookrecord/edit.php //for editing or update book record mybookrecord/rating.php //for reviewing and rating book mybookrecord/table_list.php //main page to list all the available books mybookrecord/home.php //link to the table_list.php mybookrecord/function.php //link to the add.php pages mybookrecord/star_rate.gif //rate star use for user review/rating mybookrecord/commit.php //all the activities implement in the commit.php mybookrecord/review.php //to display book detail
Goal
- This tutorial will show you how to use PHP with MySQL for insert, update, delete book information.
Star GIF for rating
- This is the star use to display the rating level
- Download this star and save it on the project directory
- Save the file: star_rate.gif
Tables and MySQL Database
- You need to have the MySQL up and running
- Create the following tables for this application
book table
- This table uses to store book information.
- Table name: book
CREATE TABLE book ( book_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, book_name VARCHAR(50) NOT NULL, book_type SMALLINT(5) UNSIGNED NOT NULL, book_year SMALLINT(5) UNSIGNED NOT NULL, book_author INT UNSIGNED NOT NULL, book_publisher INT UNSIGNED NOT NULL );
booktype table
- This table uses to store the book type.
- Users will not allow to edit this book type.
- Table name: booktype
- All records on the booktype table are predefined.
CREATE TABLE booktype ( booktype_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, booktype_label VARCHAR(50) NOT NULL );
book_reviews table
- This table stores book review information from users.
- Table name: book_reviews
- Please note that the book_review_id is not a primary key or an auto increment.
CREATE TABLE book_reviews( book_review_id INT NOT NULL, book_review_date DATE NOT NULL, book_reviewer_email VARCHAR(100) NOT NULL, book_review_comment VARCHAR(255) NOT NULL, book_review_rating TINYINT(3) UNSIGNED NOT NULL );
people table
- This table uses for book author and book publisher.
- All the records on the people table are predefined.
CREATE TABLE people( people_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, people_firstname VARCHAR(50) NOT NULL, people_lastname VARCHAR(50) NOT NULL, people_isauthor TINYINT(1) UNSIGNED NOT NULL, people_ispublisher TINYINT(1) UNSIGNED NOT NULL );
Insert Records
- You are required to insert records to the tables.
Insert Record to the people table
- These records are for book author and book publisher.
- You need these records when you create a new book.
INSERT INTO people VALUES (1,'chris','stepen',1,1),
(2,'Sunny','chong',1,1),
(3,'Milk','Yiah',1,1),
(4,'Phi','Aeah',1,1),
(5,'Christ','Zvan',0,1),
(6,'David','van',1,0),
(7,'Jeffery','Micsuzki',0,1);
Insert Record to the booktype table
- These records are for book types.
- This table will generate a drop down list.
INSERT INTO booktype VALUES (1,'Computer Programming'),
(2,'Mathematics'),
(3,'Biology'),
(4,'Nursing'),
(5,'Dramma'),
(6,'English'),
(7,'Social-Science'),
(8,'Comedy'),
(9,'Political Science');
Insert Record to the book table
- These are the sample records for books that we have already created.
INSERT INTO book VALUES (1,'Java',1,2011,3,2),
(2,'PHP',1,2011,3,3),
(3,'MySQL',1,2004,4,1);
Insert Record to the book_reviews table
- We insert two records for the book reviews.
- This is for testing only.
INSERT INTO book_reviews VALUES (1,'2011-12-23','[email protected]','lovely book, i like to read this.',3), (1,'2011-12-23','[email protected]','let share it all together.',5);
Predefined links
Code (home.php) for link to the book home page
<a href="table_list.php?display=<?php echo $_GET['display']; ?>">Back to home</a>
Code (function.php) for link to the add.php page
<hr width="100%"/>
<div style="clear:both">
<a href="add.php?display=<?php echo $_REQUEST['display'];?>">[Add]</a>
</div>
Center Control Access Functions
- This program uses to control add, rating, edit, and delete activities.
Redirect page
- It is redirected to PAGE_NAME.php.
header('Location: PAGE_NAME.php');
Redirect page with parameter
- There are two parameters with this example id=3 and optioin=detail.
header('Location: PAGE_NAME.php?id=3&option=detail');
PHP code (commit.php)
<?php
$option = $_REQUEST['submit_opt'];
$id = $_REQUEST['id'];
$db = new mysqli("localhost","root","caojiang","school");
if(mysqli_connect_errno ()){
printf("Connect failed: %s<br/>", mysqli_connect_error());
}
// book
$book_name = $_POST['bookName'];
$book_type = $_POST['bookType'];
$book_year = $_POST['bookYear'];
$book_author= $_POST['bookAuthor'];
$book_publisher = $_POST['bookPublisher'];
// rating
$email = $_POST['rating_email'];
$comment = $_POST['comment'];
$rating = $_POST['review_rating'] ;
switch($option) {
case 'add':
if(!empty($book_name) && !empty($book_type)
&& !empty($book_year) && !empty($book_author)
&& !empty($book_publisher)) {
$query ="INSERT INTO book
(book_name, book_type, book_year, book_author,book_publisher)
VALUES(?,?,?,?,?)";
$record =$db->prepare($query);
$record->bind_param("siiii",
$book_name,
$book_type,
$book_year,
$book_author,
$book_publisher);
$record->execute();
header('Location: review.php?id='.$record->insert_id);
$record->close();
}
else {
header('Location: add.php');
}
break;
case 'rating':
if(!empty($email) && !empty($comment) && !empty($rating)) {
$query = "INSERT INTO book_reviews
(book_review_id,
book_reviewer_email,
book_review_comment,
book_review_rating,
book_review_date)
VALUES(?,?,?,?,now())";
$record =$db->prepare($query);
$record->bind_param("issi",$id,$email, $comment, $rating);
$record->execute();
$record->close();
header('Location: review.php?id='.$id);
}
else {
header('Location: rating.php?id='. $id);
}
break;
case 'edit':
if(!empty($book_name) && !empty($book_type)
&& !empty($book_year) && !empty($book_author)
&& !empty($book_publisher)) {
$query ="UPDATE book SET
book_name=?,
book_type=?,
book_year=?,
book_author=?,
book_publisher=?
WHERE book_id=?";
$record =$db->prepare($query);
$record->bind_param("siiiii",
$book_name,
$book_type,
$book_year,
$book_author,
$book_publisher,
$id);
$record->execute();
$record->close();
header('Location: review.php?id='.$id);
}
else {
header('Location: edit.php?id='. $id);
}
break;
case 'delete':
if($id) {
$query = "DELETE FROM book WHERE book_id=". $id;
$db->query($query);
$query = 'DELETE FROM book_reviews where book_review_id ='. $id;
$db->query($query);
}
else {
header('Location: review.php?id='. $id);
}
break;
}
include 'home.php';
?>
add option
- This option will be executed when the add.php page submit the form data to the commit.php script.
- And, the add.php page uses the post method to send the form data to the commit.php script.
- Within the commit.php script, the book information will be checked to make sure it is not empty.
- If the book information from the add.php is not empty, the book information submit from the add.php will be inserted into the book table.
- And, after inserted into the book table, the commit.php redirect user to the review.php page which is display book information.
- However, if the book information is empty, the commit.php script will direct user back to the add.php page.
rating option
- The rating option will be called when the rating.php page form submit.
- After successfully insert the user review (rating) into the book_reviews table, the commit.php redirect user to the review.php page.
- However, if user does not fill in all the rating.php form data, the commit.php script will redirect user back to the rating.php page.
- Again, this do not include the validation.
edit option
- In the edit option requires to update the book table.
- If user does not fill in all the form value in the edit.php page, the commit.php script will redirect user back to the edit.php page.
- However, when all the form value in the edit.php page is filled in, the commit.php script will direct user to the review.php page after update the book table with the new value.
delete option
- In the delete option will delete the book record from the book table and book_reviews table.
Main Page
This is the home page of the book record system. From this home page you can edit the book, provide review to the book, or delete the book. On the Book Name row, you can display the book detail. The Book Name links to the review.php page, and the [rating] links to the rating.php page, the [delete] links to the delete.php page, and the [edit] links to the edit.php page. Finally, the Add links to the add.php page.
Code (table_list.php)
<?php
// pagination section to list page accordingly
function gen_list($number_rows) {
$title = "";
$total_l = ceil($number_rows/ 5);
for($i=0; $i < $total_l; $i++) {
$n = $i * 5;
$title .= (empty($title) ? " " : "|");
$title .='<a href="table_list.php?display='. $n. '">'. ($i+1). '</a>';
}
return $title;
}
?>
<?php
$db = new mysqli("localhost","root","caojiang","school");
if(mysqli_connect_errno ()){
printf("Connect failed: %s<br/>", mysqli_connect_error());
}
$display = (!empty($_GET['display']) ? abs($_GET['display']) : 0);
// get total book in the record
$query = 'SELECT COUNT(*) FROM book';
$result = $db->prepare($query);
if($result) {
$result->execute();
$result->bind_result($number_rows);
$result->fetch();
$result->close();
}
$title = '<h3>Book Detail</h3>'. ($number_rows <= 5 ?"" : gen_list($number_rows));
$table = $title;
$table .='
<table border="1">
<tr>
<th>Book Name</th>
<th>Book Year</th>
<th>Book Type</th>
<th>Book Author</th>
<th>Book Publisher</th>
<th>Operations</th>
</tr>';
$query = 'SELECT b.book_id,
b.book_name,
b.book_year,
bt.booktype_label,
CONCAT(au.people_firstname," ",au.people_lastname)
AS "author_name",
CONCAT(pl.people_firstname," ",pl.people_lastname)
AS "publisher_name"
FROM (book b INNER JOIN booktype bt ON (b.book_type=bt.booktype_id))
INNER JOIN people au ON
(b.book_author=au.people_id)
INNER JOIN people pl ON
(b.book_publisher=pl.people_id)
ORDER BY b.book_name ASC LIMIT '.$display. ',5' ;
$stmt = $db->prepare($query);
$stmt->execute();
$stmt->bind_result(
$book_id,
$book_name,
$book_year,
$book_type,
$book_author,
$book_publisher);
$count = 0;
while($stmt->fetch()) {
$sbook_name = substr($book_name,0,10);
$count++;
$table .=<<<HTMLL
<tr>
<td><a href="review.php?id=$book_id&display=$display">$sbook_name</a></td>
<td>$book_year</td>
<td>$book_type</td>
<td>$book_author</td>
<td>$book_publisher</td>
<td>
<a href="rating.php?id=$book_id&display=$display&bookname=$sbook_name">[rating]</a>
<a href="delete.php?id=$book_id&display=$display">[delete]</a>
<a href="edit.php?id=$book_id&display=$display">[edit]</a>
</td>
</tr>
HTMLL;
}
$table .='</table>';
echo $table;
echo $count . " Book(s)";
include 'function.php';
?>
Display Result
Add Page
The add.php page fetch records from the booktype and people tables. Because the Book Type drop down list used the record from the booktype table, if there is no record on the booktype table, the add.php page is not generated enough information for user to select. User will not allow to insert any book record without select the Book Type. Finally, we also need to use record from the people table to generate the drop down list for Book Author and Book Publisher drop down list.
Code (add.php)
<?php
$db = new mysqli("localhost","root","caojiang","school");
if(mysqli_connect_errno ()){
printf("Connect failed: %s<br/>", mysqli_connect_error());
}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<h3>Create A New Book Record</h3>
<form method="post" action="commit.php">
<table border="0">
<tr><td>Book Name:</td><td><input type="text" name="bookName" size="20"/></td></tr>
<tr><td>Year:</td>
<td><select name="bookYear">
<option value="">Select Year</option>
<?php
for($i=0; $i<=10; $i++) {
$year = intval(date('Y'))-$i;
echo '<option value="'.($year) .'">'. ($year) . '</option>';
}
?>
</select>
</td>
</tr>
<tr><td>Book Type:</td>
<td><select name="bookType">
<option value="">Select Book Type</option>
<?php
$query = 'SELECT booktype_id, booktype_label FROM booktype ORDER BY booktype_label ASC';
$smt = $db->prepare($query);
$smt->execute();
$smt->bind_result($booktype_id, $booktype_name);
while($smt->fetch()) {
echo '<option value="'. $booktype_id. '">'.
$booktype_name .
'</option>';
}
$smt->close();
?>
</select>
</td>
</tr>
<tr><td>Book Author:</td>
<td><select name="bookAuthor">
<option value="">Select Author</option>
<?php
$query = 'SELECT people_id, CONCAT(people_firstname, " ",people_lastname)
as "name" FROM people
WHERE people_isauthor=1 ORDER BY people_firstname ASC';
$smt = $db->prepare($query);
$smt->execute();
$smt->bind_result($people_id, $people_name);
while($smt->fetch()) {
echo '<option value="'. $people_id. '">'. $people_name . '</option>';
}
$smt->close();
?>
</select>
</td>
</tr>
<tr><td>Book Publisher:</td>
<td><select name="bookPublisher">
<option value="">Select Publisher</option>
<?php
$query = 'SELECT people_id, CONCAT(people_firstname, " ",people_lastname) as "name"
FROM people
WHERE people_ispublisher=1 ORDER BY people_firstname ASC';
$smt = $db->prepare($query);
$smt->execute();
$smt->bind_result($people_id, $people_name);
while($smt->fetch()) {
echo '<option value="'. $people_id. '">'. $people_name .
'</option>';
}
$smt->close();
?>
</select>
</td>
</tr>
<tr><td></td><td><input type="submit" name="Insert" value="Insert Book Record"/></td></tr>
<input type="hidden" name="submit_opt" value="add"/>
</table>
</form>
<?php include 'home.php';?>
</body>
</html>
<?php
$db->close();
?>
Explanation
After you have created the add.php page, you can run the page to see how it look like. But, please wait until you have also finished review.php page. When you submit the add.php form, it calls the commit.php to execute. Within the commit.php script on the add option, you can see that after you have finished insert book record into the book table, it will redirect you to the review.php page to display information about the recently added book. Now, you don't need table_list.php page, you can ignore it. However, you can not click on the Back to home link just yet, unless you have created the table_list.php page.
Display Result
Review Page
Review page is used to display the book and the book review information.
Code (review.php)
<?php
function gen_ratings($rating) {
$review_rating;
for($i=0; $i<$rating; $i++) {
$review_rating .= '<img src="star_rate.gif" alt="star"/>';
}
return $review_rating;
}
$db = new mysqli("localhost","root","caojiang","school");
if(mysqli_connect_errno ()){
printf("Connect failed: %s<br/>", mysqli_connect_error());
}
$id = $_REQUEST['id'];
$display = (!empty($_GET['display']) ? $_GET['display'] : 0);
// book detail
$table_book .="
<h3>Book Detail</h3>
<table cellspacing=\"4\" cellpadding=\"4\">
<tr>
<th>Book Name</a></th>
<th>Book Year</th>
</tr>
";
$query = 'SELECT book_name, book_year FROM book WHERE book_id=?';
$record = $db->prepare($query);
$record->bind_param('i', $id);
$record->execute();
$record->bind_result($book_name, $book_year);
while($record->fetch()) {
$table_book .= '<tr><td>'. $book_name .'</td><td>'. $book_year . '</td></tr>';
}
$table_book .= '</table><hr width="100%"/>';
$table .="
<h3>User Review</h3>
<table cellspacing=\"4\" cellpadding=\"4\">
<tr>
<th><a href=\"review.php?sort=date&id=$id\">Date</a></th>
<th><a href=\"review.php?sort=email&id=$id\">Email</a></th>
<th><a href=\"review.php?sort=comment&id=$id\">Comment</a></th>
<th><a href=\"review.php?sort=rating&id=$id\">Rating</a></th>
</tr>
";
$sort = $_REQUEST['sort'];
switch($sort) {
case 'date':
$order = ' ORDER BY book_review_date ASC';
break;
case 'email':
$order = ' ORDER BY book_reviewer_email ASC';
break;
case 'commnet':
$order = ' ORDER BY book_review_comment ASC';
break;
case 'rating':
$order = ' ORDER BY book_review_rating ASC';
break;
default:
$order = ' ORDER BY book_review_date DESC';
break;
}
$query = 'SELECT book_review_date,
book_reviewer_email,
book_review_comment,
book_review_rating
FROM book_reviews
WHERE book_review_id = ? '. $order ;
$stmt = $db->prepare($query);
$stmt->bind_param('i', $id);
$stmt->execute();
$stmt->bind_result($book_review_date,
$book_reviewer_email,
$book_review_comment,
$book_review_rating);
$count= 0;
while($stmt->fetch()) {
$rating_count += $book_review_rating;
$count++;
$rating = gen_ratings($book_review_rating);
$temp_table .="
<tr>
<td>$book_review_date</td>
<td>$book_reviewer_email</td>
<td>$book_review_comment</td>
<td>$rating</td>
</tr>";
}
if($rating) {
$table.= $temp_table;
}
else {
$table = "No Rating for this book!<br/>";
}
$table.='</table>';
echo $table_book;
echo $table;
require_once 'home.php';
?>
Display Result
Add A NEW Book Step 1
We add a new book by using add.php.
After Add A New Book Step 2
After adding a new book, the review.php page displays the currently added book.
Delete Page
- The delete.php page calls the commit.php script to execute the delete action.
- URL contained the submit_opt=delete and the book id.
- When user click on the Yes link, the commit.php script execute the delete action.
- If you want to get more detail about the delete option, you can visit the commit.php section.
Code (delete.php)
<b>Are you sure you want to delete?</b> <a href="commit.php?submit_opt=delete&id=<?php echo $_GET['id']; ?>">Yes</a> <a href="table_list.php?display=<?php echo $_GET['display'];?>">No</a>
Edit Page
- The book id send through the URL to the edit.php page.
- At the beginning of the edit.php page, we create a single query which get a book record from multiple table: book,people, and booktype tables.
- When the form in the edit.php submit, it will call the commit.php script.
- Within the commit.php script, you can see that the script execute the edit option.
- If you want to get additional detail, you can visit the commit.php section.
Code (edit.php)
<?php
$db = new mysqli("localhost","root","caojiang","school");
if(mysqli_connect_errno ()){
printf("Connect failed: %s<br/>", mysqli_connect_error());
}
$query = 'SELECT b.book_id,
b.book_name,
b.book_year,
b.book_type,
b.book_author,
b.book_publisher,
bt.booktype_label,
CONCAT(au.people_firstname," ",au.people_lastname)
AS "author_name",
CONCAT(pl.people_firstname," ",pl.people_lastname)
AS "publisher_name"
FROM (book b INNER JOIN booktype bt ON (b.book_type=bt.booktype_id))
INNER JOIN people au
ON (b.book_author=au.people_id)
INNER JOIN people pl
ON (b.book_publisher=pl.people_id)
WHERE b.book_id='.$_GET['id']. ' LIMIT 1' ;
$stmt = $db->prepare($query);
$stmt->execute();
$stmt->bind_result(
$book_id,
$book_name,
$book_year,
$book_type_label,
$book_author,
$book_publisher,
$book_label,
$book_authorname,
$book_publishername
);
$stmt->fetch();
$stmt->close();
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<h3>Edit your book</h3>
<form method="post" action="commit.php">
<table border="0">
<tr><td>Book Name:</td>
<td><input type="text" name="bookName" size="20" value="<?php echo $book_name; ?>"/></td></tr>
<tr><td>Year:</td>
<td><select name="bookYear">
<option value="<?echo $book_year; ?>" checked="checked">
<?echo $book_year; ?>
</option>
<?php
for($i=0; $i<=10; $i++) {
$year = intval(date('Y'))-$i;
echo '<option value="'.($year) .'">'
. ($year) .
'</option>';
}
?>
</select>
</td>
</tr>
<tr><td>Book Type:</td>
<td><select name="bookType">
<option value="<?php echo $book_type_label; ?>" checked="checked">
<?php echo $book_label; ?></option>
<?php
$query = 'SELECT booktype_id, booktype_label FROM booktype ORDER BY booktype_label ASC';
$smt = $db->prepare($query);
$smt->execute();
$smt->bind_result($booktype_id, $booktype_name);
while($smt->fetch()) {
echo '<option value="'. $booktype_id. '">'. $booktype_name . '</option>';
}
$smt->close();
?>
</select>
</td>
</tr>
<tr><td>Book Author:</td>
<td><select name="bookAuthor">
<option value="<?php echo $book_author; ?>" checked="checked">
<?php echo $book_authorname; ?></option>
<?php
$query = 'SELECT people_id, CONCAT(people_firstname, " ",people_lastname) as "name" FROM people
WHERE people_isauthor=1 ORDER BY people_firstname ASC';
$smt = $db->prepare($query);
$smt->execute();
$smt->bind_result($people_id, $people_name);
while($smt->fetch()) {
echo '<option value="'. $people_id. '">'. $people_name . '</option>';
}
$smt->close();
?>
</select>
</td>
</tr>
<tr><td>Book Publisher:</td>
<td><select name="bookPublisher">
<option value="<?php echo $book_publisher; ?>" checked="checked">
<?php echo $book_publishername; ?>
</option>
<?php
$query = 'SELECT people_id, CONCAT(people_firstname, " ",people_lastname) as "name" FROM people
WHERE people_ispublisher=1 ORDER BY people_firstname ASC';
$smt = $db->prepare($query);
$smt->execute();
$smt->bind_result($people_id, $people_name);
while($smt->fetch()) {
echo '<option value="'. $people_id. '">'. $people_name . '</option>';
}
$smt->close();
?>
</select>
</td>
</tr>
<tr><td></td><td><input type="submit" name="Insert" value="Update"/></td></tr>
<input type="hidden" name="submit_opt" value="edit"/>
<input type="hidden" name="id" value="<?php echo $_GET['id']; ?>"/>
</table>
</form>
<?php require_once 'home.php';?>
</body>
</html>
<?php
$db->close();
?>
Display Result
We try to edit a book.
Rating or Review Book
This rating.php page uses to submit review or rating a particular book.
Code (rating.php)
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<h3>Rating <?php echo $_GET['bookname'];?> book</h3>
<form method="post" action="commit.php?id=<?php echo $_GET['id']; ?>">
<table border="0">
<tr>
<td>Email Address:</td>
<td><input type="text" name="rating_email" size="20"/></td>
</tr>
<tr><td>Comments:</td>
<td><textarea name="comment" cols="40" rows="4"></textarea>
</td></tr>
<tr><td>Ratings:</td>
<td>
<input type="radio" name="review_rating" value="1"/>1
<input type="radio" name="review_rating" value="2"/>2
<input type="radio" name="review_rating" value="3"/>3
<input type="radio" name="review_rating" value="4"/>4
<input type="radio" name="review_rating" value="5"/>5
</td>
</tr>
<tr><td></td><td><input type="submit" name="rating" value="Submit"/></td></tr>
<input type="hidden" name="submit_opt" value="rating"/>
</table>
</form>
<?php require_once 'home.php';?>
</body>
</html>
Display Result
