phpHoo, Part I | 5

phpHoo, Part I

The Links Table

Let's create a table to hold all of our Link data. First, the actual command:

    mysql> create table Links (
           LinkID bigint(21) NOT NULL auto_increment,
           CatID bigint(21) NOT NULL,
           Url varchar(255) NOT NULL,
           LinkName varchar(64) NOT NULL,
           Description varchar(255) NOT NULL,
           PRIMARY KEY (LinkID),
           UNIQUE (Url)
           );

Most of this you should be able to discern for yourself. Every link in the database will have it's own unique identifier (LinkID). Every link will be associated with a Category ID (CatID). Each Link will have a Url, LinkName, and Description using variable length strings. The primary key for this table is the LinkID, and we set the Url to be UNIQUE so that no two entries in the database can contain the same Url.

Something to keep in mind when designing your tables. Don't try to think of everything you might need in your data structure at the outset. Your data structures are always going to change while you're programming as you think of things you need in the database. You can come back and modify these tables at any time, and in fact, we're going to be changing the Links table later in this tutorial.

Our data structures are completed, so let's give them some data to work with. (Server's responses omitted)

mysql> insert into Categories (CatName) values ('Art');
mysql> insert into Categories (CatName) values ('Computers');
mysql> insert into Categories (CatName) values ('Religion');

This creates 3 top level categories. Since we did not specify a 'CatParent' for these entries, the CatParent entry was set to NULL automatically. We still need to create some sub-categories under the 'Computers' category but before we do so, we need to know the 'Computers' category ID number:

mysql> select * from Categories;
+-------+--------------+-----------+
| CatID | CatName      | CatParent |
+-------+--------------+-----------+
|     1 | Art          |      NULL |
|     2 | Computers    |      NULL |
|     3 | Religion     |      NULL |
+-------+--------------+-----------+
3 rows in set (0.01 sec)

So 'Computers' is Category ID number 2. We can now assign the CatParent of the sub-categories:

mysql> insert into Categories (CatName,CatParent) values ('Dynamic HTML',2);

Let's see if everything is still OK:

mysql> select * from Categories;
+-------+--------------+-----------+
| CatID | CatName      | CatParent |
+-------+--------------+-----------+
|     1 | Art          |      NULL |
|     2 | Computers    |      NULL |
|     3 | Religion     |      NULL |
|     4 | Dynamic HTML |         2 |
+-------+--------------+-----------+
4 rows in set (0.01 sec)

Let's fill in the rest of the "Computers" sub-categories:

mysql> insert into Categories (CatName,CatParent) values ('E-Commerce',2);
mysql> insert into Categories (CatName,CatParent) values ('Graphics',2);
mysql> insert into Categories (CatName,CatParent) values ('Internet',2);
mysql> insert into Categories (CatName,CatParent) values ('Javascript',2);
mysql> insert into Categories (CatName,CatParent) values ('Perl',2);

If we want to know what sub-categories are under Computers, we could do this:

mysql> select * from Categories where CatParent = 2;
+-------+--------------+-----------+
| CatID | CatName      | CatParent |
+-------+--------------+-----------+
|     4 | Dynamic HTML |         2 |
|     5 | E-Commerce   |         2 |
|     6 | Graphics     |         2 |
|     7 | Internet     |         2 |
|     8 | Javascript   |         2 |
|     9 | Perl         |         2 |
+-------+--------------+-----------+
6 rows in set (0.00 sec)

To find the names of all the "Top" level categories, we need to write our select statement to handle NULL values. We can't use "where CatParent = NULL" because it's an oxymoron. ("Something" can never equal "Nothing") So we need to use the "IS NULL" construct:

mysql> select * from Categories where CatParent IS NULL;
+-------+-----------+-----------+
| CatID | CatName   | CatParent |
+-------+-----------+-----------+
|     1 | Art       |      NULL |
|     2 | Computers |      NULL |
|     3 | Religion  |      NULL |
+-------+-----------+-----------+
3 rows in set (0.01 sec)

Now that we have some data in the database to work with, let's get down to writing our program to access this data.

Produced by Jonathan Eisenzopf and
Created: July 20, 1999
Revised: July 20, 1999

URL: https://www.webreference.com/perl/xhoo/php1/

phpHoo, Part I | 5

phpHoo, Part I

The Links Table

Find a programming school near you