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](http://stackoverflow.com/a/192462/623041) for more information. — eggyal, Sep 14 '12 at 21:30
Reading that just made what i'm looking at doing near impossible for my level of knowledge. — Jiggles, Sep 14 '12 at 22:04
@eggyal I thought recursive functions/procedures are supported — Petah, Sep 14 '12 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, Sep 14 '12 at 22:20
hey, will you please share your code if you got also sorting working? thanks! — Kaspar L. Palgi, Aug 13 '16 at 15:15
See the answer to this question bellow. It's the accepted answer. — Jiggles, Aug 14 '16 at 01:16

score 1 · Accepted Answer · answered Sep 27 '12 at 00:53

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

}

Recursive menu with php and MySQLi

1 Answers1

Linked