Step 3: Populating the Tables with Data - PHP

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