Listings
1. bookdb.sql - to create the 'book' database. Source found on grace at:
/usr/users3/grovesr/web/books/bookdb.sql
drop table if exists book_customers; create table book_customers ( customerid int unsigned not null auto_increment primary key, name char(30) not null, address char(40) not null, city char(20) not null ); drop table if exists book_orders; create table book_orders ( orderid int unsigned not null auto_increment primary key, customerid int unsigned not null, amount float(6,2), date date not null ); drop table if exists book_books; create table book_books ( isbn char(13) not null primary key, author char(30), title char(60), price float(4,2) ); drop table if exists book_order_items; create table book_order_items ( orderid int unsigned not null, isbn char(13) not null, quantity tinyint unsigned, primary key (orderid, isbn) ); drop table if exists book_book_reviews; create table book_book_reviews ( isbn char(13) not null primary key, review text );
2. book_insert.sql - to insert a few records into the 'book' database. Source found on grace at: /usr/users3/grovesr/web/books/book_insert.sql
insert into book_customers values
(NULL, "Julie Smith", "25 Oak Street", "Airport West"),
(NULL, "Alan Wong", "1/47 Haines Avenue", "Box Hill"),
(NULL, "Michelle Arthur", "357 North Road", "Yarraville");
insert into book_orders values
(NULL, 3, 69.98, "02-Apr-2000"),
(NULL, 1, 49.99, "15-Apr-2000"),
(NULL, 2, 74.98, "19-Apr-2000"),
(NULL, 3, 24.99, "01-May-2000");
insert into book_books values
("0-672-31697-8", "Michael Morgan", "Java 2 for Professional Developers", 34.99),
("0-672-31745-1", "Thomas Down", "Installing Debian GNU/Linux", 24.99),
("0-672-31509-2", "Pruitt, et al.", "Teach Yourself GIMP in 24 Hours", 24.99),
("0-672-31769-9", "Thomas Schenk", "Caldera OpenLinux System Administration Unleashed", 49.99);
insert into book_order_items values
(1, "0-672-31697-8", 2),
(2, "0-672-31769-9", 1),
(3, "0-672-31769-9", 1),
(3, "0-672-31509-2", 1),
(4, "0-672-31745-1", 3);
insert into book_book_reviews values
("0-672-31697-8", "Morgan's book is clearly written and goes well beyond most of the basic Java books out there.");
3. search.html - The search screen for the 'book' database. Source found on grace at:
/usr/users3/grovesr/web/books/search.html
<html>
<head>
<title>Book-O-Rama Catalog Search</title>
</head>
<body>
<h1>Book-O-Rama Catalog Search</h1>
<form action="results.php" method="post">
Choose Search Type:<br>
<select name="searchtype">
<option value="author">Author
<option value="title">Title
<option value="isbn">ISBN
</select>
<br>
Enter Search Term:<br>
<input name="searchterm" type=text>
<br>
<input type=submit value="Search">
</form>
</body>
</html>
4. newbook.html - The insert screen for the 'book' database. Source found on grace at:
/usr/users3/grovesr/web/books/newbook.html
<html>
<head>
<title>Book-O-Rama - New Book Entry</title>
</head>
<body>
<h1>Book-O-Rama - New Book Entry</h1>
<form action="insert_book.php" method="post">
<table border=0>
<tr><td>ISBN</td><td><input type=text name=isbn maxlength=13 size=13><br></td></tr>
<tr><td>Author</td><td> <input type=text name=author maxlength=30 size=30><br></td></tr>
<tr><td>Title</td><td> <input type=text name=title maxlength=60 size=30><br></td></tr>
<tr><td>Price $</td><td><input type=text name=price maxlength=7 size=7><br></td></tr>
<tr><td colspan=2><input type=submit value="Register"></td></tr>
</table>
</form>
</body>
</html>
5. results.php - The PHP code to do a search on the database and return the results. Source on grace at: /usr/users3/grovesr/web/books/results.php
<html>
<head>
<title>Book-O-Rama Search Results</title>
</head>
<body>
<h1>Book-O-Rama Search Results</h1>
<?
if (!$searchtype || !$searchterm)
{
echo "You have not entered search details. Please go back and try again.";
exit;
}
$searchtype = addslashes($searchtype);
$searchterm = addslashes($searchterm);
//
// You will need to change the $database variable value to be your grace
// user ID
//
$database = "grovesr";
@ $db = mysql_connect("localhost", $database, "");
if (!$db)
{
echo "Error: Could not connect to database server. Please try again later.";
exit;
}
@ $ok = mysql_select_db($database);
if (!$ok)
{
echo "Error: Could not select database: $database. Please try again later.";
exit;
}
$query = "select * from book_books where ".$searchtype." like '%".$searchterm."%'";
$result = mysql_query($query);
$num_results = mysql_num_rows($result);
echo "<p>Number of books found: ".$num_results."</p>";
for ($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_array($result);
echo "<p><strong>".($i+1).". Title: ";
echo stripslashes($row["title"]);
echo "</strong><br>Author: ";
echo stripslashes($row["author"]);
echo "<br>ISBN: ";
echo stripslashes($row["isbn"]);
echo "<br>Price: ";
echo stripslashes($row["price"]);
echo "</p>";
}
?>
</body>
</html>
6. insert_book.php - The PHP code to insert new books into the 'book' database. Source on grace at: /usr/users3/grovesr/web/books/insert_book.php
<html>
<head>
<title>Book-O-Rama Book Entry Results</title>
</head>
<body>
<h1>Book-O-Rama Book Entry Results</h1>
<?
if (!$isbn || !$author || !$title || !$price)
{
echo "You have not entered all the required details.<br>"
."Please go back and try again.";
exit;
}
$isbn = addslashes($isbn);
$author = addslashes($author);
$title = addslashes($title);
$price = doubleval($price);
//
// You will need to change the value of the $database variable to your
// grace user ID
//
$database = "grovesr";
@ $db = mysql_connect("localhost", $database, "");
if (!$db)
{
echo "Error: Could not connect to database server. Please try again later.";
exit;
}
@ $ok = mysql_select_db($database);
if (!$ok)
{
echo "Error: Could not select database: $database. Please try again later.";
exit;
}
$query = "insert into book_books values ('".$isbn."', '".$author."', '".$title."', '".$price."')";
$result = mysql_query($query);
if ($result)
echo mysql_affected_rows()." book inserted into database.";
?>
</body>
</html>