Building A Persistent Shopping Cart With PHP and MySQL - Creating the database (Page 2 of 6 ) Let's assume that we're running a web site that sells Sony Playstation 2 games. We need one table to store the details of each product, and we also need a table to store the contents of each users shopping cart, so that they can be persisted over multiple sessions. Fire up the MySQL console application and create a database named cart. Fill it with two tables: items and cart, like this: create database cart;
create table items
(
itemId int auto_increment not null,
itemName varchar(50),
itemDesc varchar(250),
itemPrice decimal(4,2),
primary key(itemId),
unique id(itemId)
);
create table cart
(
cartId int auto_increment not null,
cookieId varchar(50),
itemId int,
qty int,
primary key(cartId),
unique id(cartId)
);The first table, items, will contain a list of items that the user will be able to add to his/her cart. The items table contains four fields, as described below: - itemId: A unique numeric identifier that gives each item its own ID.
- itemName: The name of the item in the catalog.
- itemDesc: A short description of the item in the catalog.
- itemPrice: The price of the item, such as 45.99.
The cart table will store the details of each item in the users cart as he/she adds them. The cart table also contains four fields, which are described below: - cartId: A unique numeric identifier that gives each item in the users cart its own ID.
- cookieId: This is the most important field in both of the tables. It's used to persist the users cart over multiple sessions. It is the value of the session ID with which the user first started browsing our product range.
- itemId: The ID of the item that the user is purchasing.
- qty: The number of this specific item being purchased.
In this article I won't show you how to create forms to add, edit and delete items from the database. That's out of the scope of this article. We will manually add some products (Playstation 2 games) to the items table now: insert into items values(0, 'Tony Hawk 3', 'Tony Hawk is back. Join him in this popular skating game where speed, collisions and tricks come together to produce the best skating game of all time!', 23.95);
insert into items values(0, 'FIFA Soccer 2002', 'The FIFA range of soccer games are the most popular in their genre. FIFA Soccer 2002 includes an all new team line up, advanced management capabilities, and richer, more realistic graphics.',36.50);
insert into items values(0, 'SSX Tricky', 'Image snowboarding down a steep hill at 100 miles per hour and you have SSX Tricky. It\'s packed with new players, new moves, and a whole new list of stages to complete.', 45.50);
Now that our database is good and ready to go, let’s create a simple PHP script that will list each item from the items table, providing a link to add each item to our shopping cart (which we will create later).Next: Displaying the items >>
More MySQL Articles
More By Mitchell Harper |
/ 124 
