Drop down <li> menu

jiggles · September 16, 2012, 2:02am

Hi everyone! Been hitting my head against the wall with this one for a week now and still cant get my head around it. My knowledge with PHP is good in some areas but lacking in others. Working on it though. I am trying to make a dynamic menu from a database of a CMS I made. The info is bellow.

I have the following code:

[php] 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>'[/php]

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 there are other columns page content etc but those aren’t needed in this:

[php] 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
[/php]

Here is the HTML structure that hopefully should be outputted!:

[php]

Home

Examples

A Page

Services
- Diving
- Angling
- Charter

Contact Us

[/php]

If you need more info/ code I can post if for you.

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. I will try and help others if I can.

jiggles · September 21, 2012, 8:58pm

Anyone help? =/

malasho · September 21, 2012, 11:33pm

jiggles,

I believe the issue you are experiencing is related to a misspelled column name. Your query uses “visible,” but your table example uses “visable.”

Your function calls itself every time a menu has a child. This means that you are querying the database multiple times. There may be more to your needs, but based on what you have posted, I would do this instead:[php]function menu()
{
global $dbc;

  $result = $dbc->prepare('SELECT page, linktext, visable, parent FROM content ORDER BY parent,sort ASC');
  $result->execute();
  $result->bind_result($menu_page, $menu_linktext, $menu_visible, $menu_parent);
  
  while($result->fetch())
    {
        $menu[$menu_parent][]=array('page'=>$menu_page,'link'=>$menu_linktext,'visible'=>$menu_visible);
    }

  $result->close();
  
  if(!empty($menu))
    {
        echo '<ul class="sf-menu" id="nav"<br />';
        foreach($menu as $parent=>$temp)
          {
              foreach($temp AS $i=>$data)
                {
                    if(isset($menu[$parent][$i]['used'])) break;
              
                    echo '<li><a href="',$data['link'],'">',$data['link'],'</a></li>';
              
                    if(!empty($menu[$data['page']]))
                      {
                          echo '<ul>';
                          foreach($menu[$data['page']] AS $index=>$sub)
                            {
                                $menu[$data['page']][$index]['used'] = 1;
                                echo '<li><a href="#">',$menu[$data['page']][$index]['link'],'</a></li>';
                            }
                          echo '</ul>';
                      }
                }
          }
        echo '</ul>';
    }

}
?>[/php]

Note that I included the misspelling of “visible” in the query. If you fix the column name in your table, you will need to update this.

Let me know if this doesn’t work for you…

jiggles · September 22, 2012, 9:15pm

malasho, Thanks for the help but that sadly doesn’t show anything. No errors or anything.

malasho · September 22, 2012, 9:33pm

Weird, I setup a test table based on the one you provided and the code I posted worked perfectly.

Lets setup a test. See what happens when you execute the following:
[php]<?php
$dbc = new mysqli(“localhost”, “my_user”, “my_password”, “my_database”);
$result = $dbc->prepare('SELECT page FROM content');
$result->execute();
$result->bind_result($menu_page);
while($result->fetch())
{
echo “-- $menu_page
”;
}
$result->close();
$dbc->close();
?>[/php]

Obviously you will need to substitute the correct connection parameters on the first line.

If this successfully outputs, make sure that the other column names in your database precisely match the code. Also make sure that your are establishing $dbc as a new mysqli object before calling the menu function.

Let me know…

jiggles · September 22, 2012, 10:05pm

Chucks out:

-- 1 -- 2 -- 3 -- 4 -- 5 -- 6 -- 7 -- 8 -- 9 -- 10 -- 11

jiggles · September 22, 2012, 10:20pm

Scratch that, I put all that code into a test file and it works perfect! ALthough it ignores whether the link is visible or not. It means there is a possible problem with a connection to my DB. The thing is this works with out issue:

[php]$result = $dbc->prepare(‘SELECT linktext, visible, sort FROM content ORDER BY sort’);
$result->execute();
$result->bind_result($menu_linktext, $menu_visible, $menu_sort);
while ($row = $result->fetch()) {
// If the link is visible create the link
if ($menu_visible == ‘1’) {
$menu_linktext_ = str_replace(’ ‘,’_’,$menu_linktext);
echo “

$menu_linktext

”;
}
}[/php]

jiggles · September 22, 2012, 10:39pm

Right I have it working on the real web page! Although I have noticed a error with how the HTML is built. The parent that has children should have all its children UL wrapped in its LI tag. like so:

[php]

Example Drop Down

Drop Down One

Drop Down Two

[/php]

Plus im trying to get my head round the visibility of the pages. Thanks for the help so far.

malasho · September 22, 2012, 11:34pm

EDIT: FOUND AN ISSUE AND I AM FIXING IT, IGNORE THE REST OF THIS POST FOR THE MOMENT…

OK,

See if this is what you are looking for:[php]function menu()
{
global $dbc;

  $result = $dbc->prepare('SELECT page, linktext, visable, parent FROM content ORDER BY parent,sort ASC');
  $result->execute();
  $result->bind_result($menu_page, $menu_linktext, $menu_visible, $menu_parent);
  
  while($result->fetch())
    {
        $menu[$menu_parent][]=array('page'=>$menu_page,'link'=>$menu_linktext,'visible'=>$menu_visible);
    }

  $result->close();
  
  if(!empty($menu))
    {
        echo '<ul class="sf-menu" id="nav"<br />';
        
        foreach($menu as $parent=>$temp)
          {
              foreach($temp AS $i=>$data)
                {
                    if(isset($menu[$parent][$i]['used']) || empty($data['visible'])) continue;
              
                    echo '<li><a href="',$data['link'],'">',$data['link'],'</a>';
              
                    if(!empty($menu[$data['page']]))
                      {
                          echo '<ul>';
                          foreach($menu[$data['page']] AS $index=>$sub)
                            {
                                if($sub['visible'] != 0)
                                  {
                                      $menu[$data['page']][$index]['used'] = 1;
                                      echo '<li><a href="#">',$menu[$data['page']][$index]['link'],'</a></li>';
                                  }
                            }
                          echo '</ul>';
                      }
                    echo '</li>';
                }
          }
        echo '</ul>';
    }

}[/php]
There was nothing in your original post about how to handle the visible column, and it wasn’t being used in your original code, so I ignored it. I took a guess here, but let me know if it needs to be handled differently.

I changed the output so that the children are wrapped in the parent

.

Let me know (fingers crossed)…

jiggles · September 23, 2012, 12:01am

It works but the child links dont appear! =/ I have tried setting it to == 1 but nothing. removing that IF statement makes it work.

malasho · September 23, 2012, 12:25am

Sorry,

Lets give this a try…[php]function menu()
{
global $dbc;

  $result = $dbc->prepare('SELECT page, linktext, visable, parent FROM content ORDER BY parent,sort ASC');
  $result->execute();
  $result->bind_result($menu_page, $menu_linktext, $menu_visible, $menu_parent);
  
  while($result->fetch())
    {
        if(empty($menu_visible) || ($menu_parent > 0 && empty($menu[0][$menu_parent]['visible']))) continue;
        $menu[$menu_parent][$menu_page]=array('page'=>$menu_page,'link'=>$menu_linktext,'visible'=>$menu_visible);
    }

  $result->close();
  
  if(!empty($menu))
    {
        echo '<ul class="sf-menu" id="nav"<br />';
        
        foreach($menu as $parent=>$temp)
          {
              foreach($temp AS $i=>$data)
                {
                    if(isset($menu[$parent][$i]['used']) || empty($data['visible'])) continue;
              
                    echo '<li><a href="',$data['link'],'">',$data['link'],'</a>';
              
                    if(!empty($menu[$data['page']]))
                      {
                          echo '<ul>';
                          foreach($menu[$data['page']] AS $index=>$sub)
                            {
                                if($sub['visible'] != 0)
                                  {
                                      $menu[$data['page']][$index]['used'] = 1;
                                      echo '<li><a href="#">',$menu[$data['page']][$index]['link'],'</a></li>';
                                  }
                            }
                          echo '</ul>';
                      }
                    echo '</li>';
                }
          }
        echo '</ul>';
    }

}[/php]

I can’t help but think I am making this too complicated! I think I’ll try again from scratch tomorrow - sometimes when you start over, a better solution becomes obvious…

Let me know if the above fails to function correctly for you

jiggles · September 23, 2012, 12:45am

Its made it worse. All I get is the parents now. I would try and work out what could be wrong but this kind of code is well out of my knowledge. I’m very grateful for the help so far!

malasho · September 23, 2012, 1:12am

Sorry, I have started over with a different approach. I’ll post shortly.

malasho · September 23, 2012, 1:27am

OK,

I took a totally different approach. This seems to be working well for me, but I’m watching football and I’ve had a few beers so who knows ;D

[php]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>';
      }

}[/php]

jiggles · September 23, 2012, 2:13am

Sadly the child elements aren’t loading with that code. I even added {} after the [php]foreach($sub[$page] as $lnk)[/php] section. :’(

malasho · September 23, 2012, 2:28am

I don’t get it, both versions are working for me. I may be misunderstanding how you want the visibility to work.

With this table[php]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 1 5 2
8 Charters 0 5 3[/php]

I get this:Home About Us Gallery Services Angling Contact Us

And with this table:
[php]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 0 0 4
6 Diving 0 5 1
7 Angling 1 5 2
8 Charters 0 5 3[/php]

I get this:Home About Us Gallery Contact Us

Can you confirm whether this is what you are expecting or not.

malasho · September 23, 2012, 2:50am

If the results I posted are what you want, but not what you are getting, try creating a test page with the following. Please use it exactly as is with exception of the db connection info. You will note that I added two rawurlencodes to ensure html validation and you will want to include these in your final code - these should not affect the actual results though.[php]

Menu Builder <?php 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)
          {
              $link1 = rawurlencode($link);
              echo "<li><a href='$link1'>$link</a>";
              if(!empty($sub[$page]))
                {
                    echo '<ul>';
                    foreach($sub[$page] as $lnk)
                      {
                          $lnk1 = rawurlencode($link);
                          echo "<li><a href='$lnk1'>$lnk</a></li>";
                      }
                    echo '</ul>';
                }
              echo '</li>';
          }
        echo '</ul>';
      }

}

$dbc = new Mysqli(“localhost”, USER, PASS, TABLE);

menu();
?>

[/php]

jiggles · September 23, 2012, 10:19pm

The table should be:

[php]
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 1 5 1
7 Angling 1 5 2
8 Charters 1 5 3[/php]

I dont know why I posted it without all the pages not visible.

The code you posted above I get this as the output:

Home
About Us
Services
Gallery
Terms
Contact Us

When I want:

Home
About Us
Services
      Diving
      Angling
      Charter  
Gallery
Terms
Contact Us

This works perfectly appart from the Visiblity issue.

[php]function menu()
{
global $dbc;

  $result = $dbc->prepare('SELECT page, linktext, visable, parent FROM content ORDER BY parent,sort ASC');
  $result->execute();
  $result->bind_result($menu_page, $menu_linktext, $menu_visible, $menu_parent);
  
  while($result->fetch())
    {
        $menu[$menu_parent][]=array('page'=>$menu_page,'link'=>$menu_linktext,'visible'=>$menu_visible);
    }

  $result->close();
  
  if(!empty($menu))
    {
        echo '<ul class="sf-menu" id="nav"<br />';
        
        foreach($menu as $parent=>$temp)
          {
              foreach($temp AS $i=>$data)
                {
                    if(isset($menu[$parent][$i]['used']) || empty($data['visible'])) continue;
              
                    echo '<li><a href="',$data['link'],'">',$data['link'],'</a>';
              
                    if(!empty($menu[$data['page']]))
                      {
                          echo '<ul>';
                          foreach($menu[$data['page']] AS $index=>$sub)
                            {
                                if($sub['visible'] != 0)
                                  {
                                      $menu[$data['page']][$index]['used'] = 1;
                                      echo '<li><a href="#">',$menu[$data['page']][$index]['link'],'</a></li>';
                                  }
                            }
                          echo '</ul>';
                      }
                    echo '</li>';
                }
          }
        echo '</ul>';
    }

}[/php]

jiggles · September 23, 2012, 10:29pm

I shall now hang my head in shame! The menu items for some reason had became visible = 0 and so didn’t appear! Im not sure how that would of happend. It all working perfectly and explains the reason why i would work for you and not me. Thanks very much!

malasho · September 23, 2012, 11:00pm

That is really strange. Here is the output that I get:Home About Us Gallery Services Diving Angling Charters Contact Us

Note that the order is different than you posted, but matches the table with regard to the linktext and sort order.

Just so I am certain, did you use the code I posted in its entirety and by itself. (Every thing from the through to the final tag with nothing else on the page? If not, would you do that and let me know what happens. (You will still need to modify the database connection line).