Building an online Store

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.

Requirements:

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

Let's say we are selling books. A book will have the following attributes:

Author

Publisher

Title

Price

Date of Publication

Genre

ISBN number

From the list of attributes we can see three main tables emerging:

books

• Title
• Price
• Date-of-publication
• Genre
• ISBN Number

author

• name

publisher

• name
• address

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:

books

author

publisher

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:

mysql> create table books ( -> book_id int not null primary key auto_increment, -> authID int not null, -> pubID int not null, -> title varchar(200), -> price decimal(9,2), -> date_of_pub date, -> book_img varchar(50), -> ISBN varchar(7), -> genID int not null); Query OK, 0 rows affected (0.08 sec)

The books table holds all the information about the actual book as well as the related author and publisher.

mysql> create table publisher( -> pub_id int not null primary key auto_increment, -> pub_name varchar(50), -> pub_address text); Query OK, 0 rows affected (0.08 sec)

The publisher table will hold all the information about the publishing company, information such as the name of the company and address.

mysql> create table author( -> auth_id int not null primary key auto_increment, -> auth_name varchar(50), ->); Query OK, 0 rows affected (0.09 sec)

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.

CREATE TABLE `genres` ( `gen_id` int(4) NOT NULL auto_increment, `gen_name` varchar(100) NOT NULL default '', desc text, PRIMARY KEY (`gen_id`) )

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:

mysql> INSERT INTO `genres` VALUES (1, 'Get the latest Comedy books here ', 'Comedy'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `genres` VALUES (2, 'The latest and best novels in the Romantic genre! \n', 'Romance'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `genres` VALUES (3, 'Books for all Educational Disciplines ', 'Education'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `genres` VALUES (4, 'Get all the best thrillers here', 'Thriller'); Query OK, 1 row affected (0.00 sec)

Sample records for the books table:

mysql> insert into books values(0,'1','1','The Way','10.00','2006-10-10','book1.g if','1'); Query OK, 1 row affected (0.00 sec) mysql> insert into books values(0,'2','2','The Way North','10.50','2006-10-11','b ook2.gif','1'); Query OK, 1 row affected (0.00 sec) mysql> insert into books values(0,'3','3','The Way North and West','11.50','2006- 10-12','book3.gif','2'); Query OK, 1 row affected (0.00 sec)

Sample records for the publisher table:

mysql> insert into publisher values(0,'ABC Publishers','666 ABC Street') -> ; mysql> insert into publisher values(0,'Jane Doe Publisher','777 Doe Street'); Query OK, 1 row affected (0.00 sec) mysql> insert into publisher values(0,'Van der Hoeven Doe Publishers','111 van de Hoeven Street');

Sample records for the author table:

Query OK, 1 row affected (0.00 sec) mysql> insert into author values(0,'Mike Malone'); Query OK, 1 row affected (0.02 sec) mysql> insert into author values(0,'Jane Malone'); Query OK, 1 row affected (0.00 sec) mysql> insert into author values(0,'Chris Malone'); Query OK, 1 row affected (0.00 sec)

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:

<?php $db = mysql_connect("localhost","root","") or die("Failed to open connection to MySQL server."); mysql_select_db("books") or die("Unable to select database"); $query ="SELECT * from genres"; $results=mysql_query($query); if($results){ $num = mysql_num_rows($results); }else{ echo mysql_error(); } ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "https://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="https://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <title>Book Store</title> </head> <body> <table width="100%" border="0"> <tr> <td><h1>Book Categories</h1> </td> </tr> <?php if($num>0){ while($row=mysql_fetch_assoc($results)){ ?> <tr> <td> </td> </tr> <tr> <td bgcolor="#CCCCCC"><a href="list.php?catid=<?php echo $row['gen_id'];?>"><?php echo $row['gen_name'];?></a></td> </tr> <tr> <td><?php echo $row['desc']; ?> </td> </tr> <?php } }else{ ?> <tr> <td>No categories available.</td> </tr> <? } ?> </table> </body> </html>

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.

Find a programming school near you

Zip Code:

Subject: - Select All Subjects - Computer Applications Computer Engineering Computer Science Computer Prog. - Software Dev. Computer Game Programming Web Design Web Development Internet Systems Internet Certification Database Design and Development Information Systems Management Information Technology Project Management Network Design - Development Systems Analyst

Degree: - Select All Degrees - Associate's Bachelor's Master's Doctoral Certificates Diplomas Coursework MBA

Online Campus Both