how to create dynamic menu with sub-menu with php & mysql

Question

I want to have a dynamic menu that feeds from a table using php and mysql.

My table looks like this:

sec_id  sec_name    sec_group
1   section 1   group 1
2   section 2   group 1
3   section 3   group 2
4   section 4   group 1
5   section 5   group 3

I can do a query to get and display unique sec_group values but can't figure out a way to include sec_name into each sec_group

//Query by unique sec_group
$qry_secs="SELECT DISTINCT sec_group FROM tbl_user_sec ORDER BY sec_id ASC";
$result_secs = mysql_query($qry_secs);

//echo values
while($row_secs = mysql_fetch_assoc($result_secs)){
 echo '<ul><li><a href="#">'.$row_secs['sec_group'].'</a></li></ul>';
}

Eventually, the HTML should like the code below.

<ul>
<li><a href="#">Group 1</a>
  <ul>
  <li><a href="#">Section 1</a></li>
  <li><a href="#">Section 2</a></li>
  <li><a href="#">Section 4</a></li>
  </ul>
</li>

<li><a href="#">Group 2</a>
  <ul>
  <li><a href="#">Section 3</a></li>
  </ul>
</li>

<li><a href="#">Group 3</a>
  <ul>
  <li><a href="#">Section 5</a></li>
  </ul>
</li>
</ul>

any ideas?

@PeterSzymkowski I respected the OP's wish to have ORDER BY sec_id ASC. If-you-know-what-I-mean. :) — hjpotter92
– hjpotter92, Commented Sep 2, 2012 at 22:09
@Nerd-Herd ok updated my answer. Still no need to use nested queries which makes groups qty*queries to db — Peter
– Peter, Commented Sep 2, 2012 at 22:12

Peter · Accepted Answer · 2012-09-02 22:12:02Z

3

$q = mysql_query("SELECT sec_id, sec_name, sec_group FROM tbl_user_sec ORDER BY sec_id");

// prepare data 
$groups = Array();
while($w = mysql_fetch_assoc($q)) {
  if(!isset($groups[$w['sec_group']])) $groups[$w['sec_group']] = Array();
  $groups[$w['sec_group']][] = $w;
}

// display data
echo "<ul>";
foreach($groups as $group_name => $sections) {
  echo '<li><a href="#">'.$group_name.'</a><ul>';
  foreach($sections as $section) {
    echo '<li><a href="#">'.$section['sec_name'].'</a>';
  }
  echo '</ul></li>';
}
echo "</ul>";

There is another solution if you don't care about sorting result by sec_id

edited Sep 2, 2012 at 22:12

answered Sep 2, 2012 at 22:05

Peter

17k9 gold badges55 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

hjpotter92 · Accepted Answer · 2012-09-02 22:00:58Z

0

$qry_secs="SELECT DISTINCT sec_group FROM tbl_user_sec ORDER BY sec_id ASC";
$result_secs = mysql_query($qry_secs);
echo '<ul>\n';
//echo values
while($row_secs = mysql_fetch_assoc($result_secs)) {
    echo '<li><a href="#">'.$row_secs['sec_group'].'</a></li>\n';
    echo '<ul>\n';
    $newqry = "SELECT sec_name FROM tbl_user_sec WHERE `sec_group` = '" . mysql_real_escape_string($row_secs['sec_group'] . "'";
    $result = mysql_query($newqry);
    while($row = mysql_fetch_assoc($result) ) {
        echo '<li><a href="#">' . $row['sec_name'] . '</li>\n';
    echo '</ul>\n';
}
echo '</ul>\n';

answered Sep 2, 2012 at 22:00

hjpotter92

81.1k36 gold badges148 silver badges188 bronze badges

Collectives™ on Stack Overflow

how to create dynamic menu with sub-menu with php & mysql

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related