Forget the tree I'm lost as hell

rulian

Okay, here we go:::

my articles table looks like so:
it containt articles divided into catagories and subcatagories:

id | title | cat_id | subcat_id

lets say we have 3 entries:

id | title | cat_id | subcat_id
1 | one | 2 | 1
2 | two | 2 | 2
3 | three | 1 | 2

okay, now I have to make that, look like this on my website::

-cat2
--subcat 1
-----article1
--subcat 2
-----article2
-cat1
--subcat1
---- EMPTY
--subcat2
---article3

Does anybody have a clue how to do this without querying every time I need to get to organize information. Kind of have one master query of which everything can be done

Anon

Can't you just do a full query, get the array and loop through that each time?

rulian

i would if I had very little cats and sub cats, but I have over 10 cats and about 20 sub cats, 10 X 20 = 200, that's four hundred queries if I do it basic, that's alot of time

Anon

Oracle, db2, and perhaps one or two other majors support extensions to sql that provide the kind of recursive query you need. Mysql does not, so your only recourse is to issue multiple queries.

Note if your tree is strictly limited to three levels, you might be able to get away with 3 queries, run simultaneously. This would involve 3 queries, one for each level, with the proper ORDER BY clause on each. Then, you read the first row from each level, and loop at the lowest possible level until you're out of rows that match the next higher level.