How to retrieve subcategories of a given category

Question

I've been looking around SO for a sec and been seeing questions about displaying categories and was wondering how would I display all my sub categories for my parent categories for example my link below?

I'm using PHP & MySQL -Thanks in advance!

Here is my link.

http://www.example.com/categories/index.php?main_category=web-development&sub_1=programming&sub_2=html&sub_3=basics&sub_4=forms&sub_5=select-tag

Here is my MySQL code.

CREATE TABLE categories ( 
id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
parent_id INT UNSIGNED NOT NULL DEFAULT 0, 
category TEXT NOT NULL, 
PRIMARY KEY (id), 
INDEX parent (parent_id)
);

I know that i can then use mod rewrite to get urls to look like this.

http://www.example.com/categories/main-category/sub-1/sub-2/sub-3/sub-4/sub-5/

Answer 1

MySQL doesn't yet support recursive queries so you can't really do what you are asking for without a lot of effort.

I suggest you take a look at Bill Karwin's slideshow Models for heirarchical data for other ways to store your data (examples: nested sets, closure table). Slide 48 compares the advantages and disadvantages of the different designs. 'Query subtree' is particularly interesting for you, and the adjacency list model you are using is the one where this type of query is hardest to write.