Home arrow PHP arrow Page 8 - Building an E-Commerce Site Part 3: Catalogs and Shopping Carts

Step 1: Database Changes - PHP

This is the third and final article in a three-part series dealing with using PHP 4 and MySQL to make a comprehensive e-commerce storefront solution. This article covers the shopping cart, payment processing, and database engine considerations, among many other topics. Full source code included!

TABLE OF CONTENTS:
  1. Building an E-Commerce Site Part 3: Catalogs and Shopping Carts
  2. Assumptions and Requirements
  3. Overview of The Process
  4. MyMarket Shopping Experience
  5. The Product Catalog
  6. The Shopping Cart
  7. Payment Processing
  8. Step 1: Database Changes
  9. Step 2: Extracting the New Scripts
  10. Step 3: General Script Changes from Tutorial 2
  11. Step 4: New Shopping Scripts
  12. Conclusion
By: Ying Zhang
Rating: starstarstarstarstar / 50
June 08, 2000

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

First off, let's go through the database changes since part 2. Fire up MySQL and login as the root user by issuing this command from the shell prompt:


$ mysql -u root -p
You should see MySQL started:

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 412 to server version: 3.22.30 Type 'help' for help. mysql>
Now select the mymarket database:

USE mymarket;

Order Tracking Tables

First thing we need to add is some tables to track customer orders. Specifically, we would like to track the orders that are going through MyMarket, and the products that are being purchased.

We will need two new tables, an orders table and an order_items table. There is a one-to-many relation between orders and order_items, we say that an order can have one or more order items, and order items can belong to one or more orders.

First the orders table to hold the order details. Each record in this table will contain:

  • id - An order ID (which is auto-generated by MySQL)
  • username - The username of the customer who initiated the order
  • o_timestamp - The date/time when the order was created
  • a_timestamp - The date/time when the payment was authorized or declined
  • status - A status of the authorization process
  • status_details - A text message describing the status of the autorization process
  • custinfo - Customer information
  • comments - Any special instructions or comments to go with the order
  • amount - The amount the customer was billed
  • - An order ID (which is auto-generated by MySQL) - The username of the customer who initiated the order - The date/time when the order was created - The date/time when the payment was authorized or declined - A status of the authorization process - A text message describing the status of the autorization process - Customer information - Any special instructions or comments to go with the order - The amount the customer was billed

Issue this statement to create the table:


CREATE TABLE orders ( id int not null auto_increment, username varchar(16) not null, o_timestamp datetime, a_timestamp datetime, status tinyint, status_details varchar(255), custinfo text, comments text, amount float(5,2), PRIMARY KEY (id) INDEX username (username) )
Next, the order_items table to hold the items that are a part of an order. The fields in this table are:
  • order_id - The order ID to which this item belongs
  • product_id - The item that was ordered (from the products table)
  • price - The price of the item at the time of purchase (this may differ from the price now)
  • qty - The and the quantity ordered
  • - The order ID to which this item belongs - The item that was ordered (from the products table) - The price of the item at the time of purchase (this may differ from the price now) - The and the quantity ordered
Issue this statement to create the table:

CREATE TABLE order_items ( order_id int not null, product_id int not null, price float(5, 2) not null, qty int not null, PRIMARY KEY (order_id, product_id) )

On-Special Products

While we're doing database changes, let's also add an extra flag to our products table so we can mark a product as being on-special. We will display on-special products on the MyMarket homepage.

First let's take a look at the products table as it is now:


mysql> DESCRIBE products; +-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | | PRI | 0 | auto_increment |
| name | varchar(25) | | MUL | | |
| description | varchar(255) | | | | |
| price | float(5,2) | | | 0.00 | |
+-------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

We are going to add an extra field called on_special to flag a product as being on-special. To modify an existing table, we will need to issue an ALTER statement. Issue this statement like so:

ALTER TABLE products ADD on_special tinyint default 0 AFTER price;
This will add the on_special field of type tinyint, the default value be 0. This field will be added after the price field. Now let's look at the table again to verify the new field is there:

mysql> DESCRIBE products; +-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | | PRI | 0 | auto_increment |
| name | varchar(25) | | MUL | | |
| description | varchar(255) | | | | |
| price | float(5,2) | | | 0.00 | |
| on_special | tinyint(4) | | | 0 | |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Okay, that looks good. We will need to modify our product management scripts to deal with this new on_special field.

 
 
>>> More PHP Articles          >>> More By Ying Zhang
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: