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

`

Which while is at fault?

Mihai
– Mihai

2014-09-02 12:47:29 +00:00
Commented Sep 2, 2014 at 12:47 — Mihai
– Mihai, Commented Sep 2, 2014 at 12:47

andrew · Accepted Answer · 2014-09-02 12:47:45Z

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

answered Sep 2, 2014 at 12:47

andrew

2,1182 gold badges27 silver badges33 bronze badges

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

7 Comments

Mihai Over a year ago

In this case the id should be a class since it needs to be unique.

Corrie Over a year ago

Thank you, I did this, however now only the top 2 entries display, I need all the entries in the table to display.

Corrie Over a year ago

@Mihai is that in the <ul> or the <div> tag?

Mihai Over a year ago

@Corrie An id has to be unique per page it doesnt matter where.

Corrie Over a year ago

No one has an idea how to assist me?

|

Corrie · Accepted Answer · 2014-09-08 07:19:09Z

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>";

                    }

answered Sep 8, 2014 at 7:19

Corrie

216 bronze badges

Collectives™ on Stack Overflow

While Loop not displaying correct data

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related