CODEWITHSUNDEEP
phpmysql
Paul Clift
Paul Clift

Reputation: 169

Populate multiple lists from SQL database

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?

Upvotes: 0

Views: 180

Answers (2)

RiggsFolly
RiggsFolly

Reputation: 94642

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

Upvotes: 1

Nick
Nick

Reputation: 147146

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

Upvotes: 5

Related Questions