Building an E-Commerce Site Part 1: Building a Product Catalog - Step 3: Populating the Tables with Data (
Page 6 of 8 )
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.
/ 102 
