Creating a dynamic menu with mysql that child elements

Question

Sorry if my title was off, I'm not exactly sure what the technical term of what I'm trying to do is. With that said, what I'm trying to do is build a accordion style menu that populates from a mysql query. The first part of the menu will simply have a list of team names, which will come from a query that selects all team names and builds a menu:

$result = mysql_query("SELECT * FROM SportsByTeam");


<ul class='nav nav-list'>
<?php
while($row = mysql_fetch_array($result))
  {
?>
<li>
<a href="<? echo $row['seo_team']?>"> <?php echo $row['team'] ?> </a>
</li>
<?php
} ?>

This works perfectly for the top level of the menu I'm trying to build. What I'm completely stuck on is how to now incorporate the accordion (bottom level) of the menu. What I want to happen is when someone clicks on a team generated by the query above, a list of the players that belong to that team will drop down. To be clear, I'm not so much asking how to create an accordion menu from an html/css standpoint, I'm asking how to create a dynamic accordion menu based on a mysql query. The query I came up with for the this part is:

SELECT PlayerByTeam.team_name AS TeamName, SportsByTeam.seo_team, PlayerByTeam.first_name, PlayerByTeam.last_name

FROM PlayerByTeam

inner join SportsByTeam ON PlayerByTeam.team_name = SportsByTeam.team

But that's as far as I can get. How do I incorporate the results of the two queries into one dynamic menu? That looks like:

<ul>
<li> Team Name 1 </li>
<ul>
<li> Player Name 1 </li>
<li> Player Name 2 </li>
</ul>
<li> Team Name 2 </li>
..etc
</ul>

(I really appreciate all of your help. SO has helped me learn so much. I hope to one day soon be able to give back to the community.)

I can give you some hint: This is one of the question that is asked more often on this website. For your two leveled menu I suggest you do a query of all data, sort it by the first level (like with the join). Then the result is processed within PHP to pack this together. I have some code on my disk and there was a bounty question that was related not far ago: [Displaying a table in PHP with repeated columns](http://stackoverflow.com/q/11528436/367456) — hakre, Aug 05 '12 at 17:21
Please, don't use `mysql_*` functions to write new code. They are no longer maintained and the community has begun [deprecation process](http://goo.gl/KJveJ). See the *[red box](http://goo.gl/GPmFd)*? Instead you should learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide which, [this article](http://goo.gl/3gqF9) will help you. If you pick PDO, [here is good tutorial](http://goo.gl/vFWnC). — Madara's Ghost, Aug 05 '12 at 17:34

score 0 · Answer 1 · edited May 23 '17 at 12:04

You can select all players and order them by their team. Then each time the team name changes, a new element needs to be done.

Some pseudo-code:

while($row = get_row()) {
    if (team_has_changed($row)) {
        if (there_was_a_previous_team()) {
            end_previous_team();
        }
        start_new_team();
    }
    process_team_member($row);
}    
if (there_was_a_previous_team()) {
    end_previous_team();
}

This quite expressive code with a while loop I have outlined in some previous answer, you can find it here: https://stackoverflow.com/a/11616884/367456

You can then wrap this kind of grouping into some iterator component that allows you some simple usage (you should use Mysqli or PDO here as they have results you can iterate over). I demonstrated that in a previous answer which already has some code examples: https://stackoverflow.com/a/11619281/367456

It simplifies the problem by providing an iterator per each level while in the beginning you just have queried the database and get one result from it.

Creating a dynamic menu with mysql that child elements

1 Answers1