The online shopping cart application allows an end user to search for a specific book in a database, place an order for the book, and purchase the book online. This application contains two sections, Admin and Client. The Admin section lets you administer the application by managing information related to the books, such as creating and removing a book category, adding a book to a category, and modifying the book information. The Client section lets the end user perform online transactions, such as searching for books based on either author or book title, and buying books.
This chapter discusses the architecture and database structure of the online shopping cart application. This chapter shows how to create the shopping cart application using PHP as the scripting language and MySQL as the database. This chapter discusses the process of administering the data related to books, such as adding and removing a new category, adding and removing a book from the database, viewing the book information, and placing an order to purchase the selected books. The chapter also describes the process of performing online transactions, such as buying a book, or searching for specific books based on the author name or book title.
The architecture of the online shopping cart application contains two sections, Admin and Client. In addition, this application uses a database created in MySQL server to store data.
The Admin section of the online shopping cart application performs administrative tasks to maintain the state of the application, such as adding or removing a category and adding or removing a book from a category. The Client section enables the end user perform activities related to online transactions, such as registering with the application, navigating through the various available categories to search for specific books, and adding the selected books to the cart.
Figure 9-1 shows the architecture of the online shopping cart application:
The index.php page is the first page of the application that allows the end users to log on to the application. The end user logs on as an administrator, using the administrative user ID. The index.php file contains a hyperlink to the new.php file that registers new end users and creates new client accounts.
The adminFirst.php page is the first Web page of the Admin section. It contains hyperlinks to six other Web pages, which perform specific operations related to the administration of the application. The Web pages include:
The logout.php Web page is displayed when the end user logs out from the application.
The add_book.php Web page adds a book in a category. The view_book.php Web page displays the details of a book.
The list_cat.php Web page lists all the existing categories of the books. The list_cat.php Web page contains hyperlinks to the edit_cat.php and del_cat.php Web pages.
The add_cat.php Web page adds a new category to the list of existing categories.
The create_xml.php Web page generates the orders by creating an Extensible Markup Language (XML) file.
The view_book.php Web page also lets you edit and delete a book from a category. It contains hyperlinks to the edit_book.php and del_book.php Web pages.
The edit_cat.php Web page receives the required parameters from the end user to edit a category and calls the edit_cat1.php Web page to edit a category. The del_cat.php Web page receives the required parameters from the end user to delete a category and calls the del_cat1.php Web page to delete a category.
The edit_book.php Web page receives the required parameters from the end user to edit the book information and calls the edit_book1.php Web page to edit a book. The del_book.php Web page receives the required parameters from the end user to delete a book and calls the del_book1.php Web page to delete a book.
The Web pages of the client section are stored in the client folder of the application. The index.php Web page directs all registered clients with valid account IDs to the welcome.php Web page. This is the first page of the client section. The Welcome.php Web page contains hyperlinks to the logout.php and book_desc.php Web pages. The logout.php Web page lets a client log out from the application and go back to the index.php Web page. The book_desc.php Web page contains a hyperlink to the checkout.php and add_cart.php Web pages. The add_cart.php Web page adds the selected book in the cart of the end user or client. The check_out.php Web page shows the status of the cart and contains a link to the final_order.php Web page, which displays the order number of the client.
The online shopping cart application uses the MySQL database, which consists of the category, book, user_profile, order1, transaction, and tmp tables. The category table stores information related to the categories of the books, such as category ID and name of the category.
Table 9-1 lists the structure of the category table:
field name | data type |
---|---|
tbl_id | int(11), primary key, auto_increment |
item_type | varchar(50), unique, NOT NULL |
The book table stores information about the books available in the online shopping cart application. The book table consists of information, such as book ID, category, book title, and author name.
Table 9-2 lists the structure of the book table:
field name | data type |
---|---|
item_no | varchar(20), primary key, NOT NULL |
item_type | varchar(50) references category(item_type) |
title | varchar(60), NOT NULL |
author | varchar(60), NOT NULL |
price | float, NOT NULL |
The user_profile table stores the registration information of the end users registered using the online shopping cart application. The user_profile table stores information, such as the user name, password, address, phone number, and credit card information.
Table 9-3 lists the structure of the user_profile table:
field name | data type |
---|---|
name | varchar(40), NOT NULL |
user_id | varchar(20), primary key |
password | varchar(20), NOT NULL |
address_line1 | varchar(40), NOT NULL |
address_line2 | varchar(40), NOT NULL |
city | varchar(20), NOT NULL |
country | varchar(20), NOT NULL |
pin | varchar(20), NOT NULL |
email_id | varchar(20), NOT NULL |
phone_number | varchar(20), NOT NULL |
card_no | varchar(20), NOT NULL |
card_type | varchar(20), NOT NULL |
expirty_date | varchar(20), NOT NULL |
fax_number | varchar(20), NOT NULL |
The order1 table stores the order information, such as the order number, book ID, and user ID.
Table 9-4 lists the structure of the order1 table:
field name | data type |
---|---|
tbl_id | int(11) references category(tbl_id) |
order_no | int(11) primary key auto_increment |
item_no | varchar(20) references book(item_no) |
user_id | varchar(40) references user_profile(user_id) |
The tmp table stores the session information for each order.
Table 9-5 lists the structure of the tmp table:
field name | data type |
---|---|
order_no | int(11), primary key auto_increment |
user_id | varchar(20), NOT NULL |
item_no | varchar(20), NOT NULL |
sesid | varchar(50), NOT NULL |
date | date, NOT NULL |
The online shopping cart application uses the shop.sql script to create the required databases and tables. You need to run the shop.sql script from the command prompt of the MySQL server to create the databases and the tables. The code to run the shop.sql script from the command line is:
mysql shop < shop.sql
In the above code, the shop parameter specifies the name of the database, and shop.sql is the script file that consists of the SQL statements to create the tables required to work with the online shopping cart application.
Listing 9-1 shows the content of the shop.sql script:
# Table structure for table 'category' CREATE TABLE category ( tbl_id int(11) NOT NULL auto_increment, item_type varchar(20) NOT NULL, PRIMARY KEY (tbl_id), UNIQUE item_type (item_type) ); # Table structure for table 'book' CREATE TABLE book ( item_no varchar(20) NOT NULL, item_type varchar(20) NOT NULL references category(item_type), title varchar(60) NOT NULL, author varchar(60) NOT NULL, price float DEFAULT '0' NOT NULL, PRIMARY KEY (item_no) ); # Table structure for table 'user_profile' CREATE TABLE user_profile ( name varchar(40) NOT NULL, user_id varchar(20) NOT NULL, password varchar(20) NOT NULL, address_line1 varchar(40) NOT NULL, address_line2 varchar(40), city varchar(20) NOT NULL, country varchar(20) NOT NULL, pin varchar(20) NOT NULL, email_id varchar(20) NOT NULL, phone_number varchar(20) NOT NULL, card_no varchar(20) NOT NULL, expiry_date varchar(20) NOT NULL, card_type varchar(20) NOT NULL, fax_number varchar(20) NOT NULL, PRIMARY KEY (user_id) ); # Table structure for table 'order1' CREATE TABLE order1 ( tbl_id int(11) NOT NULL references category (tbl_id), order_no int(11) auto_increment, item_no varchar(20) NOT NULL references book(item_no), user_id varchar(40) NOT NULL references user_profile(user_id), PRIMARY KEY (order_no) ); # Table structure for table 'tmp' CREATE TABLE tmp ( order_no int(11) NOT NULL auto_increment, user_id varchar(20) NOT NULL, item_no varchar(20) NOT NULL, sesid varchar(50) NOT NULL, date date DEFAULT '0000-00-00' NOT NULL, PRIMARY KEY (order_no) ); # Table structure for table 'transaction' CREATE TABLE transaction ( order_no int(11) NOT NULL auto_increment, user_id varchar(20) NOT NULL, date date DEFAULT '0000-00-00' NOT NULL, status varchar(20) NOT NULL, PRIMARY KEY (order_no) );
The above listing creates the book, category, order1, user_profile, tmp, and transaction tables in the shop database. After creating all the tables, you need to insert a record in the user_profile table that contains the user ID and password. Use the following code to insert a row in the user_profile table:
INSERT INTO user_profile VALUES ( 'admin', 'admin', 'admin', '', '', '', '', '', '', '', '', '', '', '');
The above code creates an account that is used by the end user working as an administrator, with admin as the user ID and password.