Use mysql query result in more than one place in code

Question

I'm building a system that tracks contact lenses. I'm storing the contact lens info in a database as sometimes prices/availabilities change and i access this info from multiple points in the program. I'm trying to interface with this list using a dropdown by doing "SELECT * FROM contacts" as a query. my code looks like this :

$contact_list = mysqli_query($link, "SELECT brand FROM contacts ORDER BY brand");

Then I echo that list out in a while loop using PHP to populate the options in the dropdown.

My question is this: I have these dropdowns for each eye on the same form. So it's "Brand Right Eye"....other miscellaneous info about the right eye....then "Brand Left Eye". But ONLY the right eye is populating with the brand info because it appears first in the code. What i'm having to do is copy/paste the exact same query and do

$contact_list2 = mysqli_query($link, "SELECT brand FROM contacts ORDER BY brand");

then later if I need the dropdown again, I need to do $contact_list3..and so on. Why can i not generate a drop down using the same variable? Why does it stop responding to calling the variable after the first execution of it and is there any work around that I can implement that would allow me to not have to copy/paste the same query with a different variable association each time?

just for refernce, my php while code is this:

 <select class="form-control" name = "brandOS"> 
    <option value="0">Please Select</option>
        <?php
            while($row = mysqli_fetch_array($contact_list))
            {
            ?>
            <option value = "<?php echo($row['brand'])?>" name = "brandOS">
                <?php echo($row['brand']) ?>
            </option>
            <?php
            }               
        ?>
    </select>

I have this loop copy/pasted for right eye and left eye. But it only works on which ever drop down appears first in the code.

Collect the brands in a PHP array and then generate the output you need using this array's elements. — Andy G, Jan 23 '18 at 15:58
There are several 'work-arounds': 1: Move the resultSet back to the start before iterating over it a second time. 2: Build your option list once and echo it in multiple places. — Moob, Jan 23 '18 at 15:59
Possible duplicate of [How to go through mysql result twice?](https://stackoverflow.com/questions/6439230/how-to-go-through-mysql-result-twice) — Moob, Jan 23 '18 at 16:00

score 0 · Answer 1 · answered Jan 23 '18 at 16:08

A possible solution will be more efficient in term of performance could be :

<?php
    $left_eye = '<option value="0">Please Select</option>';
    $rigth_eye = '<option value="0">Please Select</option>';

    while($row = mysqli_fetch_array($contact_list))
    {
        //logic for left eye
        $left_eye .= <<<HTML
        <option value ="{$row['brand']}" name = "brandOS">
            {$row['brand']}
        </option>
HTML;

        //logic for rigth eye
        $rigth_eye .= <<<HTML
        <option value ="{$row['brand']}" name = "brandOS">
            {$row['brand']}
        </option>
HTML;
    }       
?>



<select class="form-control" name = "brandOS"> 
  <?php echo $left_eye ; ?>
</select>


<select class="form-control" name = "brandOS"> 
  <?php echo $rigth_eye ; ?>
</select>

With this solution you get your result in the same while loop. If the left and right select are the same you can use the same variable.

just out of curiosity, why does it require this "workaround"? why doesn't it work by just repeating the code like i was doing originally? — maximus1127, Jan 23 '18 at 16:31
It will works with two while loop. But it will just be more efficient to use only one while loop to retrieve your data. — Ananta, Jan 24 '18 at 08:55

score 0 · Answer 2 · answered Jan 23 '18 at 16:09

Store the brands in an array, then you can just loop through the array.

<?php

    $contact_list = mysqli_query($link, "SELECT brand FROM contacts ORDER BY brand");

    $brands = array();

    while($row = mysqli_fetch_array($contact_list))
    {
        array_push($brands, $row['brand']);
    }

?>
<select class="form-control" name = "brandOS"> 
    <option value="0">Please Select</option>
    <?php
        foreach($brands as $brand){
            ?>

                <option value = "<?php echo($brand[0])?>" name = "brandOS">
                    <?php echo($brand[0]) ?>
                </option>

            <?php
        }

    ?>
</select>

score 0 · Answer 3 · answered Jan 23 '18 at 16:10

You can use a PHP array, like the SESSION one, to store values and use them across your site. Be sure you call "session_start()" method on each page you use that array, though.

//Initialize sessions
session_start();

...

//Right after getting result from query
$_SESSION['contact_list'] = $contact_list;

To use it, just be sure to call the method I told you above, and call the variable with the same syntax:

<?php 
    while($row = mysqli_fetch_array($_SESSION['contact_list'])) { ?>

Hope this helps.

Use mysql query result in more than one place in code

3 Answers3