Populate multiple lists from SQL database

Question

There are similar questions already on here, but none of them quite suit my situation. Basically, I want to populate a variable number of select menus with the same data from a MYSQL database. The reason for this is that I would like the user to be able to add rows to submit multiple choices simultaneously.

In order to avoid doing a login --> read of MYSQL for each instance of the select menu, I created an array which would be used to populate the menu whenever necessary. The problem is that it works ONLY for the first instance; in other words, PHP is somehow deleting the values stored in the array after the first reading.

I apologise if this question is very basic, I am quite new to PHP.

Here is my code so far:

TO ACCESS MYSQL AND DEFINE VARIABLE 'result':

<?php 
    $servername = "localhost";
    $username = "sonch_PUBLIC";
    $password = "sonch_PUBLIC";
    $dbname = "sonch_MAIN";
    date_default_timezone_set('Europe/Zurich');
    $date = date('Y-m-d');

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
        } 

    //START SEARCH BY VENUE
    $sql = "SELECT Name, City FROM Venue ORDER BY City";
    $result = $conn->query($sql);

    $conn->close();
?>

TO POPULATE THE FIRST INSTANCE OF :

<select style="width:138px; text-align:center">
    <option selected>CHOOSE VENUE</option>';
    <?php
        if ($result->num_rows > 0) {
            while($row = $result->fetch_assoc()) {
                echo "<option>" . $row["Name"] . " - " . $row["City"] . "</option>";
            }
        } 
    ?>
</select>

So far so good.... but when I want to populate subsequent menus with the same code, i.e., by repeating:

<select style="width:138px; text-align:center">
    <option selected>CHOOSE VENUE</option>';
    <?php
        if ($result->num_rows > 0) {
            while($row = $result->fetch_assoc()) {
                echo "<option>" . $row["Name"] . " - " . $row["City"] . "</option>";
            }
        } 
    ?>
</select>

...they are empty.

Suggestions?

The page in question is: s-on.ch/add2-event.php .... you can see that the 'choose venue' option is populated in the first instance, but when a new line is added, the menu is empty. — Paul Clift
– Paul Clift, Commented Nov 23, 2018 at 11:33
You are NOT creating an array to be used many times. You are just processing a result set. Once you consume the resultset it is finished. Do as you suggested yourself and process the resultset into an array and then reuse the array — RiggsFolly
– RiggsFolly, Commented Nov 23, 2018 at 11:34
Thanks for the answer! I thought that I had set 'result' as an array..... could you please show me how to change that? — Paul Clift
– Paul Clift, Commented Nov 23, 2018 at 11:35

Nick · Accepted Answer · 2018-11-23 11:41:31Z

5

Your first while loop:

        while($row = $result->fetch_assoc()) {
            echo "<option>" . $row["Name"] . " - " . $row["City"] . "</option>";
        }

reads all the data from the result set. So when you do the second one, there is nothing left to read. To fix that, you need to reset the data pointer using mysqli::data_seek. So before the subsequent while loops, add this line:

 $result->data_seek(0);

data_seek moves the result set read pointer to the specified row number. In this case, we are moving it back to row 0, or the start of the result set. Having done that, you can then read the result set again in full

edited Nov 23, 2018 at 11:41

answered Nov 23, 2018 at 11:35

Nick

147k23 gold badges67 silver badges106 bronze badges

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

2 Comments

Paul Clift Over a year ago

Brilliant! It worked. I have to admit that I do not completely understand why though. I understand that the data was being read and could not be read again, but how does this line change that exactly?

Nick Over a year ago

data_seek moves the result set read pointer to the specified row number. In this case, we are moving it back to row 0, or the start of the result set. Having done that, you can then read the result set again in full.

RiggsFolly · Accepted Answer · 2018-11-23 12:00:15Z

1

You are NOT creating an array to be used many times. You are just processing a result set. Once you consume the resultset it is finished. Do as you suggested yourself and process the resultset into an array and then reuse the array

<?php 
    $servername = "localhost";
    $username = "sonch_PUBLIC";
    $password = "sonch_PUBLIC";
    $dbname = "sonch_MAIN";
    date_default_timezone_set('Europe/Zurich');
    $date = date('Y-m-d');

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
        } 

    //START SEARCH BY VENUE
    $sql = "SELECT Name, City FROM Venue ORDER BY City";
    $result = $conn->query($sql);

    // If you have the MySQL Native Driver installed
    //$allRows = $result->fetch_all();

    // if you dont have the MySQL Native Driver installed you will have to do this
    $allRows = array();
    while ($row = $result->fetch_assoc() ) {
        $allRows[] = $row;
    }

    $conn->close();
?>

FIRST INSTANCE

<select style="width:138px; text-align:center">
<option selected>CHOOSE VENUE</option>';
<?php
    foreach ($allRows as $row) {
        echo "<option>" . $row["Name"] . " - " . $row["City"] . "</option>";
    } 
?>
</select>

And as many times as you like reuse that array

edited Nov 23, 2018 at 12:00

answered Nov 23, 2018 at 11:38

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

10 Comments

Paul Clift Over a year ago

I apologise, I am obviously doing something stupid here..... when I add the code, the page no longer displays (<body></body> =$0 in console).....

Paul Clift Over a year ago

It all works if I remove 'fetch_all()' from the code you gave me.... but I don't feel good about just blindly hacking into code until it does what i want it to...

RiggsFolly Over a year ago

That makes no sense. If you remove the fetch_all() then the array will be empty and there will be no data to output.... Hmmmm the plot thickens

RiggsFolly Over a year ago

Ahh I bet I know what it is.... Your system does not have the MySQL Native Driver installed. So fetch_all() will not work. Two secs I will amend accordingly

RiggsFolly Over a year ago

Ok try that foreach loop to unload the resultset and create the array

|

Collectives™ on Stack Overflow

Populate multiple lists from SQL database

2 Answers 2

2 Comments

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related