tags:

views:

60

answers:

1

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/
+1  A: 

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.

Mark Byers
An example of how to do it will be very helpful even if i have to add another table.
NeW-GeEk
@NeW-GeEk: An example of what? Simulating a recursive query for your adjacency list design? There is a good explanation with examples on Quassnoi's site: http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/ But I warn you now - what you are trying to do is not easy and it pushes the limits of what is sensible to do in a database that doesn't support recursion, like MySQL. I would seriously consider using a different model for storing your heirarchical data.
Mark Byers
So what is the best way to go about creating the code I need?
NeW-GeEk
@NeW-GeEk: Choose one of: a) change database to one that supports recursive queries b) change model to (for example) nested sets c) read through the examples on Quassnoi's site that I linked to in my previous comment, understand how they work, learn how to write it yourself, then grit your teeth and write the code.
Mark Byers
So how do you suggest my MySQL table or tables structure should look like?
NeW-GeEk
@NeW-GeEk: The nested sets model might be a good approach for your situation. But this design has some serious drawbacks that you should evaluate first. In particular updates to the table are quite expensive.
Mark Byers