Home arrow PHP arrow Page 6 - Building an E-Commerce Site Part 1: Building a Product Catalog

Step 3: Populating the Tables with Data - PHP

This is the first 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 product catalog database design and creating the catalog administration interface.

TABLE OF CONTENTS:
  1. Building an E-Commerce Site Part 1: Building a Product Catalog
  2. Assumptions and Requirements
  3. Overview of a Simplified E-Commerce System
  4. Step 1: Creating the Database
  5. Step 2: Creating the Product Catalog
  6. Step 3: Populating the Tables with Data
  7. Step 4: Creating Catalog Maintenance Screens
  8. Step 5: Putting It All Together
By: Ying Zhang
Rating: starstarstarstarstar / 129
April 25, 2000

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
Now that we've created the tables, we will go put some data into them. Starting with the categories table, we need to add the [Top] category, and we want it to have the ID of 0:

mysql> INSERT INTO categories (id, parent_id, name, description) -> VALUES (0, 0, 'Top', 'This is the top level category.')
Let's see what we just added by running a SELECT query:

mysql> SELECT * FROM categories; +----+-----------+------+---------------------------------+ | id | parent_id | name | description | +----+-----------+------+---------------------------------+ | 1 | 0 | Top | This is the top level category. | +----+-----------+------+---------------------------------+
Everything is in order here, or is it? Looking carefully we see that the ID is 1 instead of 0 -- which is what we wanted it to be. What gives? This is MySQL's autonumber, it automatically assigned the number 1. Normally this would be okay, but we want our [Top] category to have the ID of 0 (just because) so let's issue an UPDATE statement to fix this:

mysql> UPDATE categories SET id = 0 WHERE id = 1;
Now let's see it again:

mysql> SELECT * FROM categories; +----+-----------+------+---------------------------------+ | id | parent_id | name | description | +----+-----------+------+---------------------------------+ | 0 | 0 | Top | This is the top level category. | +----+-----------+------+---------------------------------+
That's much better :). Now let's create a few more categories, feel free to come up with more creative descriptions:

mysql> INSERT INTO categories (name, description) -> VALUES ('Fruits', 'Fresh and tasty fruits.'); mysql> INSERT INTO categories (name, description) -> VALUES ('Snacks', 'Tasty snacks.');
Notice that this time around we didn't specify the "id" and "parent_id" fields. That's because the "id" field is an autonumber and the "parent_id" field defaults to 0. We've already seen that specifying a value for autonumber fields doesn't do anything in MySQL so we don't have to bother. As for the "parent_id" field, the default value is 0, which is the ID of the [Top] category so okay for now because we are creating top level categories.

Now we should create some sub-categories, but before we do that let's find out what ID's MySQL assigned to the two that we've just created:

mysql> SELECT * FROM categories; +----+-----------+--------+---------------------------------+ | id | parent_id | name | description | +----+-----------+--------+---------------------------------+ | 0 | 0 | Top | This is the top level category. | | 1 | 0 | Fruits | Fresh and tasty fruits. | | 2 | 0 | Snacks | Tastely snacks. | +----+-----------+--------+---------------------------------+
So we've got 1 for [Fruits] and 2 for [Vegetables]. Now let's make some Fruit and Vegetable categories:

mysql> INSERT INTO categories (parent_id, name, description) -> VALUES (1, 'Apples', 'Yummy crunchy apples.'); mysql> INSERT INTO categories (parent_id, name, description) -> VALUES (1, 'Berries', 'Yummy berries.'); mysql> INSERT INTO categories (parent_id, name, description) -> VALUES (2, 'Chips', 'Crunchy Greasy Treats.'); mysql> INSERT INTO categories (parent_id, name, description) -> VALUES (2, 'Icecream', 'Great on a hot summer day.');
Okay, now let's see what we have again:

mysql> SELECT * FROM categories; +----+-----------+----------+---------------------------------+ | id | parent_id | name | description | +----+-----------+----------+---------------------------------+ | 0 | 0 | Top | This is the top level category. | | 1 | 0 | Fruits | Fresh and tasty fruits. | | 2 | 0 | Snacks | Tasty snacks. | | 3 | 1 | Apples | Yummy crunchy apples. | | 4 | 1 | Berries | Yummy berries. | | 5 | 2 | Chips | Crunchy Greasy Treats. | | 6 | 2 | Icecream | Great on a hot summer day. | +----+-----------+----------+---------------------------------+
Let's practice writing some more interesting SELECT queries. For example, let's find all the sub-categories under the [Fruits] category:

mysql> SELECT cat.id, cat.name, cat.description -> FROM categories cat, categories parent -> WHERE cat.parent_id = parent.id -> AND parent.name = 'Fruits'; +----+---------+-----------------------+ | id | name | description | +----+---------+-----------------------+ | 3 | Apples | Yummy crunchy apples. | | 4 | Berries | Yummy berries. | +----+---------+-----------------------+
Note that was a little unnecessary because we already knew that the ID of the [Fruits] category was 1, we could have just run the query:

mysql> SELECT * FROM categories WHERE parent_id = 1;
to do the same thing, but that was for fun so that we could practice our joins :) Okay, now that we've got some category data in there let's put in some products.

mysql> INSERT INTO products (name, description, price) -> VALUES ('Granny Smith', 'Yummy Granny Smith Apples', 1.00); mysql> INSERT INTO products (name, description, price) -> VALUES ('Strawberries', 'Fresh Strawberries', 1.50); mysql> INSERT INTO products (name, description, price) -> VALUES ('Apple Chips', 'Crunch Dried Apple Chips', 2.00);
Let's see what we have in the products table now:

mysql> SELECT * FROM products; +----+--------------+---------------------------+-------+ | id | name | description | price | +----+--------------+---------------------------+-------+ | 1 | Granny Smith | Yummy Granny Smith Apples | 1.00 | | 2 | Strawberries | Fresh Strawberries | 1.50 | | 3 | Apple Chips | Crunch Dried Apple Chips | 2.00 | +----+--------------+---------------------------+-------+
Okay, we've got some products in the system, now we have to categorize them. We will start off by putting the Granny Smith apples into the [Apples] category (id:3) and the Strawberries into the [Berries] category (id:4).

mysql> INSERT INTO products_categories (product_id, category_id) VALUES (1, 3); mysql> INSERT INTO products_categories (product_id, category_id) -> VALUES (2, 4);
We have Apple Chips showing up in both the [Apples] category (id:3) and the [Chips] category (id:5), so we need two entries for it in the product_categories table:

mysql> INSERT INTO products_categories (product_id, category_id) VALUES (3, 3); mysql> INSERT INTO products_categories (product_id, category_id) -> VALUES (3, 5);
Let's take a look at our products_categories table now:

mysql> SELECT * FROM products_categories; +------------+-------------+ | product_id | category_id | +------------+-------------+ | 1 | 3 | | 2 | 4 | | 3 | 3 | | 3 | 5 | +------------+-------------+
Wow, that's so informative! Let's write a more useful query, we will start by writing a query to display the category, name, and price of all the products in the system:

mysql> SELECT cat.name, prod.name, prod.price -> FROM categories cat, products prod, products_categories pc -> WHERE cat.id = pc.category_id -> AND prod.id = pc.product_id +---------+--------------+-------+ | name | name | price | +---------+--------------+-------+ | Apples | Granny Smith | 1.00 | | Berries | Strawberries | 1.50 | | Apples | Apple Chips | 2.00 | | Chips | Apple Chips | 2.00 | +---------+--------------+-------+
That's much more useful than just looking at a bunch of numbers isn't it? Okay, let's try another one, find the name and price of all the products under the [Apples] category:

mysql> SELECT prod.name, prod.price -> FROM categories cat, products prod, products_categories pc -> WHERE cat.id = pc.category_id -> AND prod.id = pc.product_id -> AND cat.name = "Apples"; +--------------+-------+ | name | price | +--------------+-------+ | Granny Smith | 1.00 | | Apple Chips | 2.00 | +--------------+-------+
Getting the hang of it? Practice writing some queries on your own before moving on to the next step. When you're ready to move on, type QUIT to exit the MySQL client. The next step is to create some PHP scripts for us to do catalog maintenance through a web interface.

 
 
>>> 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: