best way to define a database with CAT/SUBCAT/SUB-SUBCAT.?

Forum Moderators: open

Message Too Old, No Replies

best way to define a database with CAT/SUBCAT/SUB-SUBCAT.?

rfung

7:07 am on May 7, 2008 (gmt 0)

What is the best way to define a database structure that has:

Main categories

which may have multiple subcategories

which in turn may have additional subcategories

..and this could go on for a few more levels (as a result of user input)

without creating a table for each subcategory since I don't know how far deep it would go?

kirang

10:47 am on May 8, 2008 (gmt 0)

CREATE TABLE `category`
(`icat_id` bigint(64) unsigned NOT NULL auto_increment,
`iparent_id` bigint(64) NOT NULL,
`vcategory` varchar(250) NOT NULL,
`vcaturl` text NOT NULL,
`vrank` int(11) NOT NULL,
`estatus` enum('0','1','3') NOT NULL,
PRIMARY KEY (`icat_id`))
ENGINE=MyISAM

In this table , u can store as many subcat you want.
iparent_id will have its parent cat_id value.

rfung

2:55 pm on May 8, 2008 (gmt 0)

Great, thanks!

Would you happen to know the typical way to query a table like this? how do i get all the subcats in a query in the manner as:

category subcat1
category subcat1 subcat2
category subcat12
category subcat12 subcat2
category subcat12 subcat21
category subcat13..etc