hearts. Although PHP and MySQL are the fodder for a range of Web applications,
many developers learned their trade with the ambition to write the ubiquitous shopping
cart. If there is a Zen to Web development, it is likely to be experienced while
writing a shopping cart.
Although a common sight on the Web, shopping carts do come in a variety of
different flavors. The various incarnations typically differ in ways that are specific
to the type of business using the software. For example, the sale of items such as
books, CDs, and DVDs differs from the sale of cables, food, building materials, and
janitorial products. The core difference is quantity; you generally buy only a single
book or DVD at time, but it is not uncommon for a restaurant to buy 10 packs of
dinner rolls.
PROJECT OVERVIEW
In this project, you create the core features of a shopping cart. To get a clear idea of
how the project works, take a look at the following use case:
John needs to buy some teabags for his mobile café. He goes to a popular
online shop that was created by an owner of this very book. John does not
have a user account on the Web site but starts shopping anyway. He clicks
the Beverages category and sees the list of teabags. He clicks the Buy link
and is taken to another page, where he can choose the quantity. He selects
10 boxes of teabags and adds them to his shopping cart. The page now
refreshes, and he sees the contents of his shopping cart. John then buys coffee,
and his cart is updated again. John realizes he does not need the coffee
after all, so he clicks the X link to delete the coffee from his cart. John finishes
choosing items and clicks the Go to the Checkout link. He is prompted
for his address, which he fills in, and is taken to the payment screen. John
can choose to pay with PayPal or by check. John clicks the PayPal button
and taken to the PayPal payment screen at paypal.com, where he pays for the
order.
Pauline needs some teabags, too. Pauline already has an account on the site,
so she logs in. She adds the items she needs to her cart and clicks the Go to
the Checkout link. At the address page, she can choose between a new
address and the address stored with her user account. She chooses the
account address and is taken to the payment screen. Pauline chooses to pay
by check and is given instructions about where to send the check and to
whom to make it payable.
Ken runs the Web site and wants to see all current orders. He logs in with his
administrator username and password and is provided with a list of orders.
Ken looks at each item, packages the order, and writes the address on the
parcel. To confirm the completion of the order, Ken clicks the Confirm Payment
link. The order is now complete.
The shopping cart you build in this chapter satisfies all of the features discussed
in the preceding use case, but there is still a huge scope for development.
Shopping carts can become huge and complex systems, and an entire book would
do the subject of building shopping carts justice. This project will provide a solid
foundation in which you can continue to build in extra features.
BUILDING THE DATABASE
The database you will create is shown in Figure 6-1.
FIGURE 6-1 The database schema revolves around the main orders table.
BUILDING THE DATABASE
The database you will create is shown in Figure 6-1.
This entire project fundamentally hinges on orders stored in the orders table.
This table relates to the customers (contains registered customer address details)
and delivery_addresses (contains unregistered and alternative addresses) tables.
Each product (stored in the products table) in the order is stored in the order_items
table. Other tables include logins (stores the registered user’s login details), categories
(contains the categories that the products are part of), and admins (stores
administrator login details).
Implementing the Database
Start phpMyAdmin, create a new database called shoppingcart, and add the following
tables:
Note:
Always Know Your Status
In the orders table is a field called status. The purpose of this field is to indicate
at what point in the shopping cart the user has progressed. This field
has four possible values:
0 The user is still adding items to her shopping cart.
1 The user has entered her address.
2 The user has paid for the item.
10 The administrator has confirmed the transaction and sent the item.
The admins Table
■ id. Make this a TINYINT (lots of users are possible) and turn on auto_
increment. Set this field as a primary key.
■ username. Make this a VARCHAR with a length of 10.
■ password. Make this a VARCHAR with a length of 10.
The categories Table
■ id. Make this a TINYINT (there will be few categories) and turn on
auto_increment in the Extras column. Make this field a primary key.
■ name. Make this a VARCHAR and set the size to 20. (It is unlikely a category
title will be longer than 20 letters.)
The customers Table
■ id. Make this an INT (lots of users are possible) and turn on auto_increment.
Set this field as a primary key.
■ forename. Make this a VARCHAR with a length of 50.
■ surname. Make this a VARCHAR with a length of 50.
■ add1. Make this a VARCHAR with a length of 50.
■ add2. Make this a VARCHAR with a length of 50.
■ add3. Make this a VARCHAR with a length of 50.
■ postcode. Make this a VARCHAR with a length of 10.
■ phone. Make this a VARCHAR with a length of 20.
■ email. Make this a VARCHAR with a length of 100.
■ registered. Make this a TINYINT.
The delivery_addresses Table
■ id. Make this an INT (lots of users are possible) and turn on auto_increment.
Set this field as a primary key.
■ forename. Make this a VARCHAR with a length of 50.
■ surname. Make this a VARCHAR with a length of 50.
■ add1. Make this a VARCHAR with a length of 50.
■ add2. Make this a VARCHAR with a length of 50.
■ add3. Make this a VARCHAR with a length of 50.
■ postcode. Make this a VARCHAR with a length of 10.
■ phone. Make this a VARCHAR with a length of 20.
■ email. Make this a VARCHAR with a length of 100.
The logins Table
■ id. Make this an INT (lots of users are possible) and turn on auto_increment.
Set this field as a primary key.
■ customer_id. Make this an INT.
■ username. Make this a VARCHAR with a length of 10.
■ password. Make this a VARCHAR with a length of 10.
The orderitems Table
■ id. Make this an INT (lots of items are possible) and turn on auto_increment.
Set this field as a primary key.
■ order_id. Make this an INT.
■ product_id. Make this an INT.
■ quantity. Make this an INT.
The orders Table
■ id. Make this an INT (lots of orders are possible) and turn on
auto_increment. Set this field as a primary key.
■ customer_id. Make this an INT.
■ registered. Make this an INT.
■ delivery_add_id. Make this an INT.
■ payment_type. Make this an INT.
■ date. Make this a DATETIME.
■ status. Make this a TINYINT.
■ session. Make this a VARCHAR and set the size to 50.
■ total. Make this a FLOAT.
The products Table
■ id. Make this an INT (lots of images are possible) and turn on auto_increment.
Set this field as a primary key.
■ cat_id. Make this a TINYINT.
■ name. Make this a VARCHAR with a length of 100. It is likely there will be long
product names.
■ description. Make this a TEXT.
■ image. Make this a VARCHAR and set the size to 30.
■ price. Make this a FLOAT.
Insert Sample Data
With a solid set of tables ready to go, add some sample data to get started. Remember,
do not fill in a number in the id column; this is handled by auto_increment.
Feel free to add your own sample data, or use the suggested information.
Sample Data for the admins Table
Create a username and password for the administrator. This example uses jono as
the username and bacon as the password.
Sample Data for the categories Table
Add two categories: beverages and cakes.
Sample Data for the customers Table
Add the following customers, as shown in Table 6-1.
TABLE 6-1 The customers and logins tables store details about registered users.
Sample Data for the logins Table
Add the login details from Table 6-2 for each customer.
TABLE 6-2 Make sure you match the customer_id field to the id field in the customers
table.
Sample Data for the delivery_addresses TableLeave this table empty.
Sample Data for the products Table
Add the products shown in
Table 6-3 to the products table.
