Syllabus Schedule Project Labs Canvas Server Access phpMyAdmin Assignment 1 Assignment 2 Assignment 3 Assignment 4 Assignment 5 Assignment 6 Assignment 7 Assignment 8 Assignment 9

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:

ER Diagram

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:

include_once("Connection.php")

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:

/sandvig/mis314/assignments/bookstore/bookimages/XXX.01.THUMBZZZ.jpg

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:

Size Extension Page
thumb .01.THUMBZZZ.jpg default & search/browse pages
medium .01.MZZZZZZZ.jpg Product page
large .01.LZZZZZZZ.jpg 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 named "search" 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:

  1. It is a distinct and separable piece of functionality that is nicely encapsulated within a function.
  2. 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 the syntax:

<?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 versus using group_concat (results) 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:

  1. Search and browse have different SQL statements
  2. We give the user a slightly different message for search verse browse (i.e. Search results: ..)

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 above.

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.

php humor

Congratulations: You have finished first section of your on-line bookstore!


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.

Regular Expressions HTML Color Names Color Picker ASCII Character Codes Unicode Character Codes