[SOLVED - ISH] Converting adjacency hierarchy to nested list

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
andym01480
Forum Contributor
Posts: 390
Joined: Wed Apr 19, 2006 5:01 pm

[SOLVED - ISH] Converting adjacency hierarchy to nested list

Post by andym01480 »

I'm wanting to use adjacency hierarchy to store a website menu that is max 3 levels deep.

Code: Select all

CREATE TABLE `menu` (
  `title` varchar(255) NOT NULL,
  `parent` int(11) NOT NULL,
  `menu_id` int(11) NOT NULL AUTO_INCREMENT,
  `pagename` varchar(255) NOT NULL,
  `popup` int(1) NOT NULL,
  PRIMARY KEY  (`menu_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=53 ;
I've created functions to add, edit, delete and so on but am struggling to convert it to a nested list for a vertical popout menu that is standards compliant! I've got it to work but it doesn't validate.

I'm now trying this approach...

Code: Select all

<?php 
 
$sql="select root.title  as root_title
     , down1.title as down1_title
     , down2.title as down2_title,root.menu_id as root_id
 
  from menu as root
left outer
  join menu as down1
    on down1.parent = root.menu_id
left outer
  join menu as down2
    on down2.parent = down1.menu_id
 
 where root.parent ='0'
order 
    by root_id 
     , down1_title 
     , down2_title 
";
$result=mysql_query($sql) OR DIE(mysql_error());
echo "<table>";
echo "<tr><th>root_title</th><th>down1_title</th><th>down2_title</th></tr>";
 
while($row=mysql_fetch_assoc($result))
{
echo "<tr><td>{$row['root_title']}</td><td>{$row['down1_title']}</td><td>{$row['down2_title']}</td></tr>";
}
echo "</table>"
?>
 
which gives a great table for testing but doesn't get me nearer the magic nested list.
Any ideas?
User avatar
andym01480
Forum Contributor
Posts: 390
Joined: Wed Apr 19, 2006 5:01 pm

Re: [SOLVED - ISH] Converting adjacency hierarchy to nested list

Post by andym01480 »

Couldn't do it as previously posted, couldn't work out an iterative way either. But as the menu is only going to be 3 deep at most, here's how I solved it. Posted here as a Google search has no code for the problem!

Code: Select all

 
<?php 
echo '<div id="menu">';
//select top level
$result1=mysql_query("SELECT * FROM menu WHERE parent='0' ORDER BY menu_id");
while($row1=mysql_fetch_assoc($result1))
    {
        echo "\r\n<ul>\r\n\t<li><h2>{$row1['title']}</h2>\r\n";
        //grab layer 2 for that parent
        $sql2="SELECT * FROM menu WHERE parent={$row1['menu_id']}";
        $result2=mysql_query($sql2);
        if (mysql_num_rows($result2)>0)
        {
            echo "\t\t<ul>\r\n";
            while($row2=mysql_fetch_assoc($result2))
                {
                    echo "\t\t\t<li><a href=\"index.php?page={$row2['pagename']}\">{$row2['title']}</a>\r\n";
                    //grab layer 3 for that parent
                    $sql3="SELECT * FROM menu WHERE parent={$row2['menu_id']}";
                    $result3=mysql_query($sql3);
                    if(mysql_num_rows($result3)>'0')
                        {
                            echo "\t\t\t\t<ul>\r\n";
                            while($row3=mysql_fetch_assoc($result3))
                                {
                                    echo "\t\t\t\t\t<li><a href=\"index.php?page={$row3['pagename']}\">{$row3['title']}</a></li>\r\n";
                                }
                            echo "\t\t\t\t</ul>\r\n";   
                        }
                    echo "\t\t\t</li>\r\n"; 
                }
                echo "</ul>\r\n";
        }
echo "</li></ul>";      
 
    }       
 
    
echo '</div>';
?>
 
Is there a more efficient way of doing it?
Post Reply