In this article, we are going to create a simple, generic storefront, which we will later add to a shopping cart. There are many ways to implement this, depending on what you are selling and what experience you want the user to have. If you have never built an online store application, use this as a foundation to build more sophisticated online shopping carts.
- Display individual items
- Display item categories
- Create database to store item or product information
When you go into a real life store, all products are in some kind of order; shoes are not mixed with clothes, women's clothes are separate from men's clothes, etc. When applying that knowledge to database normalization, you easily conclude that you will need one table to hold categories and another table to hold individual items.
Date of Publication
From the list of attributes we can see three main tables emerging:
- ISBN Number
Now let's look at the relationships:
One author can write many books, and a book can only have one author (in most cases anyway). This creates a one-to-many relationship between the author and the books that he writes. Therefore, we need to add some information about the author in the books table. In this case, we are going to add the author's id into the books table. This will make it easy for us to identify and retrieve the author's details together with a list of books by that author, as and when needed.
One publisher can publish many books, but one book can only have one publisher (again, in most cases). This makes a one to many relationship between the publisher and the number of books that it publishes. We need to add information about the publisher into the books table. As with the author, we are just going to add the publisher's id to the books table. This will make it easy to identify and retrieve a list of books published by a particular publisher.
One publisher can publish books written by many authors, but one author can only have one publisher at a time. This establishes a link between the author and publisher. However, we are going to ignore this relationship, because it is adequately represented in the book table.
Please feel free to do further database normalization as you see fit. For the purposes of this article, this database is normalized enough.
From the above statements we can now construct the following three tables:
The books table has the following fields:
Id Â this field will keep track of the number of books in the store
authID/pubID Â will store the ID of the author and the ID of the publisher
title Â will store the title of the book
price ÂPrice of book
date_of_pub Â refers to the date of publication
book_img Â image of the book
genID Â stores the books genre
ISBN number - will store the ISBN number of the book
The author table will take the name and id of an author and the publisher table will take the name, address and id of the publisher. So let's create the actual tables:
The books table holds all the information about the actual book as well as the related author and publisher.
The publisher table will hold all the information about the publishing company, information such as the name of the company and address.
The author table stores information about the author. Please feel free to add as much information about the author as you want. I've only added the author's name.
The genres table will store a list of all genres, such as Comedy, Education, Romance, etc.
These are all the tables we need to run a basic storefront. Remember, we only want these to store and display our product at this stage. We will be creating a shopping cart later on, which will connect with this store. So let's insert some sample data into our tables.
Sample records for the genres table:
Sample records for the books table:
Sample records for the publisher table:
Sample records for the author table:
That's it for the database. This is probably the most difficult part of the project, when you consider that the next part to be about retrieving and displaying information from the database. The next thing we need to do is to create the front page of the site, where the user can select the type of books that they want. The front page will display a list of categories, for the user to choose from. Once the user has clicked on the category, a list of books for that category will be displayed. Then the user will be able to click on the individual books to view further details.
So create a new PHP document and add the following code:
The code on the page is self-explanatory; all that happens is that the PHP code on the top of the page retrieves a list of categories from the genres table and list them on the page. The category names are converted into a hyperlink that will enable us to select it and consequently view the books contained within that category. In the next section we are going to create the page that enables us to view a list of books in a given category. This page must display the names of the books, the price and the option to buy a book. In addition, we are also going to look at how to write a shopping cart mechanism.