[SOLVED] Generating a tree structure from a MySQL database

Hi everyone,

I'm a beginner in PHP and I hope the following problem is worthy of your time. I'm sure there are a million ways of handling what I'm trying to accomplish but I'm too green as yet to figure out any of them

I have a MySQL table that looks something like this:

Category 1 | Subcategory X | Item 1

Category 1 | Subcategory X | Item 2

Category 1 | Subcategory Y | Item 3

Category 1 | Subcategory Y | Item 4

Category 2 | Subcategory Z | Item 5

Category 2 | Subcategory Z | Item 6

Basically, a collection of Items which fall under different Subcategories, which in turn fall under 2 or 3 main Categories.

I've been asked to code an interface where one can easily add Categories, Subcategories and Items. I also have to display these to the viewer in the form of a tree structure.

I've had no trouble writing a form which INSERTs data into the database but I'm stumped as to how I can display this table to the viewer as a tree, e.g. show Category 1 as a title, then under that show Subcategory X and under that Items 1 and 2 etc.

My idea was to SELECT everything from Category 1 and display all the Subcategories (which I managed to do) then select each Subcategory and display the items. Unfortunately this meant that Subcategory names had to be unique, because if I had the same name under different Categories, they would all be selected.

Anyway, if you guys have any ideas, I'd really appreciate the help. Thanks.

Well, assuming there's never any more than two levels to your structure (e.g. there couldn't be a sub category of a sub category), then it's just a case of ordering by the categories, then the sub categories. You then just have to keep track of what was the category/sub category last time your loop was run. If it's different, show the new category. If it's not, don't.

Something like this:

<?php
$prevcat = '';
$prevsubcat = '';
$sql = "SELECT * FROM tbl ORDER BY category,subcategory";
$result = mysql_query($sql) or trigger_error(mysql_error(),E_USER_ERROR);
while($row = mysql_fetch_assoc($result){
    $cat = $row['category'];
    $subcat = $row['subcategory'];
    $item = $row['item'];
    if($cat != $prevcat{
        echo $cat.'<br />';
        echo $subcat.'<br />';//if the category has changed, we also want to show the new subcat
    }elseif($subcat != $prevsubcat){
        echo $subcat.'<br />';
    }
    echo $item.'<br />'
}
?>

Whoa, so simple.. I'd written 5 times as much code and wasn't even close to getting where I wanted

Thanks a lot Ben!

LE. in case anyone ever needs this, here's the final thing : (pretty much GR's version, just updated $prevcat and $prevsubcat at the end)

$prevcat = '';
$prevsubcat = '';
$sql = "SELECT * FROM $tbl_name ORDER BY categ, subcateg";
$result = mysql_query($sql) or trigger_error(mysql_error(),E_USER_ERROR);
while($row = mysql_fetch_assoc($result))		{
    $cat = $row['categ'];
    $subcat = $row['subcateg'];
    $item = $row['itemname'];
$description = $row['itemdesc'];
    if($cat != $prevcat){
        echo $cat.'<br />';
        echo 'sc '. $subcat.'<br />';//if the category has changed, we also want to show the new subcat
    }elseif($subcat != $prevsubcat){
        echo $subcat.'<br />';
    }
    echo 'it '.$item.'<br />';
echo 'desc '.$description.'<br />';

$prevcat = $cat;
$prevsubcat = $subcat;
}