Reusing a Data query result set

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Nazum
Forum Newbie
Posts: 1
Joined: Mon Jul 26, 2010 12:42 am

Reusing a Data query result set

Post by Nazum »

Hi all,

I'm relatively a novice to php(I've worked mainly in ASP.NET in the past) and therefore using the mysql and/or mysqli libraries. I have a result set that is very small and I'm using it to populate a html select list like so:

Code: Select all

<?php 
$conn = mysqli_connect(.....);
$sql = "select id, name
                    from client
                    order by name";

$result = $conn->query($sql) or die("Database Error:  " . mysqli_error($conn));
?>
.
.
.
<select name="client" id="client">
                        <?php
                        while($row = $result->fetch_array())
                        {
                            echo"<option value =".$row['id'].">".$row['name']."</option>";     
                        }
                        ?>
                    </select>
As you can see I'm making a list of clients. This is for a work order/time entry system. The problem is that I would like to reuse the result set and make a second list of clients for another data field namely billable clients.

If I understand $result->fetch_array() correctly the result set is iterated through or else consumed like stack's pop(). I'd either like to reset the result set so I can start back at the beginning or else make a copy of it so I can pull data out of the copy. I'd prefer to not run the query twice if I don't have to.

Thanks,

-Jordan
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Reusing a Data query result set

Post by Christopher »

Code: Select all

// results into an array
                        $rows = array();
                        while($row = $result->fetch_array())
                        {
                            $rows[] = $row;     
                        }
// and later
                        foreach ($rows as $row)
                        {
                            echo"<option value =".$row['id'].">".$row['name']."</option>";     
                        }
(#10850)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Reusing a Data query result set

Post by VladSun »

Or use mysql_data_seek function to rewind the result.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply