Recursive menu with php and MySQLi

Question

I have the following code:

function menu($parent, $level){
global $dbc;

$result = $dbc->prepare('SELECT linktext, visible, sort FROM content WHERE parent =? ORDER BY sort');
$result->bind_param('s', $parent);
$result->execute();
$result->bind_result($menu_linktext, $menu_visible, $menu_sort);  
$total_records = $result->num_rows;

if($level > 0 && $total_records > 0){
echo '<ul>';
}
while($row = $result->fetch()){
echo "<li>";
echo '<a href="?page=' . $menu_linktext . '">' . $menu_linktext . '</a>'.$id;
//display this level's children
menu($id, $level+1);
echo "</li>\n";
}
if($level > 0 &&  $total_records > 0){
echo '</ul>';
 }
}
echo '<ul>' . menu(0,0) . '</ul>'

It works for one link (Home) then throws out a Call to a member function bind_param() on a non-object error.

The basics of the table is:

page | linktext | visable | parent | sort
  1       Home       1         0      1
  2      Gallery     1         0      3
  3     About Us     1         0      2
  4    Contact Us    1         0      5
  5     Services     1         0      4
  6     Diving       0         5      1
  7     Angling      0         5      2
  8     Charters     0         5      3

Here is the HTML structure:

 <ul class="sf-menu" id="nav">
          <li><a href="index.html">Home</a></li>
          <li><a href="about.html">Examples</a></li>
          <li><a href="gallery.html">A Page</a></li>
          <li><a href="#">Services</a>
            <ul>
              <li><a href="#">Diving</a></li>
              <li><a href="#">Angling</a>
              <li><a href="#">Charter</a></li>
            </ul>
          </li>
          <li><a href="contact.html">Contact Us</a></li>
        </ul>

I want to get the basic menu working then work on the sort order of the links.If anyone can help it would be very much appreciated.

MySQL doesn't support recursive functions, so it is not well suited to this adjacency list model of storing hierarchical data. You ought to consider restructuring your data to use either nested sets or a transitive closure table. See this answer for more information. — eggyal
– eggyal, Commented Sep 14, 2012 at 21:30
Reading that just made what i'm looking at doing near impossible for my level of knowledge. — CallumBee
– CallumBee, Commented Sep 14, 2012 at 22:04
@eggyal I thought recursive functions/procedures are supported — Petah
– Petah, Commented Sep 14, 2012 at 22:19
@Petah: Recursive procedures up to a fixed maximum depth (default zero), yes. But functions, no. And one would really want to use a function here e.g. WHERE is_descendent(node.id, 123). — eggyal
– eggyal, Commented Sep 14, 2012 at 22:20
hey, will you please share your code if you got also sorting working? thanks! — Kaspar L. Palgi
– Kaspar L. Palgi, Commented Aug 13, 2016 at 15:15

CallumBee · Accepted Answer · 2012-09-27 00:53:19Z

Problem solved! I will post how it was done and someone may be able to use it.

function menu()
  {
    global $dbc;

  $result = $dbc->prepare('SELECT page, linktext, visable, parent FROM content WHERE visable > 0 ORDER BY parent,sort ASC');
  $result->execute();
  $result->bind_result($menu_page, $menu_linktext, $menu_visible, $menu_parent);

  while($result->fetch())
    {
        if($menu_parent == 0) $menu[$menu_page]=$menu_linktext;
        elseif(!empty($menu[$menu_parent])) $sub[$menu_parent][]=$menu_linktext;
    }

  $result->close();

  if(!empty($menu))
    {
        echo '<ul class="sf-menu" id="nav">';
        foreach($menu as $page=>$link)
          {
              echo "<li><a href='$link'>$link</a>";
              if(!empty($sub[$page]))
                {
                    echo '<ul>';
                    foreach($sub[$page] as $lnk) echo "<li><a href='$lnk'>$lnk</a></li>";
                    echo '</ul>';
                }
              echo '</li>';
          }
        echo '</ul>';
      }

}

Collectives™ on Stack Overflow

Recursive menu with php and MySQLi

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related