While Loop not displaying correct data

Question

While Loop in PHP is not showing the correct data. I am trying to populate my Menu out of SQL DB. It only gives me the last entry from the DB eventhough there are 6 Entries in the DB.

Here is my Code:

$top_sql = "SELECT * FROM menu_top_level /* WHERE top_level_visible = 'Yes' */ ORDER BY 
                  top_level_order ASC";                                               // create a database query

                $top_res = sqlsrv_query($conn, $top_sql) or die(sqlsrv_error($conn)); // check connection and execute query
                if ($top_res = sqlsrv_query($conn, $top_sql)) {                     // if the query contains results...
    Here    >>>>>>>> while ($row = sqlsrv_fetch_array($top_res)) {                     // loop through each given row
                        $menu_block = "<div id='Accord' class='accord'><ul id='menu'>"; 
                        $menu_block .= "<li id='menubar'><a>".$row['2']."</a>";
                 /* echo "<pre>";
                    print_r($row);
                    echo "</pre>";*/
                        // Start: Build mid-level
                        $mid_sql = "SELECT * FROM menu_mid_level WHERE mid_level_fk_id = $row[top_level_pk_id] /* AND 
                          mid_level_visible = 'Yes' */ ORDER BY mid_level_order ASC"; // create a database query
                        $mid_res = sqlsrv_query($conn, $mid_sql, array(), array("Scrollable"=>"buffered")) or die(sqlsrv_error($conn));

                        $mid_num_rows = sqlsrv_num_rows ($mid_res); // get number of row
                        //echo $mid_num_rows;
                        $mid_num_rows_constant = $mid_num_rows;    // store number of row as a value
                        $mid_num_rows_counter = 0;                 // counter to match constant by adding 1 each loop (line 32)
                        if ($mid_num_rows == 0) {                  // unless if row count equals 0
                            $menu_block .= "</li>";                // close LI
                        } else if ($mid_num_rows > 0) {            // otherwise if more than 0...
                            $menu_block .= "<ul class='sub'>";                 // open UL for coming LI rows
                        }

                        if ($mid_res = sqlsrv_query($conn, $mid_sql)) {            // if the query contains results...
                            while ($row = sqlsrv_fetch_array($mid_res)) {            // loop through each given row
                                $menu_block .= "<li id='menubar'><a href='$row[mid_level_url]'>".$row[mid_level_name]."</a>";
                                $mid_num_rows_counter = $mid_num_rows_counter + 1;   // mark row count as handled (line 60)

                                // Start: Build bot-level
                                $bot_sql = "SELECT * FROM menu_bot_level WHERE bot_level_fkt_id = $row[mid_level_fk_id] 
                                  AND bot_level_fkm_id = $row[mid_level_order] /* AND bot_level_visible = 'Yes' */ 
                                  ORDER BY bot_level_order ASC";

                //echo $bot_sql;              // create a database query
                                $bot_res = sqlsrv_query($conn, $bot_sql, array(), array("Scrollable"=>"buffered")) or die(sqlsrv_error($conn));

                                $bot_num_rows = sqlsrv_num_rows ($bot_res);   
                 //echo $bot_num_rows;              // check number of rows
                                if ($bot_num_rows == 0) {                        // if no inner rows...
                                    $menu_block .= "</li>";                      // close above LI
                                } else if ($bot_num_rows > 0) {                  // otherwise if more than 0, it does contain...
                                    $menu_block .= "<ul class='sub'>";                       // so open UL to contain coming LI
                                }

                                if ($bot_res = sqlsrv_query($conn, $bot_sql)) {    // if the query contains results...
                                    while ($row = sqlsrv_fetch_array($bot_res)) {    // loop through each given row
                                        $menu_block .= "<li id='menubar'><a href='$row[bot_level_url]'>".$row[bot_level_name]."</a>
                                          </li>";                                    // and output row result

                                        $bot_num_rows = $bot_num_rows - 1;           // keep counting back 1 each loop until...
                                        if ($bot_num_rows == 0) {                    // check it is exactly 0...
                                            $menu_block .= "</ul></li>";             // row has ended so close UL and LI
                                        }
                                    }
                                } // End: Build bot-level

                            }
                        } // End: Build mid-level

                        // if reached last row & provided there was more than 1 row, then close UL and LI
                        if ($mid_num_rows_counter == $mid_num_rows_constant && $mid_num_rows_counter > 1) {

                            $menu_block .= "</ul></li></ul></div>";

                        }
                    }
                } // End: Build top-level

`

Answer 1

I found the problem.

I had the incorrect if statement argument. The loop stopped once it has reached the second loop. Because my closing if statement that checked whether the loop should exit or not was set to $variable > 1 and not >=, so it reached 1 and it sees that the variable is equal to the value 1 and exits

I replaced this:

if ($mid_num_rows_counter == $mid_num_rows_constant && $mid_num_rows_counter > 1) {

                        $menu_block .= "</ul></li></ul></div>";

                    }

With this:

if ($mid_num_rows_counter == $mid_num_rows_constant && $mid_num_rows_counter >= 1) {

                        $menu_block .= "</ul></li></ul>";

                    }

Answer 2

I think your error is in this line:

$menu_block = "<div id='Accord' class='accord'><ul id='menu'>"; 

For each item in your loop, your variable $menu_block is being reset to a fix value.
Change that line to:

$menu_block .= "<div id='Accord' class='accord'><ul id='menu'>"; 

(note the .=)
You may need to add $menu_block=''; at someplace above your loop