I have a problem that is hurting my brain - please help!
I have an unknown depth to go to - let's say it's 3
$d=3;
now if $d was a fixed value, I'd write my PHP code like:
$q0 = mysql_query("SELECT * FROM table WHERE nested_under IS NULL");
while($res0 = mysql_fetch_array($q0, MYSQL_ASSOC)) {
//depth = 0
$q1 = mysql_query("SELECT * FROM table WHERE nested_under=$res0[nested_under]");
while($res1 = mysql_fetch_array($q1, MYSQL_ASSOC)) {
//depth = 1
$q2 = mysql_query("SELECT * FROM table WHERE nested_under=$res1[nested_under]");
while($res2 = mysql_fetch_array($q2, MYSQL_ASSOC)) {
//depth = 2
$q3 = mysql_query("SELECT * FROM table WHERE nested_under=$res2[nested_under]");
while($res3 = mysql_fetch_array($q3, MYSQL_ASSOC)) {
//depth = 3
}
}
}
}
and everything would be fine. query1 is fixed and sure. The problem is all the other queries depend upon whether the field 'nested_under' is set to the preceding value or not and iterates until the final depth is achieved. The database is actually a category (nested_under==NULL), subcategory(nested_under==category), subsubcategory(nested_under==subcategory), subsubsubcategory(nested_under==subsubcategory) typ of thing and so the depth (the # of sub(x)cats is not fixed and can be 1 for one category but 10 for the next.
What I am trying to do is print out a tree of the category structure. I know the maximum depth ever achieved as there is a table field depth showing the depth of the subcat, from a mysql query:
SELECT MAX(depth) AS depth FROM table
(at the moment, maximum depth is 4, but tomorrow it may be 8)
How can I nest X mysql queries, where X is the maximum depth, within the first mysql_query??
My table structure is:
cat_id
name
shortname
nested_under
depth
cat_id is unique
what I want to do is have a tree print out:
category1
|__subcat1
|__subcat2
|__subsubcat1
|__subcat3
|__subsubcat1
|__subsubsubcat1
|__subsubcat2
category2
|__etc
in the tree above maximum depth = 3 (the main category doesn't have depth). I can hardcode the code as given above to printout the tree, but if maximum depth for one category changes to 4 in the future, depth 4 will never be shown, until I go back and add another sql query iteration
If that makes any sense to you, bravo! Any ideas? Please please help, it's doing my nut in!
