[dannymitza]

Hello, DIC! I'm trying to populate a dropdown menu using PHP, navigation links are stored in a MySQL Database.

HTML Standard Code:

<ul id="nav" class="dropdown dropdown-vertical">
	<li><a href="./">Home</a></li>
	<li><span class="dir">About Us</span>
		<ul>
			<li><a href="./">History</a></li>
			<li><a href="./">Our Vision</a></li>
			<li><span class="dir">The Team</span>
		</ul>
	<li><a href="./">Clients</a></li>
	<li><a href="./">Testimonials</a></li>
	<li><a href="./">Press</a></li>
	<li><a href="./">FAQs</a></li>
	<li class="divider"><a href="./">More...</a></li>
</ul>

My database table from stored links is structured as:
id - link id
text - link text
link - link adress
parent - 0 if link is not a sub-menu link

I simply dont know how to check if a link have any sub-menu links, and if it does, how to display them properly.

PHP Code:

<?php
require_once("config.php");

$con = mysql_connect($host, $user, $password);
	if(!$con){
		die('Could not connect: ' . mysql_error());
	}
	
mysql_select_db($db, $con);

$create_link = "<ul id='nav' class='dropdown dropdown-vertical'>";
$sel_hnl = mysql_query("SELECT * FROM navigation");
	if(!$sel_hnl || mysql_num_rows($sel_hnl) == 0){
		$error = "1";
	} else {
		while($fet_hnl = mysql_fetch_array($sel_hnl)){
			$id = $fet_hnl['id'];
			$text = $fet_hnl['text'];
			$link = $fet_hnl['link'];
			$pid = $fet_hnl['text'];
			$parent = $fet_hnl['parent'];
		
			if($parent == 0){// Is not sub-link
				$create_link .= "<li><a href='" . $link . "'>" . $text . "</a></li>";
			}
		
			if($parent != 0){ // Is sub-link
				$create_link .= "<li><span class='dir'>" . $text . "</span>";
				$create_link .= "<ul>";
			
				$sel_hnslink = mysql_query("SELECT * FROM navigation WHERE id = '" . $parent . "'");
					if(!$sel_hnslink || mysql_num_rows($sel_hnslink) == 0){
						$error = "1b";
					} else {
							while($fet_hnslink = mysql_fetch_array($sel_hnslink)){
									$sid = $fet_hnslink['id'];
									$stext = $fet_hnslink['text'];
									$slink = $fet_hnslink['link'];
									
									$create_link .= "<li><a href='" . $slink . ">" . $stext . "</a></li>";
							}
					}
			$create_link .= "</ul>";
			}
		}
	}
$create_link .= "</ul>";


echo $create_link;
mysql_close($con);
?>

Any help will be much appreciated!

[StefanOnRails]

Hi dannymitza,

I've encountered the same problem as you some time ago and here's how I solved it (simplified solution):

// First off, I took 2 $arrays
$menus = array();
$submenus = array();
// then I queried my db to get the menus & submenus
$query = mysql_query("SELECT `text`,`link`,`parent` FROM `navigation` ");
// loop through the $query
while($row = mysql_fetch_assoc($query))
    if($row['parent'] == '0') $menus[$row['text']] = array('name' => $row['text'], 'link' => $row['link'], 'subs' => array());
    else $submenus[$row['text']] = array('text' => $row['text'], 'link' => $row['link'], 'parent' => $row['parent']);
// add submenus
foreach($submenus as $sub) $menus[$sub['parent']]['subs'][] = $sub['text']; 
// you can preview your array here:
echo '<pre>', print_r($menus,true), '</pre>';
// create the markup
echo '<ul>';
foreach($menus as $item) 
	if(empty($item['subs'])) echo '<li><a href="', $item['link'] ,'">', $item['name'],'</a></li>';
	else{
		echo '<li><a>',$item['name'],'</a>
				  <ul>';
		foreach($item['subs'] as $subitem) echo '<li><a href="', $submenus[$subitem]['link'],'">', $subitem ,'</a></li>';
		echo '    </ul>
		      </li>';
		}
echo '</ul>';

Be careful, in order to work properly, the parent column for submenus must contain the full name of the parent such as 'about'. Obviously, this method can be improved, but this is how I did it back then. Keep in mind that this should not be executed when the visitor reaches the page, but rather on a page where only the admin can run the script. Also BIG WARNING: mysql_* functions are obsolete and should NOT be used anymore (I used them only in order to fit your example), learn here about PDO and prepared statements.

This post has been edited by StefanOnRails: 22 September 2012 - 05:49 AM

[CTphpnwb]

The biggest obstacle to getting code to "work" is organization. To overcome that, I suggest that you use functions and separate your languages.

[Nullified]

You should have a name for the parent id anyways instead of just having a 1 or 0 to turn it off and on, this way when you parse the array you'll know which parent to place the sub links in. Just in case you have more than one sub menu.

[dannymitza]

Many thanks for replies and advice!

StefanOnRails, on 22 September 2012 - 05:08 AM, said:

Also BIG WARNING: mysql_* functions are obsolete and should NOT be used anymore (...)

CTphpnwb, on 22 September 2012 - 06:51 AM, said:

The biggest obstacle to getting code to "work" is organization. To overcome that, I suggest that you use functions and separate your languages.

Thanks for the advice, I understand that.

Nullified, on 22 September 2012 - 07:00 AM, said:

You should have a name for the parent id anyways instead of just having a 1 or 0 to turn it off and on, this way when you parse the array you'll know which parent to place the sub links in.

I'm not using 0 or 1 that way. 0 is for normal links, while other values defines from which links does other links goes in sub menu.
For example, a link with "parent" = 42, goes in sub-menu from the link with that id (which is unique).

Also, I managed to display sub-menus corectly, but the problem that I'm facing now is that the links which should only show in sub-menus, are displayed as normal links too.

Actual code:

<?php
require("config.php");

$con = mysql_connect($host, $user, $password);
	if(!$con){
		die('Could not connect: ' . mysql_error());
	}
	
mysql_select_db($db, $con);

$create_link = "<ul id='nav' class='dropdown dropdown-vertical'>";


$sel_nav = mysql_query("SELECT * FROM navigation");
	while($fet_nav = mysql_fetch_array($sel_nav)){
		$id = $fet_nav['id'];
		$link = $fet_nav['link'];
		$text = $fet_nav['text'];
		$parent = $fet_nav['parent'];
		
			//Selecting sub-menu links which have actual parent id
			$sel_snav = mysql_query("SELECT * FROM navigation WHERE parent = '" . $id . "'");
				if(mysql_num_rows($sel_snav) == 0){
					$create_link .= "<li><a href='" . $link . "'>" . $text . "</a></li>"; //Normal link
				} else {
				
					$create_link .= "<li><span class='dir'>" . $text . "</span>";
					$create_link .= "<ul>";
					
					while($fet_snav = mysql_fetch_array($sel_snav)){
						$slink = $fet_snav['link'];
						$stext = $fet_snav['text'];
						
							$create_link .= "<li><a href='" . $slink . "'>" . $stext . "</a></li>"; //Sub-menu link
					}
					$create_link .= "</ul>";
				}
	}

	
echo $create_link;
mysql_close($con);
?>

Result:

[Nullified]

I would urge you to not run an sql query within your while statement. By doing so you are querying the db once for every single item in the $sel_nav array. Load times will increase just so you can run 20 sql queries only to fetch your navigation links.

Instead, since you have already pulled everything (*) from the db table just use php to parse the link structure that is already in $sel_nav.

[StefanOnRails]

First of all, I think you forgot to enclose the "<li>" for the "parent links". Also I don't see where you put the matching "</ul>" for the list you declared above ("<ul id='nav'...>").
Now related to your issue, you're seeing the subLinks as normal links because your main while is going through each record and even if it's a subLink it is still appended here:

 $create_link .= "<li><span class='dir'>" . $text . "</span>";

Also your method doesn't link the subLinks to their corespondent parent.
Did you "really" check my method? I run it and it works very well as long as you replace the parent column with the actual parent name (normal links should still have parent = "0").

This post has been edited by StefanOnRails: 22 September 2012 - 08:38 AM

[Nullified]

Working Model: http://www.option9.com/navigation.php

Code:

$connection = mysql_connect($config['server']['addr'], $config['server']['username'], $config['server']['password']);
if(!$connection){die('Could not connect to server: ' . mysql_error());}
$database = mysql_select_db('info', $connection);
if(!$database){die('Could not connect to database: ' . mysql_error());}
$roots = mysql_query("SELECT * FROM links WHERE parent = ".intval(0));
$subs = mysql_query("SELECT * FROM links WHERE parent > ".intval(0));
$sublinks = array();
while ($row = mysql_fetch_array($subs, MYSQL_ASSOC)) {array_push($sublinks, $row);}

$menu = '<ul id="nav" class="dropdown dropdown-vertical">'."\n";
while($link = mysql_fetch_assoc($roots)) {
	if (!empty($link['url'])) {
		$menu .= '	<li><a href="'.$link["url"].'">'.$link["text"].'</a></li>'."\n";
	} elseif (empty($link['url'])) {
		$menu .= '	<li><span class="dir">'.$link["text"].'</span></li>'."\n".'	<ul>'."\n";
		foreach ($sublinks as $sublink) {
			if ($sublink['parent'] == $link['id']) {$menu .= '		<li><a href="'.$sublink["url"].'">'.$sublink["text"].'</a></li>'."\n";}
		}
		$menu .= '	</ul>'."\n";
	}
}
$menu .= '</ul>';

echo $menu."\n";
@mysql_free_result($rootlinks, $sublinks);

[dannymitza]

StefanOnRails, on 22 September 2012 - 08:29 AM, said:

First of all, I think you forgot to enclose the "<li>" for the "parent links". Also I don't see where you put the matching "</ul>" for the list you declared above ("<ul id='nav'...>").
Now related to your issue, you're seeing the subLinks as normal links because your main while is going through each record and even if it's a subLink it is still appended here:

 $create_link .= "<li><span class='dir'>" . $text . "</span>";

Also your method doesn't link the subLinks to their corespondent parent.
Did you "really" check my method? I run it and it works very well as long as you replace the parent column with the actual parent name (normal links should still have parent = "0").

Thanks, the problem was on line #23, and was fixed by adding "parent = '0'" to the query (again, not enough attention).

Nullified, on 22 September 2012 - 10:56 AM, said:

Working Model: http://www.option9.com/navigation.php

Many thanks, I'll try it too!
I'll consider for all your suggestion regarding mysql function, and mixing php with html issue.
I appreciate all of your help.

Thread can be closed from my point of view.

This post has been edited by dannymitza: 22 September 2012 - 12:35 PM

[Nullified]

Just please do keep in mind that you are executing a mysql query in your while statement, thus for every single item (link) the while cycles through you are re-querying the db. I highly recommend you rethink your code. My example uses php to conduct the parent check and only uses 2 queries.

This post has been edited by Nullified: 22 September 2012 - 01:52 PM