Converting adjacency model resultset into nested lists.

[kutchbhi]

Hi, I am trying to implement a categories/subcategories feature for navigation.

For this I am using the The adjacency model as described Here

Description(copy pasta):
This is the categories table:

create table categories
( id       integer     not null  primary key 
, name     varchar(37) not null
, parentid integer     null
, foreign key parentid_fk (parentid) 
      references categories (id)
);

This is the sql query used to fetch all the 'paths' from the categories table. The resultset of this query can be seen in the linked page.

select root.name  as root_name
     , down1.name as down1_name
     , down2.name as down2_name
     , down3.name as down3_name
  from categories as root
left outer
  join categories as down1
    on down1.parentid = root.id
left outer
  join categories as down2
    on down2.parentid = down1.id
left outer
  join categories as down3
    on down3.parentid = down2.id
 where root.parentid is null
order 
    by root_name 
     , down1_name 
     , down2_name 
     , down3_name

Now my question is how do I convert this resultset into an nested unordered lists in php ?
This is what I came up with (it doesn't work at all(needs to completely rewritten), but it should give an idea of what I am trying to do ):

echo "<ol>" ;
$r = '';
$r1 = '';
$r2 = '';
while ($row = mysql_fetch_array($result)) {

    if ($r != $row['root_category']  ) {
        $r = $row['root_category'] ;
        echo "<li>{$r}</li><ol>" ;
    }

    if ($r1 != $row['down1_category'] ) {
        $r1 = $row['down1_category'] ;
        echo "<li>{$r1}</li>" ;

    }
    else {
        echo "<ol>" ;
    }
    if ($r2 != $row['down2_category'] ) {
        $r2 = $row['down2_category'] ;
        echo "<li>{$r2}</li>" ;

    }
    elseif ($row['down2_category'] == NULL){}
    else {
        echo "</ol>" ;
    }

}
echo "</ol> " ;

Any help ?

thanks

[kutchbhi]

A bit more googling led me to this solution: http://blog.richardknop.com/2009/05/adjacency-list-model/ . Finally!