Logic Problem...

CrashRoX

I have been coding all day today, and either I fried myself or im having some problem with the logic behind this... I have a categories table in an MYSQL database that should output in a hieratical manor. Looks something like this:

id - parent_id - title
1 0 Cat1
2 1 Cat2
3 1 Cat3
4 2 Cat4
5 2 Cat5
6 4 Cat6
7 4 Cat7
etc...

Im trying to create list in its correct hieratical order... So something like:
Cat1

Cat2

Cat4

Cat6
Cat7
Cat5
Cat3

I tried several group by statements, some array ideas. But everything im trying seems to end up using one to many queries or my array is to messy to look at. I know this is simple, so any help is appreciated. Thanks in advanced... Time to find some more coffee 🙂

planetsim

Any chance of seeing what you have already.

CrashRoX

This was my most recent attempt:

$sql = 'SELECT * FROM `categories` WHERE `parent_id` = 0 ORDER BY `title`';  

$result = mysql_query($sql);

while ($row = mysql_fetch_array($result)) {  

echo "(" . $row['id'] . ")" . $row['title'] . "<br>"; // print title from highest level (0)

$sql = 'SELECT * FROM `categories`WHERE `parent_id` = '.$row['id'].' ORDER BY `title`'; 
$result1 = mysql_query($sql); 

    while ($row1 = mysql_fetch_array($result1)) {  
	echo ">> (" . $row1['id'] . ")" . $row1['title'] . "<br>"; // print sub title (x)
    }  
}

Unfortunatly it only alows for one directory deep...

drawmack

You'll need a recursive function to do this properly.

function build_tree($parent = 0,$indent = '->') {
    $sql = "SELECT * FROM categories WHERE parent_id =  " . $parent  . " ORDER BY title";  

    $result = mysql_query($sql);
    $retval = '';
    if(mysql_num_rows($result) > 0) {
        while ($row = mysql_fetch_array($result)) {  

            $retval .= "(" . $row['id'] . ")" . $row['title'] . "<br>";
            $retval .= build_tree($row['id'],'-' . $indent);
        } //end while
    } //end if
    return $retval
} //end build_tree

then you'll have to call it like this

echo build_tree();

CrashRoX

ahhhhhhh I see what you did, I knew it was going to end up using recursion. THANK YOU very much!