Populating Drop Down Menu from PHP/MYSQL Select Values. How to rearrange output

Question

I'm going to go ahead and show how the current HTML output looks and in the same fiddle below is how I wish the HTML to be output: http://jsfiddle.net/xQ95X/3/

Everything works great, I just need this HTML structure for the jQuery plugin (Superfish) to be function. The dropdown won't work with the child UL's outside of the LI's. They must be nested inside.

The part which is stopping me from formatting the HTML in this manner is below:

  <ul class="sf-menu">

    <li><a href="#">HOME</a></li>

    <?php

    $sql="SELECT * FROM section ORDER BY orderID";
    $result=mysql_query($sql) or die ("Error!! BAD SELECT SEARCH STATEMENT");
    $nrows = mysql_num_rows($result);

    // Top level Items

    for($i=1;$i<=$nrows;$i++) {
      $row = mysql_fetch_array($result);
      extract($row);

      $sections[] = $section;
      $sectionids[] = $sectionid;

      if ($section == "FORUM") {
        echo"<li><a href='#' rel='dropmenu$i'>$section</a></li>"; 
      } else {
        echo"<li><a href='#h' rel='dropmenu$i'>$section</a></li>"; 
      }
    }

    // Submenu Items


    for($s=0;$s<count($sections);$s++) {

      /*$sm1 = ($s - 1);*/
      $sp1 = ($s + 1);

      $sql3="SELECT * FROM category WHERE sectionid='$sectionids[$s]'";
      $result3=mysql_query($sql3) or die ("Error!! BAD SELECT SEARCH STATEMENT");
      $nrows3 = mysql_num_rows($result3);

      if ($nrows3 > 0) {
        echo"<ul>";
        for($t=0;$t<$nrows3;$t++) {
          $row3 = mysql_fetch_array($result3);
          extract($row3);

          if ($type=='Photo') {
            $title20 = strtolower($title);
            $title20 = str_replace(" ", "_", $title20);
            $sec = strtolower($sections[$s]);
            $sec = str_replace(" ", "_", $sec);
            if ($sec=='photos') {
              echo"<li><a href='" . dirname($_SERVER['PHP_SELF']) . "$sec/$catID-$title20/1.html'>$title</a></li>";
            }

            else {
              echo"<li><a href='" . dirname($_SERVER['PHP_SELF']) . "$sec/photos/$catID-$title20/1.html'>$title</li></a>";
            }

          } elseif ($type=="Contact") {
            $title20 = strtolower($title);
            $title20 = str_replace(" ", "_", $title20);
            echo "<li><a href='" . dirname($_SERVER['PHP_SELF']) . "contactus/contact_$title20.html'>$title</a></li>";
          } elseif ($type=='Quote') {

            $sql4="SELECT articleID FROM article WHERE category='49' AND Now() > publishingdate ORDER BY publishingdate DESC LIMIT 0, 1";
            $result4=mysql_query($sql4) or die ("Error! bad select statement");
            $row4=mysql_fetch_array($result4);
            extract($row4);

            echo "<li><a href='" . dirname($_SERVER['PHP_SELF']) . "everton_quotes.php?id=$articleID'>$title</a></li>";
          } 
          elseif ($type=='LastMatch') {
            $sql4="select r.matchID as matchID2, r.venue, r.versus as versus2, s.year FROM regmatch r, season s WHERE r.season=s.seasonID AND Now() > r.matchdate ORDER BY r.matchdate DESC LIMIT 0,1";
            $result4=mysql_query($sql4) or die ("Error! bad select statement");
            $nrows4=mysql_num_rows($result4);
            if ($nrows4 > 0) {
              $row4=mysql_fetch_array($result4);
              extract($row4);
              if ($venue=='Home') {
                $teams = "Everton V $versus2";
                $teams2 = "everton_vs_" . str_replace(" ", "_", strtolower($versus2));
              }
              else {
                $teams = "$versus V Everton";
                $teams2 = str_replace(" ", "_", strtolower($versus2)) . "_vs_everton";
              }

              $year = str_replace("/", "-", $year);
              echo "<li><a href='" . dirname($_SERVER['PHP_SELF']) . "match/$year/$matchID2-$teams2/" . strtolower($venue) . "/matchreport.html'>$title</a></li>";
            }
          } elseif ($type=='NextMatch') {

            $sql5="select r.matchID as matchID3, r.venue, r.versus as versus2, s.year FROM regmatch r, season s WHERE r.season=s.seasonID AND r.matchdate > Now() ORDER BY r.matchdate ASC LIMIT 0,1";
            $result5=mysql_query($sql5) or die ("Error! bad select statement");
            $nrows5=mysql_num_rows($result5);
            if ($nrows5 > 0) {
              $row5=mysql_fetch_array($result5);
              extract($row5);
              if ($venue=='Home') {
                $teams = "Everton V $versus2";
                $teams2 = "everton_vs_" . str_replace(" ", "_", strtolower($versus2));
              }
              else {
                $teams = "$versus2 V Everton";
                $teams2 = str_replace(" ", "_", strtolower($versus2)) . "_vs_everton";
              }

              $year = str_replace("/", "-", $year);
              echo "<li><a href='" . dirname($_SERVER['PHP_SELF']) . "match/$year/$matchID3-$teams2/" . strtolower($venue) . "/teamnews.html'>$title</a></li>";
            }
          } else {

    // Overides for missing pages

            if($title == "Gwladys Street Preacher") {
              echo"<li><a href='/fans/121-gwladys_street_preacher/index.html'>$title</a></li>";
            } 

            elseif($title == "The Secret Fan") {
              echo"<li><a href='/news/124-secret-fan/index.html'>$title</a></li>";
            } 

            elseif($title == "Toffee Girl") {
              echo"<li><a href='/fans/125-toffee_girl/index.html'>$title</a></li>";
            } 

            else {

              echo"<li><a href='" . dirname($_SERVER['PHP_SELF']) . $link . "'>$title</a></li>";

            }

          }

        }

        echo"</ul>";
      }

    }

    echo "</ul>";

    ?>

I hope I have provided enough information, let me know if you require anything else. Though the JSFiddle should give you a good idea of how I would like the HTML to be output.

PS - I'm fully aware this isn't the cleanest code around, my guess is that it will need a fair amount of rearranging to get the desired output. I'm open to suggestions.

Can you put some sample data out in sqlfiddle? – gwc Nov 20 '13 at 22:33 — gwc, Nov 20 '13 at 22:33

ModulusJoe · Accepted Answer · 2013-10-25T10:28:02.197

4

I would personally nest the second for loop (which I have changed to while loops) in the firsts (and this is very much rough code) but notice in the parent loop the has been removed from the section title.:

Some pseudo code:

print parent unordered list start
get section data
loop through section results
    print parent list item start
    print section title
    get page data
    print unordered child list start
    loop through page results
         print child list item start
         print page info
         print child list item end
    print child unordered list end 
    print parent list item end
print parent unordered list end

And from pseudo code to something close to your code:

$sql="SELECT * FROM section ORDER BY orderID";
$result=mysql_query($sql) or die ("Error!! BAD SELECT SEARCH STATEMENT");

// Top level Items

while($row = mysql_fetch_array($result){
    extract($row);

    if ($section == "FORUM") echo"<li><a href='#' rel='dropmenu$i'>$section</a>"; 
    else echo"<li><a href='#h' rel='dropmenu$i'>$section</a>"; 

    $sql3="SELECT * FROM category WHERE sectionid='$sectionid'";
    $pageResult=mysql_query($sql3) or die ("Error!! BAD SELECT SEARCH STATEMENT");

    echo "<ul>";
    while($page = mysql_fetch_array($pageResult){
       extract($page);
       //process and print each child <li>...</li> here 
       //(this is a cut down example) where you would add your if/elseif section
       echo "<li>$title</li>";
    }
    echo "</ul>"; //close child UL
    echo "</li>"; //close parent LI
}

edited Oct 25 '13 at 10:28

answered Oct 23 '13 at 15:42

ModulusJoe

1,416
10
17

Can't get this to print anything, blank page. Hmm... Even if I just use it at it's most minimum code. – rowefx Oct 25 '13 at 09:34
I've literally tried entering your code, but it returns nothing. I thought even without printing the child
's it would have returned the top level
's ?

rowefx

Oct 25 '13 at 09:52

Thanks for the help so far. I'm having trouble getting your framework to display anything, could you point me in the right direction to get this print the top level items? – rowefx Oct 25 '13 at 10:05

I'm starting to understand what you've done a little better, though your code isn't printing anything just yet :) – rowefx Oct 25 '13 at 10:18

I'm trying to replicate this code you've given me, is there anything that I'm missing for it to at least print the top level items? I understand this block of code, but for some reason it is just not returning even the top level items. – rowefx Nov 19 '13 at 15:42

I'm actually getting this to work now, my syntax was off on the while statements that's why I wasn't getting anything to print. Very appreciative to what you have given me here! – rowefx Nov 19 '13 at 16:22

This answer was the one that helped me the most. Thanks very much! – rowefx Nov 25 '13 at 13:49

Alexander Kimaru · Answer 2 · 2013-11-18T20:23:36.877

I think you need to nest the second for loop within the first one so that the ul tags are nested within the li: A general guideline would be:

   for($i=1;$i<=$nrows;$i++) {
          $row = mysql_fetch_array($result);
          extract($row);

          $sections[] = $section;
          $sectionids[] = $sectionid;

          if ($section == "FORUM") {
            echo"<li><a href='#' rel='dropmenu$i'>$section</a>";
            for($s=0;$s<count($sections);$s++) { //Filter $sections for FORUM submenu items only
           //submenu items
            } 
            echo "</li>"; 
          } else {
            echo"<li><a href='#h' rel='dropmenu$i'>$section</a>";
            for($s=0;$s<count($sections);$s++) { 
           //submenu items
            }
            echo </li>"; 
          }
        }

Groovepig · Answer 3 · 2013-11-19T04:49:49.613

I ran into this same problem developing our company's site navigation, what provided the best scalable solution was what's called 'The Nested Set Model.'

Mike hiller's excellent post here:

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

explains all and gives a great summary of the nested set model. It should be a great place to start.

It allows you to put all of the complexity into your database queries and keep both your database tables and your php clean.

Basically, it sets up a, say, 'categories' table in the database and uses a value, left and right to relate to each other and define their hierarchy and relationship.

And yes, just one table... No 'subcategories' table or anything....they are all equal and on one table. Again, what defines their placement and hierarchy is their left (lft) and right (rgt) value.

It walks you through how to structure the db table and how your site's categories and sub-categories are laid out. This has scaled very well for me and everytime I find myself having to add a category, all I do is add it to the categories table and change the left and right values to fit the new category in.

Hope this is as much of a help to you as it was to me!

Deepika Janiyani · Answer 4 · 2013-11-20T04:56:23.783

You need to enter your second select statement inside

  if ($section == "FORUM") {
    echo"<li><a href='#' rel='dropmenu$i'>$section</a>";
       //the ul should start here
    echo "</li>"; 
  } else {
    echo"<li><a href='#h' rel='dropmenu$i'>$section</a>";
       //the ul should start here
    echo "</li>"; 
  }

So then you will have to avoid the second for loop

 for($s=0;$s<count($sections);$s++)

because you can get the section id directly inside

$sql3="SELECT * FROM articles WHERE id='$sectionid'";

so writing the code inside if and then also inside else, is not good.

I have modified your code a bit, hope it helps, have not removed the second for loop yet but I think its not needed, modify according to your requirements.

<ul class="sf-menu">

<li><a href="#">HOME</a></li>

<?php
$sql="SELECT * FROM section ORDER BY orderID";
$result=mysql_query($sql) or die ("Error!! BAD SELECT SEARCH STATEMENT");
$nrows = mysql_num_rows($result);

// Top level Items

for($i=1;$i<=$nrows;$i++) {
  $row = mysql_fetch_array($result);
  extract($row);

  $sections[] = $section;
  $sectionids[] = $sectionid;

      echo "<li><a href='";

      if ($section == "FORUM")
          echo "#";
      else
          echo "#h";

      echo "' rel='dropmenu$i'>$section</a>";

      for($s=0;$s<count($sections);$s++) {

      /*$sm1 = ($s - 1);*/
      $sp1 = ($s + 1);

      $sql3="SELECT * FROM category WHERE sectionid='$sectionids[$s]'";
      $result3=mysql_query($sql3) or die ("Error!! BAD SELECT SEARCH STATEMENT");
      $nrows3 = mysql_num_rows($result3);

      if ($nrows3 > 0) {
        echo"<ul>";
        for($t=0;$t<$nrows3;$t++) {
          $row3 = mysql_fetch_array($result3);
          extract($row3);

          if ($type=='Photo') {
            $title20 = strtolower($title);
            $title20 = str_replace(" ", "_", $title20);
            $sec = strtolower($sections[$s]);
            $sec = str_replace(" ", "_", $sec);
            if ($sec=='photos') {
              echo"<li><a href='" . dirname($_SERVER['PHP_SELF']) . "$sec/$catID-$title20/1.html'>$title</a></li>";
            }

            else {
              echo"<li><a href='" . dirname($_SERVER['PHP_SELF']) . "$sec/photos/$catID-$title20/1.html'>$title</li></a>";
            }

          } elseif ($type=="Contact") {
            $title20 = strtolower($title);
            $title20 = str_replace(" ", "_", $title20);
            echo "<li><a href='" . dirname($_SERVER['PHP_SELF']) . "contactus/contact_$title20.html'>$title</a></li>";
          } elseif ($type=='Quote') {

            $sql4="SELECT articleID FROM article WHERE category='49' AND Now() > publishingdate ORDER BY publishingdate DESC LIMIT 0, 1";
            $result4=mysql_query($sql4) or die ("Error! bad select statement");
            $row4=mysql_fetch_array($result4);
            extract($row4);

            echo "<li><a href='" . dirname($_SERVER['PHP_SELF']) . "everton_quotes.php?id=$articleID'>$title</a></li>";
          }
          elseif ($type=='LastMatch') {
            $sql4="select r.matchID as matchID2, r.venue, r.versus as versus2, s.year FROM regmatch r, season s WHERE r.season=s.seasonID AND Now() > r.matchdate ORDER BY r.matchdate DESC LIMIT 0,1";
            $result4=mysql_query($sql4) or die ("Error! bad select statement");
            $nrows4=mysql_num_rows($result4);
            if ($nrows4 > 0) {
              $row4=mysql_fetch_array($result4);
              extract($row4);
              if ($venue=='Home') {
                $teams = "Everton V $versus2";
                $teams2 = "everton_vs_" . str_replace(" ", "_", strtolower($versus2));
              }
              else {
                $teams = "$versus V Everton";
                $teams2 = str_replace(" ", "_", strtolower($versus2)) . "_vs_everton";
              }

              $year = str_replace("/", "-", $year);
              echo "<li><a href='" . dirname($_SERVER['PHP_SELF']) . "match/$year/$matchID2-$teams2/" . strtolower($venue) . "/matchreport.html'>$title</a></li>";
            }
          } elseif ($type=='NextMatch') {

            $sql5="select r.matchID as matchID3, r.venue, r.versus as versus2, s.year FROM regmatch r, season s WHERE r.season=s.seasonID AND r.matchdate > Now() ORDER BY r.matchdate ASC LIMIT 0,1";
            $result5=mysql_query($sql5) or die ("Error! bad select statement");
            $nrows5=mysql_num_rows($result5);
            if ($nrows5 > 0) {
              $row5=mysql_fetch_array($result5);
              extract($row5);
              if ($venue=='Home') {
                $teams = "Everton V $versus2";
                $teams2 = "everton_vs_" . str_replace(" ", "_", strtolower($versus2));
              }
              else {
                $teams = "$versus2 V Everton";
                $teams2 = str_replace(" ", "_", strtolower($versus2)) . "_vs_everton";
              }

              $year = str_replace("/", "-", $year);
              echo "<li><a href='" . dirname($_SERVER['PHP_SELF']) . "match/$year/$matchID3-$teams2/" . strtolower($venue) . "/teamnews.html'>$title</a></li>";
            }
          } else {

    // Overides for missing pages

            if($title == "Gwladys Street Preacher") {
              echo"<li><a href='/fans/121-gwladys_street_preacher/index.html'>$title</a></li>";
            }

            elseif($title == "The Secret Fan") {
              echo"<li><a href='/news/124-secret-fan/index.html'>$title</a></li>";
            }

            elseif($title == "Toffee Girl") {
              echo"<li><a href='/fans/125-toffee_girl/index.html'>$title</a></li>";
            }

            else {

              echo"<li><a href='" . dirname($_SERVER['PHP_SELF']) . $link . "'>$title</a></li>";

            }

          }

        }

        echo"</ul>";
      }

    }//end of for

    echo "</li>";
  }

// Submenu Items

echo "</ul>";

?>

have edited the ans, actually for testing it on my machine i had changed the table name, have now edited the table name to your's, please check now — Deepika Janiyani, Nov 20 '13 at 04:53

score 2 · Answer 5 · answered Nov 19 '13 at 13:24

See this, NOTE: i added an index called 'has_sub' to check if the section has sub, then create a sub menu for it

echo '<ul>';
$array = array();
while($row = mysql_fetch_array($query_main_data)) {
    if($row['has_sub'] === 'yes')  {
       echo '<li><a>'.$row['section_name'].'</a></li>';
       echo '<ul>';
       while($row2 = mysql_fetch_array($query_sub_data)) {
          echo '<li><a>'.$row['section_name'].'</a></li>';
       }
       echo '</ul>';
    }
}
echo '</ul>';

zerobfd · Answer 6 · 2013-11-23T18:02:16.243

tl;dr get all of your data then build the menu

There are a lot of ways to do this and none of them are wrong; everywhere I've had to do this, we had more web servers than database servers and so to scale better we tried to minimize database calls, and to keep things easy on developers we pulled as much logic as possible outside of the database and did all of the processing in php/javascript.

Since you know what data you want at the beginning (i.e. you don't have to pull different things from the database depending on some logic that's passed in or anything), you can pull all of the data that you want at the beginning of the page and then build the menu, instead of trying to do it as you pull from the database. This lets you 1) separate the code that makes database calls and the code that holds the logic for building the menus and 2) cut down your database calls to just two instead of 1 + number of sections. Depending on your needs, #2 may not be a big factor, but it makes it easier to read if you separate everything at the beginning.

Something like this (could be cleaner):

<li><a href="#">HOME</a></li>

<?php

$sql="SELECT * FROM section ORDER BY orderID";
$result=mysql_query($sql) or die ("Error!! BAD SELECT SEARCH STATEMENT");
$nrows = mysql_num_rows($result);

// Top level Items

for($i=1;$i<=$nrows;$i++) {
  $row = mysql_fetch_array($result);
  extract($row);

  $sections[$sectionid] = $section; //we want to reference the section by the id later
  $sectionids[] = $sectionid;
}

// Here we can pull all of the subsections we need
$all_section_ids = implode(',', $sectionids); // join all of the ids with a comma
// SQL IN lets you select all rows where the sectionid matches any of a list
$sql3="SELECT * FROM category WHERE sectionid IN ($all_section_ids)";
$result=mysql_query($sql) or die ("Error!! BAD SELECT SEARCH STATEMENT");
$nrows = mysql_num_rows($result);

for($i=1;$i<=$nrows;$i++) {
  $row = mysql_fetch_array($result);
  extract($row);

  //Whatever values that you need from the category table, depends on what your data looks like
  $subsections[$sectionid][] = $subsection;
  $types[$sectionid][] = $type; //each sectionid gets a separate array
  $some_array[$sectionid][] = $whatever_variable;
}

//Now we can loop through and create everything
foreach ($sections as $id => $section) {
    //now $section is the name and $id is the sectionid for that section
    echo '<li><a href="whatever link"></a>';
    echo '<ul>';
    //since we separated the subsections by sectionid, we can do this
    foreach ($subsections[$id] as $subsection) {
        echo '<li>';
        //whatever logic you need to to build the inner list
        echo '</li>';
    }
    echo '</ul>';
}

Populating Drop Down Menu from PHP/MYSQL Select Values. How to rearrange output

6 Answers6