[thelist] Multi-level data storage

rudy r937 at interlog.com
Mon Feb 26 20:38:07 CST 2001


> Okay, I've just answered my own question. There's a great guide to
> creating a Yahoo style directory with PHP and MySQL at
>       http://webreference.com/perl/xhoo/php1/index.html


hi simon

thanks for that, it's a really good article

no sarcasm intended, i really like it

especially the writing style -- example:  "it's a good idea to always
assign a PRIMARY KEY to your tables. This tells MySQL what element in the
table is the item that other tables and associations will key off of."

however, the table structure in this article is not what you want, i.e. it
will not support the same link in several categories

for that, you need a many-to-many structure

you will need to replace this table --

    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)
           );

with these two tables --

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

    create table LinkCats (
           LinkID bigint(21) NOT NULL,
           CatID bigint(21) NOT NULL,
           PRIMARY KEY (LinkID, CatID),
           );

notice that the LinkCats table does not have to have its own auto_increment
field

i haven't really analyzed the code in that article, but obviously, you'd
have to change portions of it to reflect the fact that a link can be in
more than one category

holler if you need any help with the sql if you choose to modify the
webreference code


rudy.ca







More information about the thelist mailing list