[SOLVED] Generating a tree structure from a MySQL database

[maxkool]

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.

[GingerRobot]

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 />'
}
?>

[maxkool]

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;
}