Step 1: Database Changes - PHP

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