Assignment 7 -- Bookstore Project: index, productPage, searchBrowse, and about pages
In this assignment you will create the first four pages of your on-line bookstore.
The sample site, GeekBooks, has the basic functionality of an online store with product information,
a shopping cart, and a checkout process. Your bookstore will have this functionality
at the completion of assignment 9. You may add additional enhancements to earn
a higher grade on the term project. Information about possible enhancements is located
under the "project' link on the course web site.
Start by creating a new folder named Bookstore for all of your project files. Since
you will have quite a few files I suggest that you create a subdirectory for images.
The data for the database is located in DatabaseDump.txt. Open phpMyAdmin and execute this SQL. This
will create the following five tables in your mySQL database:
index.php -- The home page should have a header
that includes a store name (your choice), logo (your design), and utility icons
(shopping cart and account info). Try to keep your header's height to 150px or less
so that it does not consume up too much valuable page space. The body of the page
will contain a search box, browse menu, and a few randomly selected items from the
database. You should also create a footer that contains a few relevant links. Somewhere
on your page include a link called "About this site" that links to a page that describes
the details of the site (for potential employers, visitors, etc.) For page
design ideas see Amazon.com, Walmart.com, bn.com, Crutchfield.com ThinkGeek.com
and other on-line stores.
You are not expected to create your own icons, although you may do so later as a
site enhancement. For now, find icons on the Internet that you like and use them.
Give credit to the originating site in your "about this site" page. You can use
the icons from the sample site, which came from Amazon.
DatabaseConnection.php - The database connection information is
needed on all pages of the site. Since information about the database could change
(location, database name, password, etc.) this information should be kept in only
one location. Create a file named "Connection.php and put the code for opening the
database in it (mysql_connect() and mysql_select_db()). Include this file in index.php
by putting the following include statement near the top of the page:
Note: the .php extension on the connection file hides the database connection information
from malicious users. The connection script executes (briefly) but produces no output to the browser. A file with an extension such as .txt or .html
would still work but its contents would be visible to clients.
Randomly selecting books: The body of the home page should display a few
randomly selected books from the 18 books in the database. The SQL to select
the books is very similar to RandomProduct.php in assignment 6.
Images -- The images are available in three sizes. To display an image, concatenate
the ISBN into the URL as follows:
where XXX is the book ISBN. The image should be hyperlinked to the product page as in the sample site.
There are three sizes of each image:
||default & search/browse pages
||Product page image enlarge.
Book Descriptions: The book descriptions can be quite long and on the default
page you will want to trim them to a shorter length. Use the function substr(string,
start, length). Enhancement idea: This method of trimming the descriptions can cut
words in half. A useful site enhancement would be to write a small function that
breaks the descriptions between words.
Search Box: Both the search box and the browse menus call a page named SearchBrowse.php.
The type of search the user wishes to conduct is passed in a querystring parameters
for search or "browse" for browse. An if statement in the SearchBrowse.php page
will check which parameter was passed and selects the appropriate SQL statement.
Browse Menu: The browse menu is dynamically generated, displaying only
those categories that have books in the database. The SQL statement requires a join
between the tables bookCategories and bookcategoriesbooks on CategoryID. It also
requires the "distinct" keyword to eliminate duplicates and a "sort by" parameter.
The hyperlinks in the browse menu point to SearchBrowse.php and pass the name of
the category that the user selected.
Include files: Once you get everything working on the home page (index.php)
cut out the elements that you want to use on other pages and paste them into include
files. This includes the header, footer, and search/browse menu.
Template enhancement: Using a template to eliminate duplicate HTML
& CSS is a project enhancement. If you elect to utilize a template this would
be good time to start. See site template for more information on implementing a template.
ProductPage.php: This page displays book details, such as the title,
authors, ISBN, price and full book description. It is very similar to index.php,
which would be a good template to use.
Listing books: Retrieve the book ISBN from the query string, clean it, and
concatenate it into a sql statement to retrieve the book title, description, price, and publisher.
Debugging Tip: It is a good idea to echo sql statements and other important
variables before attempting to use them. This is a quick way to spot errors.
Echo statements are free so use them
generously! They can be commented out or
deleted once the page is working. Syntax (including a label telling which variable is being displayed
and a break tag so that each variable gets its own line):
echo "sql: $sql <br />";
Book Image: Use the medium sized image to display the book. The
image should be hyperlinked to the large image.
Listing Authors:: Displaying items with a many-to-many relationship
(e.g. movies & actors) can be tricky. For instance, in Assignment 5 movie
with three actors returned three records. To display
this information on a web page we need a way of combining the names of all three
actors. Two possible ways of dealing with this issue are described below.
Easy way to list Authors: Using a function that takes each book's
ISBN and retrieves the author names. In theory this is slightly less efficient for the server since it
requires executing a separate SQL statement for each book to retrieve the author names. For instance,
a search that returns 10 books would require one query to retrieve
the book titles, etc. and 10 more to return the author names. I recommend
using the following ListAuthors() function.
The ListAuthors function takes in a book's ISBN and returns a list of its authors.
It is a good candidate for a function for two reasons:
- It is a distinct and separable piece of functionality that is nicely encapsulated
within a function.
- It will be used on several pages of the bookstore.
Copy the code for ListAuthors.php
and paste it into new a file named "ListAuthors.php." Use an include statement
to import it into your page. The List authors function requires
two input parameters: ISBN and a link to the database.
It returns a string of anchor tags with the names of the book's authors
(see the SearchBrowse.php page).
Use the ListAuthors function on all pages where you want to list authors. The location
of the include tag in your php page is not important, but it should not be inside
a loop. A good spot is at the top of the page near the database connection include.
When you want to list the authors for a book call the function from your page with
<?php echo fListAuthors($link, $isbn);?>
where $link is your database connection and $isbn is a variable containing the book's ISBN.
More elegant way to list authors: In theory it should be faster
to use GROUP BY and GROUP_CONCAT to group the records
and combine the author names into a single record. However a non-scientific comparison of list authors with
a ListAuthors function
found that group_concat is only a little faster. When the books are
sorted by title it is considerably slower.
If you chose to use group_concat to list authors the last exercise in
assignment 5 (DVDListingImproved) explains the syntax for GROUP BY and GROUP_CONCAT.
You will also need to use Distinct inside the group concat function to eliminate
duplicate authors. See the mySql
documentation for details.
SearchBrowse.php: This page displays the results of both searching
and browsing. The code is very similar to index.php, which would be a good template.
You may wish to code the browse feature first, since it is easier. Once you have
browse working properly, modify the page to accommodate searches.
The page needs to know if it is processing a search or a browse. It knows by looking
in the querystring for parameters named "search" and "browse" (look at the sample
store for an illustration of how this works).
There are only two differences in how we process a search verses a browse:
- Search and browse have different SQL statements
- We give the user a slightly different message for search verse browse (i.e. Search
Other than those two differences, the rest of page is identical regardless of whether
we are doing a search or browse. Use an "if" statement to check for search or browse
and use the appropriate SQL statement and/or message.
Product searches look for matching strings in category names, titles, descriptions,
publishes and author names. The SQL statement requires joins between all five tables
in the database. The SQL string for a searching, where $search is the query:
SELECT DISTINCT d.isbn, title, description, price
FROM bookauthors a, bookauthorsbooks ba, bookdescriptions d,
bookcategoriesbooks cb, bookcategories c
WHERE a.AuthorID = ba.AuthorID
AND ba.ISBN = d.ISBN
AND d.ISBN = cb.ISBN
AND c.CategoryID = cb.CategoryID
AND (CategoryName = '$search'
OR title LIKE '%$search%'
OR description LIKE '%$search%'
OR publisher LIKE '%$search%'
OR concat_ws(' ', nameF, nameL, nameF) LIKE '%$search%' )
ORDER BY title"
The user supplied query should cleaned using the mysqli_real_escape_string(). This function escapes single quotes (')
to prevent database errors and sql injection attacks.
The SQL for browsing by category returns all books from the bookcategoriesbooks
table with a matching CategoryID. It is a subset of the search SQL statement shown
Record Count: You should tell the user how many books were found.
The page should respond gracefully when a search does not return any books.
About.php: This page describes
the features of your site (so that potential employers don't miss anything). You
can cut & paste the content from the GeekBooks page and modify it with your name
and list of enhancements.
Congratulations: You have finished first section of your on-line
Submission instructions: Submit assignments via
the Canvas course management system.
Submit the full URL for each exercise in the assignment, listing the URLs
in the same order that they are listed in the assignment. To minimize typos in URLs
it is strongly recommended
that you copy the URLs from the address bar of the browser
rather than trying to type them. Incorrect URLs
will not be graded and no credit will be given.
When pages are connected via navigation (as in your Bookstore project) it is only
necessary to submit the URL of the first page.