PHPBuilder - SQL Theory & How-To Page 2



RSS Twitter
Articles Databases

SQL Theory & How-To - Page 2

by: Joe Stump
|
January 3, 2001

The first thing to note is that we have two tables here with a many to one relationship. "Many to one what?" What this means is that there can be "many" links in "one" category, thus creating a "many to one" relationship. Other types are "one to one" and "many to many". To keep things simple we won't be going into those. Another thing to note is that they share a common bond "categoryID" - this "relates" each link to its respective category. Notice that they have the exact same attributes - "tinyint(2) UNSIGNED NOT NULL" - which will optimize the JOIN we do on that field later in this article. "Optimize ... who needs to optimize - it's only gonna have 15 links in it!" - well it's a good practice to get in, I know MySQL needs to have EXACT declarations for an optimized JOIN - other DB's may not. A good article on normalizing your tables can be found here.
I'll leave how you enter the data into the tables up to you. I generally make a simple form to enter in cateogories into link_categories and then use that table in my form for link_information - choosing a category each time I enter a link and the categoryID being entered into the link_information table. So after we have some data entered into our tables we have something like this:
mysql> select * from link_categories;       
+------------+-----------------+
| categoryID | categoryName    |
+------------+-----------------+
|          1 | miester.org     |
|          2 | Linux           |
|          3 | miester's picks |
|          4 | PHP             |
|          5 | My Desktop      |
|          6 | Mini Howto's    |
+------------+-----------------+
6 rows in set (0.00 sec)

mysql> select * from link_information;
+--------+------------+-----------------+--------------------------+----------+----------------------+
| linkID | categoryID | linkTitle       | linkURL                  | linkHits | linkInfo             |
+--------+------------+-----------------+--------------------------+----------+----------------------+
|      1 |          2 | Linux.com       | http://www.linux.com     |       69 | Info about link here |
|      2 |          2 | Slackware Linux | http://www.slackware.com |       70 | Info about link here |
|      3 |          1 | Home            | http://www.miester.org   |      377 | Info about link here |
+--------+------------+-----------------+--------------------------+----------+----------------------+
"But I only want to show the title, link, and category name on my homepage!" - OK that's where the JOIN comes into play here.
mysql> SELECT I.linkTitle as title, I.linkURL as href, C.categoryName as category 
    -> FROM link_information as I, link_categories AS C 
    -> WHERE I.linkCategory=C.categoryID;
+-----------------+--------------------------+-------------+
| title           | href                     | category    |
+-----------------+--------------------------+-------------+
| Linux.com       | http://www.linux.com     | Linux       |
| Slackware Linux | http://www.slackware.com | Linux       |
| Home            | http://www.miester.org   | miester.org |
+-----------------+--------------------------+-------------+
3 rows in set (0.00 sec)

« Previous Page
1
|
2
|
3
|
4
Next Page »

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:
Joe Stump

Comment:



Comment:

(Maximum characters: 1200). You have characters left.