Assignment 7 -- Bookstore Project: index.php, productPage.php, searchBrowse.php and, about.php

In this assignment you will create the first four pages of your on-line bookstore. The bookstore can be an impressive project to add to your on-line portfolio.

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). 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, Wal-Mart.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.

There are three sizes of each image:

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 variable named "search" for search or "browse" for browse. An if statement in the SearchBrowse.php page will check which variable was passed then use 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.

Server-side Includes -- Once you get everything working on the home page (index.php) cut out the parts that you want to use on other pages and paste them into server side includes. This includes the header, footer, and search/browse menu.

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.

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) is rather tricky. Such relationships are described using three separate database tables and an inner join between the three tables returns a separate record for each match. For instance, in Assignment 5 the relationship between movies and actors was described used with three tables (movies, actors and movieactors). A 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.

Authors approach 1: Using a function that retrieves the author names by ISBN is the easier approach to code. It is less efficient for the server since it requires executing a separate SQL statement to retrieve the author names for each book.

Below is the code for a function that accepts an ISBN and returns a list of authors.  Retrieving author names 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 module.  It requires only one input parameter: book ISBN.
2. It will be reused many times, once for each book displayed. It will also be used on several php pages, which makes it a good candidate for inclusion within an include file

Copy the code for ListAuthors.php and paste it into new a file named "ListAuthors.php." This function requires one input parameter, ISBN, and returns a string containing a list of the book's authors (listed in hyperlinks that point to 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($isbn);?> 

where $isbn is a local variable containing the ISBN.

Authors approach 2: This approach is considered an enhancement to your project.  It uses a subquery, GROUP BY and GROUP_CONCAT to group the records and combine the actor names into a single record.  This approach is more efficient for the server since it requires executing only one SQL statement to retrieve book and author information. However the syntax for the SQL statement is more complicated.

Tips for approach 2: This approach requires the use of a subquery (see Lesson 11 in Forta's SQL in 10 Minutes). The subquery is similar to the search SQL statement provided below except that it returns only the ISBN field for matching books. The results of this subquery are used as in input to the outer query (using an IN operator) to retrieve authors and descriptions. The outer query uses GROUP BY and GROUP_CONCAT to list the book information and authors. The last problem in assignment 5 explains the syntax for GROUP BY and GROUP_CONCAT.  

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 bookdescriptions.isbn, title, description, price
FROM bookauthors, bookauthorsbooks, bookdescriptions, bookcategoriesbooks,bookcategories
WHERE bookauthors.AuthorID = bookauthorsbooks.AuthorID
AND bookauthorsbooks.ISBN = bookdescriptions.ISBN
AND bookdescriptions.ISBN = bookcategoriesbooks.ISBN
AND bookcategories.CategoryID = bookcategoriesbooks.CategoryID
AND (CategoryName = '$search'
OR title LIKE '%$search%'
OR description LIKE '%$search%'
OR publisher LIKE '%$search%'
OR concat_ws(' ', strFName, strLName, strFName) LIKE '%$search%' )
ORDER BY title

The user supplied query should be cleaned to replace single quotes (') with two single quotes to 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 as needed.

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