PHP MySQL mysqli for Book Record Management System using Object-Oriented Concept
From w3cyberlearnings
Requirements
- Required install and configure: PHP, MySQL, and Apache Web Server
- Understand the basic of object-oriented programming concepts in PHP in order to work with this example.
- This example is based on the PHP MySQL mysqli for Book Record Management System Example, but used Object Oriented Programming concepts
Goal
- Our goal here is to show you on how to use object-oriented programming concept in PHP to develop the web based application.
- If you are not familiar with object-oriented programming concept, you can start with this PHP Object Oriented first.
Tables
- You need to create these tables before you continue to develop the PHP programs.
book table
CREATE TABLE book ( book_id int(11) NOT NULL auto_increment, book_name varchar(50) NOT NULL, book_type smallint(5) unsigned NOT NULL, book_year smallint(5) unsigned NOT NULL, book_author int(10) unsigned NOT NULL, book_publisher int(10) unsigned NOT NULL, PRIMARY KEY (book_id) )
booktype table
CREATE TABLE booktype ( booktype_id int(10) unsigned NOT NULL auto_increment, booktype_label varchar(100) NOT NULL, PRIMARY KEY (booktype_id) )
book_reviews table
CREATE TABLE book_reviews ( book_review_id int(11) 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
CREATE TABLE people ( people_id int(10) unsigned NOT NULL auto_increment, people_firstname varchar(255) NOT NULL, people_lastname varchar(255) NOT NULL, people_isauthor tinyint(1) unsigned NOT NULL default '0', people_ispublisher tinyint(1) unsigned NOT NULL default '0', PRIMARY KEY (people_id) )
Insert Records
You need to insert these records for testing purpose.
Insert Record to the people table
We insert records for the author and publishers.
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
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
INSERT INTO book VALUES (1,'Java',1,2011,3,2),
(2,'PHP',1,2011,3,3),
(3,'MySQL',1,2004,4,1),
(4,'Linux',1,2007,4,7),
(5,'Java2EE',1,2002,2,3),
(6,'Gate',7,2010,3,2),
(7,'MySQL and PHP',1,2011,4,2),
(8,'Perl',1,2010,2,4);
Insert Record to the book_reviews table
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), (5,'2011-12-23','[email protected]','good old book',3), (6,'2011-12-24','[email protected]','I love this book ',3);
Page Structures
- These are the pages and directory structure for this project
- You can start to create all the directory and empty PHP pages
- Make sure the project directories and PHP files are readable by your web server
mybookrecord/class/DBi.php mybookrecord/class/author.php mybookrecord/class/book.php mybookrecord/class/booktype.php mybookrecord/class/function.php mybookrecord/class/people.php mybookrecord/class/publisher.php mybookrecord/class/rating.php mybookrecord/add.php mybookrecord/book_list.php mybookrecord/commit.php mybookrecord/delete.php mybookrecord/edit.php mybookrecord/function_add.php mybookrecord/home.php mybookrecord/rating.php mybookrecord/review.php mybookrecord/star_rate.gif
Demo
Home Page (book_list.php)
- This is the project home page
- From home page, you can navigate to other pages
- You can edit, delete, and add
- Home page set as a pagination where you can go back or forward to the book record
Add Page (add.php)
- Add a book record
- Provide book information
Edit Page (edit.php)
- Edit a book record is very similar to add a book record
- Edit a book, you need to query the table and fill in all the form fields.
Delete Page (delete.php)
Review Page (review.php)
Rating Page (rating.php)
mybookrecord/class/DBi.php
I defined a singleton class to make connection to the MySQL database.
<?php
/*
* @copyright 2011 Sophal Chao & w3cyberlearnings.com in respect to modifications of standard topics format.
* @link http://www.w3cyberlearnings.com
* @license http://creativecommons.org/licenses/by-sa/3.0/ Creative Commons Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0)
* @author sophal
* @date: 06/04/2011
* @description singleton class
* @usage: $conn = DB::pdo_connection()
*
* This code is as IT IS without warranty of any kind.
*/
define('HOST','localhost');
define('USER','root');
define('PASS','caojiang');
define('DBNAME','school');
class DBi {
private static $_instance;
public function &mysqli_con(){
if(!self::$_instance){
self::$_instance = new mysqli(HOST,USER,PASS,DBNAME);
}
return self::$_instance;
}
private function __construct() {
}
private function __clone() {}
}
?>
Explanation
- You need to change the database connection to meet your setting.
- This class is a singleton class, and this class uses for connection to the MySQL database.
- You can reuse this class to another project
mybookrecord/class/author.php
- This author class uses to get all the people who is an author.
- The author class is inherited from DBi class and author class has the constructor class which is initialized the MySQL connection.
- In addition, the get_authors() class fetches/queries the people table who has the author role.
<?php
/*
* @copyright 2011 Sophal Chao & w3cyberlearnings.com in respect to modifications of standard topics format.
* @link http://www.w3cyberlearnings.com
* @license http://creativecommons.org/licenses/by-sa/3.0/ Creative Commons Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0)
* @author sophal
* @date: 06/04/2011
*
* This code is as IT IS without warranty of any kind.
*/
require_once 'DBi.php';
class author extends DBi {
// you can eliminate these class variables
public $id, $first, $last, $is_author;
// defined a private variable for connection
private $mysqli;
private $query;
public function __construct() {
$this->mysqli = $this->mysqli_con();
}
public function get_authors() {
$this->query = 'SELECT
people_id,
CONCAT(people_firstname, " ",people_lastname) as "name"
FROM people
WHERE people_isauthor=1';
$result = $this->mysqli->query($this->query);
$data = array();
while ($rows = $result->fetch_assoc()) {
$data[] = $rows;
}
if ($data) {
return $data;
} else {
return false;
}
$result->close();
}
}
?>
mybookrecord/class/book.php
This is the book class uses to work with the book record.
<?php
/*
* @copyright 2011 Sophal Chao & w3cyberlearnings.com in respect to modifications of standard topics format.
* @link http://www.w3cyberlearnings.com
* @license http://creativecommons.org/licenses/by-sa/3.0/ Creative Commons Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0)
* @author sophal
* @date: 06/04/2011
* @description book
* This code is as IT IS without warranty of any kind.
*/
require_once 'DBi.php';
class book extends DBi {
//class variables
public $id,
$name,
$type,
$year,
$author,
$publisher;
private $mysqli;
private $query;
// class construction
// initialize the database connection
public function __construct() {
$this->mysqli = $this->mysqli_con();
}
// this method uses to insert record to the book table
// array can be used as the class argument
// or we can use the class public variables
public function insert($data="") {
$this->query = "INSERT INTO book(book_name, book_type,book_year,book_author,book_publisher)
VALUES(?,?,?,?,?)";
if(isset($data) && count($data) > 0 && !empty($data)) {
$this->name = $data['book_name'];
$this->type = $data['book_type'];
$this->year = $data['book_year'];
$this->author = $data['book_author'];
$this->publisher = $data['book_publisher'];
}
$result = $this->mysqli->prepare($this->query);
$result->bind_param("siiii", $this->name, $this->type, $this->year, $this->author, $this->publisher);
$result->execute();
$id = $result->insert_id;
if($id) {
return ($id);
}
else {
return false;
}
$result->close();
}
// this class method is the update method
// user can pass the array as its argument
// or use the class public variables
public function update($data="") {
// assign the id value
$id = ((isset($data['id']) && !empty($data['id'])) ? $data['id'] : $this->id);
// create the update query from the array
if(isset($data) && count($data) > 0 && !empty($data)) {
foreach ($data as $k=>$v) {
if (in_array($k, $key_col)) {
$set_query .= ( empty($set_query) ? " SET " : " , " );
$value = is_int($v) ? $v : '"'. $v . '"';
$set_query .= $k .'='. $value ;
}
}
$set_query .= " WHERE book_id= ?";
}
// create the update query from the class property
elseif( !empty($this->name)
&& !empty($this->type)
&& !empty($this->year)
&& !empty($this->author)
&& !empty($this->publisher)
&& !empty($this->id)) {
$set_query ='SET book_name="'. $this->name . '" ,' .
"book_type= ". $this->type . ' ,' .
"book_year= ". $this->year . ' ,' .
"book_author=". $this->author . ' ,'.
"book_publisher=". $this->publisher ;
$set_query .= " WHERE book_id= ?";
}
else {
return false;
}
if($set_query) {
$this->query = 'UPDATE book '. $set_query;
}
if($this->query) {
$result = $this->mysqli->prepare($this->query);
$result->bind_param("i", $id);
$result->execute();
return $result->affected_rows;
}
else {
return false;
}
$result->close();
}
// this is the delete method
// user can pass the $id value as the argument of use the public variable
public function delete($id="") {
$this->id = (isset($id) && !empty($id)) ? $id : $this->id;
if($this->id) {
$this->query="DELETE FROM book WHERE book_id= ?";
$result= $this->mysqli->prepare($this->query);
$result->bind_param('i', $this->id);
$result->execute();
return $result->affected_rows;
}
else {
return false;
}
$result->close();
}
// this method get the total row in the book table
public function get_rows() {
$this->query= "SELECT COUNT(*) FROM book";
$result = $this->mysqli->prepare($this->query);
$result->execute();
$result->bind_result($total_rows);
$result->fetch();
if($total_rows) {
return $total_rows;
}
else {
return false;
}
$result->close();
}
// this method use to get single book record
public function get_book($id) {
$this->id = (isset($id) && !empty($id)) ? $id : $this->id;
$this->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) '
. ' WHERE b.book_id='. $this->id
. ' ORDER BY b.book_name ASC LIMIT 1';
$result = $this->mysqli->query($this->query);
$rows = $result->fetch_assoc();
if($rows) {
return $rows;
}
else {
return false;
}
$result->close();
}
// this method use to get multiple book records, and it works like a paging.
public function get_books($start=5, $end=5) {
$this->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 '. $start. ','. $end;
$result = $this->mysqli->query($this->query);
$data = array() ;
while($rows = $result->fetch_assoc()){
$data[]=$rows;
}
if($data) {
return $data;
}
else {
return false;
}
$result->close();
}
}
?>
mybookrecord/class/booktype.php
This booktype class uses for the booktype table.
<?php
/*
* @copyright 2011 Sophal Chao & w3cyberlearnings.com in respect to modifications of standard topics format.
* @link http://www.w3cyberlearnings.com
* @license http://creativecommons.org/licenses/by-sa/3.0/ Creative Commons Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0)
* @author sophal
* @date: 06/04/2011
* @description booktype
* This code is as IT IS without warranty of any kind.
*/
require_once 'DBi.php';
class booktype extends DBi {
// these variables will not be used yet. You can eliminate them
public $booktype_id, $booktype_label;
private $mysqli;
private $query;
public function __construct() {
$this->mysqli = $this->mysqli_con();
}
public function get_booktypes() {
$this->query = 'SELECT
booktype_label,booktype_id FROM
booktype';
$result = $this->mysqli->query($this->query);
$data = array();
while ($rows = $result->fetch_assoc()) {
$data[] = $rows;
}
if ($data) {
return $data;
} else {
return false;
}
$result->close();
}
}
?>
mybookrecord/class/function.php
This script includes two functions use to generate the index link paging gen_list($number_rows), and use to generate the rating star gen_rating($rating).
- The first function uses to generate a pagination according to the total of the records on the table.
- The second function uses to generate rate stars.
<?php
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="book_list.php?display='. $n. '">'. ($i+1). '</a>';
}
return $title;
}
function gen_ratings($rating) {
$review_rating;
for($i=0; $i<$rating; $i++) {
$review_rating .= '<img src="star_rate.gif" alt="star"/>';
}
return $review_rating;
}
?>
mybookrecord/class/publisher.php
This class is similar to the author class. It uses the same database table to retrieve the data value but with slightly different SQL query.
<?php
/*
* @copyright 2011 Sophal Chao & w3cyberlearnings.com in respect to modifications of standard topics format.
* @link http://www.w3cyberlearnings.com
* @license http://creativecommons.org/licenses/by-sa/3.0/ Creative Commons Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0)
* @author sophal
* @date: 06/04/2011
* @description publisher
* This code is as IT IS without warranty of any kind.
*/
require_once 'DBi.php';
class publisher extends DBi {
// this is just declared, you can ignore this from now
public $id, $first, $last, $is_publisher;
// These private variable use for the MySQL connection
private $mysqli;
private $query;
public function __construct() {
$this->mysqli = $this->mysqli_con();
}
public function get_publishers() {
// publisher query required the where clause with people_ispublisher=1
$this->query = 'SELECT
people_id,
CONCAT(people_firstname, " ",people_lastname) as "name"
FROM people
WHERE people_ispublisher=1';
$result = $this->mysqli->query($this->query);
$data = array();
while ($rows = $result->fetch_assoc()) {
$data[] = $rows;
}
if ($data) {
return $data;
} else {
return false;
}
$result->close();
}
}
?>
mybookrecord/class/rating.php
This rating class has included many methods, but we are not going to use all the methods. In here, we only use the insert() and the get_rating() methods. The main purpose of this class is used to insert user review and to retrieve users review on a particular book.
<?php
/*
* @copyright 2011 Sophal Chao & w3cyberlearnings.com in respect to modifications of standard topics format.
* @link http://www.w3cyberlearnings.com
* @license http://creativecommons.org/licenses/by-sa/3.0/ Creative Commons Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0)
* @author sophal
* @date: 06/04/2011
* @description rating
* This code is as IT IS without warranty of any kind.
*/
require_once 'DBi.php';
class rating extends DBi {
public $book_review_id,
$book_review_date,
$book_reviewer_email,
$book_review_comment,
$book_review_rating;
private $connect;
private $query;
public function __construct() {
$this->connect = $this->mysqli_con();
}
public function edit($data="") {
if(count($data) > 0 && !empty($data) && isset($data)) {
$this->book_review_id = $data['id'];
$this->book_review_comment = $data['comment'];
$this->book_review_rating = $data['rating'];
$this->book_reviewer_email = $data['email'];
}
$this->query = "UPDATE book_reviews SET
book_review_id = ?,
book_review_date = now(),
book_reviewer_email = ?,
book_review_comment = ?,
book_review_rating = ?
WHERE book_review_id = ?";
$record = $this->connect->prepare($this->query);
$record->bind_param("issii", $this->book_review_id,
$this->book_reviewer_email,
$this->book_review_comment,
$this->book_review_rating,
$this->book_review_id);
$record->execute();
$row_num = $result->affected_rows;
if($row_num) {
return $row_num;
}
else {
return false;
}
$record->close();
}
public function insert($data="") {
if(count($data) > 0 && !empty($data) && isset($data)) {
$this->book_review_id = $data['id'];
$this->book_review_comment = $data['comment'];
$this->book_review_rating = $data['rating'];
$this->book_reviewer_email = $data['email'];
}
$this->query = "INSERT book_reviews (
book_review_date,
book_review_id,
book_reviewer_email,
book_review_comment,
book_review_rating )
VALUES(now(),?,?,?,?)";
$record = $this->connect->prepare($this->query);
$record->bind_param("issi", $this->book_review_id,
$this->book_reviewer_email,
$this->book_review_comment,
$this->book_review_rating
);
$record->execute();
$row_id = $record->affected_rows;
if($row_id) {
return $this->book_review_id;
}
else {
return false;
}
$record->close();
}
public function get_rating($id, $sort='date') {
if(!empty($id) && isset($id)) {
$this->book_review_id = $id;
}
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;
}
if($this->book_review_id) {
$this->query = "SELECT
book_review_id,
book_review_date,
book_reviewer_email,
book_review_comment,
book_review_rating
FROM book_reviews
WHERE book_review_id=". $this->book_review_id
. $order;
$record = $this->connect->query($this->query);
$data = array() ;
while($rows = $record->fetch_assoc()){
$data[]=$rows;
}
if($data) {
return $data;
}
else {
return false;
}
$result->close();
}
else {
return false;
}
}
public function delete($id="", $email="") {
$insert_query_binding=0;
if(!empty($id) && isset($id) && !empty($email) && isset($email)) {
$this->book_review_id = $id;
$this->book_reviewer_email = $email;
$this->query="DELETE FROM book_reviews WHERE book_review_id= ? AND book_reviewer_email= ?";
$insert_query_binding = 2;
}
if(!empty($id) && isset($id)) {
$this->book_review_id = $id;
$this->query="DELETE FROM book_reviews WHERE book_review_id= ?";
$insert_query_binding = 1;
}
if($this->query) {
$record= $this->connect->prepare($this->query);
if($insert_query_binding==2) {
$record->bind_param('is', $this->book_review_id, $this->book_reviewer_email);
}
if($insert_query_binding==1) {
$record->bind_param('i', $this->book_review_id);
}
$record->execute();
$row_number = $record->affected_rows;
if($row_number) {
return $row_number;
}
else {
return false;
}
}
$result->close();
}
}
?>
mybookrecord/add.php
We need to include the booktype, author, and publisher classes for this page. And, this page we included the hidden field for submit_opt which uses for select option in the commit.php script. In addition, in this script we generate four drop down lists.
<?php
require_once 'class/booktype.php';
require_once 'class/author.php';
require_once 'class/publisher.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>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
$booktype_c = new booktype();
$aa_booktypes = $booktype_c->get_booktypes();
foreach($aa_booktypes as $aa_booktype)
{
echo '<option value="'. $aa_booktype['booktype_id']. '">'. $aa_booktype['booktype_label'] . '</option>';
}
?>
</select>
</td>
</tr>
<tr><td>Book Author:</td>
<td><select name="bookAuthor">
<option value="">Select Author</option>
<?php
$authors = new author();
$aa_authors = $authors->get_authors();
foreach($aa_authors as $aa_author)
{
echo '<option value="'. $aa_author['people_id']. '">'. $aa_author['name'] . '</option>';
}
?>
</select>
</td>
</tr>
<tr><td>Book Publisher:</td>
<td><select name="bookPublisher">
<option value="">Select Publisher</option>
<?php
$publisher = new publisher();
$aa_publishers = $publisher->get_publishers();
foreach($aa_publishers as $aa_publisher)
{
echo '<option value="'. $aa_publisher['people_id']. '">'. $aa_publisher['name'] . '</option>';
}
?>
</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>
mybookrecord/book_list.php
This is like a home for this book record management system, the book_list.php lists all the books on the book table. From this page, you can edit, delete, or review the book. In addition, you also can add a new book as well.
<?php
require_once 'class/book.php';
require_once 'class/function.php';
$display = (!empty($_GET['display']) ? abs($_GET['display']) : 0);
//we create a book object
$book = new book();
$number_rows = $book->get_rows();
$book_record = $book->get_books($display, 5);
$count_book = 0;
?>
<div class="main">
<h3>Book List:</h3>
<?php
if ($number_rows <= 5) {
echo "";
} else {
echo gen_list($number_rows);
}
?>
<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>
<?php
if ($book_record) {
foreach ($book_record as $rec) {
$count_book++;
$sbook_name = substr($rec['book_name'], 0, 10);
echo "
<tr>
<td><a href=\"review.php?id=$rec[book_id]&display=$display\">$sbook_name</a></td>
<td>$rec[book_year] </td>
<td>$rec[booktype_label]</td>
<td>$rec[author_name]</td>
<td>$rec[publisher_name]</td>
<td>
<a href=\"rating.php?id=$rec[book_id]&display=$display\">[rating]</a>
<a href=\"delete.php?id=$rec[book_id]&display=$display\">[delete]</a>
<a href=\"edit.php?id=$rec[book_id]&display=$display\">[edit]</a>
</td>
</tr>
";
}
}
?>
</table>
<?php echo $count_book; ?> Book(s)
<?php include 'function_add.php'; ?>
</div>
mybookrecord/commit.php
This script is very similar to the tutorial of the Book Record Management System which does not use the object-oriented concept. As you can see, this script will need to include a few classes from the class directory. User requires to pass to this script with the submit_opt and the book id, and many other $_POST values. We create the book object and the rating object from the classes. As you know, this commit.php script is the heart of this system function. You insert, delete, edit/update, and review the book record you need this script. This script calls different option according to the submit_opt value pass to it.
<?php
require_once 'class/book.php';
require_once 'class/rating.php';
$option = $_REQUEST['submit_opt'];
$id = $_REQUEST['id'];
$display = $_REQUEST['display'];
$book = new book();
$rating_reviews = new rating();
// 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)) {
$book->name = $book_name;
$book->type = $book_type;
$book->year = $book_year;
$book->author = $book_author;
$book->publisher = $book_publisher;
$inser_id = $book->insert();
header('Location: review.php?id='.$inser_id);
}
else {
header('Location: add.php');
}
break;
case 'rating':
if(!empty($email) && !empty($comment) && !empty($rating)) {
$rating_reviews->book_review_id = $id;
$rating_reviews->book_review_rating = $rating;
$rating_reviews->book_reviewer_email = $email;
$rating_reviews->book_review_comment = $comment;
$rating_reviews->insert();
header('Location: review.php?id='.$id. '&display='. $display);
}
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)) {
$book->id = $id;
$book->name = $book_name;
$book->type = $book_type;
$book->year = $book_year;
$book->publisher = $book_publisher;
$book->author = $book_author;
$book->update();
header('Location: review.php?id='.$id. '&display='. $display);
}
else {
header('Location: edit.php?id='. $id. '&display='. $display);
}
break;
case 'delete':
if($id) {
if($book->delete($id)) {
$rating_reviews->delete($id);
header('Location: book_list.php');
}
}
else {
header('Location: review.php?id='. $id);
}
break;
}
include 'home.php';
?>
mybookrecord/delete.php
This is the delete.php page, and this page uses the commit.php script to perform its action.
<b>Are you sure you want to delete?</b> <a href="commit.php?submit_opt=delete&email=<?php echo $_GET['email']; ?>&id=<?php echo $_GET['id']; ?>">Yes</a> <a href="book_list.php?display=<?php echo $_GET['display'];?>">No</a>
mybookrecord/edit.php Firstly the edit.php needs to get the current book first, as you can see it queries multiple tables in order to get the book information. Next, we generate the HTML form with the default value of the book. User can select a different book attributes to make update in the form and finally to save the update by clicking on the update button.
<?php
require_once 'class/booktype.php';
require_once 'class/author.php';
require_once 'class/publisher.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?id=<?php echo $_GET['id'];?>&display=<?php echo $_GET['display'];?>">
<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
$booktype_c = new booktype();
$aa_booktypes = $booktype_c->get_booktypes();
foreach($aa_booktypes as $aa_booktype)
{
echo '<option value="'. $aa_booktype['booktype_id']. '">'. $aa_booktype['booktype_label'] . '</option>';
}
?>
</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
$authors = new author();
$aa_authors = $authors->get_authors();
foreach($aa_authors as $aa_author)
{
echo '<option value="'. $aa_author['people_id']. '">'. $aa_author['name'] . '</option>';
}
?>
</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
$publisher = new publisher();
$aa_publishers = $publisher->get_publishers();
foreach($aa_publishers as $aa_publisher)
{
echo '<option value="'. $aa_publisher['people_id']. '">'. $aa_publisher['name'] . '</option>';
}
?>
</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 include 'home.php';?>
</body>
</html>
<?php
$db->close();
?>
mybookrecord/function_add.php
This is 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>
mybookrecord/home.php
This is not a home page, this just the link to the home page only. You need this to be included in most of your script to link back to the home page.
<a href="book_list.php?display=<?php echo $_REQUEST['display']; ?>">Back to home</a>
This is just a HTML form uses to collect the user review detail. When you submit this form, it will send to the commit.php script to process. We have passed the book id when we submit this form with the hidden field called submit_opt and its assigned value rating.
mybookrecord/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 This Book</h3>
<form method="post" action="commit.php?id=<?php echo $_GET['id'];?>&display=<?php echo $_GET['display'];?>">
<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 include 'home.php';?>
</body>
</html>
mybookrecord/review.php
When you click on the book title/book name, you will get the book detail which includes how many people reviewed on the book. If the book has people review it, you can see a list of reviewers. You can filter reviewers based on Date, Email,Comment, or Rating.
<?php
require_once 'class/book.php';
require_once 'class/function.php';
require_once 'class/rating.php';
// display value uses for the book_list.php, it uses to generate the index link
$display = (!empty($_GET['display']) ? abs($_GET['display']) : 0);
// book
$book = new book();
$number_rows = $book->get_rows();
$rec = $book->get_book($_GET['id']);
// book rating or review
$rating_review = new rating();
$rating_records = $rating_review->get_rating($_GET['id'],$_GET['sort']);
$id = $_GET['id'];
?>
<div class="main">
<h3>Book Detail:</h3>
<table border="0" cellspacing="5" cellpadding="5">
<tr>
<th>Book Name</th>
<th>Book Year</th>
<th>Book Type</th>
<th>Book Author</th>
<th>Book Publisher</th>
</tr>
<tr>
<td><?php echo $rec[book_name]; ?></td>
<td><?php echo $rec[book_year]; ?></td>
<td><?php echo $rec[booktype_label]; ?></td>
<td><?php echo $rec[author_name]; ?></td>
<td><?php echo $rec[publisher_name]; ?></td>
</tr>
</table>
<hr width="100%"/>
<h3>User Review</h3>
<?php
if($rating_records) {
echo <<<HTMLH
<table cellspacing="5" cellpadding="5">
<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>
HTMLH;
foreach($rating_records as $rate) {
$count++;
$rating = gen_ratings( $rate['book_review_rating']); // we make star for rating
echo '<tr><td>'
. $rate['book_review_date'] . '</td><td>'
. $rate['book_reviewer_email']. '</td><td>'
. $rate['book_review_comment'] . '</td><td>'
. $rating .'</td></tr>';
}
echo '</table>';
}
else {
echo "No Rating for this book!<br/>";
}
include 'home.php';
?>
</div>
mybookrecord/star_rate.gif
You need to download this star and change the name to star_rate.gif, and store at /mybookrecord/star_rate.gif.
