Copy result set array

NeoGeo

I'm trying to do this:

$query = "select * from products";
$result = mysql_query($query);
$result2 = $result;

while($data = mysql_fetch_array($result)) {
// do something
}

while($data = mysql_fetch_array($result2)) {
// do something
}

This doesn't work, when I set $result2 to value of $result it acts like it is passing $result by reference rather than making a copy. I'm certain because $result2 is always empty, but if I reverse the loops and go through $result2 first then $result is empty.

Doing everything in one loop will be difficult but not impossible. Doing another query something like this:

$query = "select * from products";
$result = mysql_query($query);
$result2 = mysql_query($query);

// Same while loops

That works, but then I have to make two calls to the DB and the this query (the real one) is rather intensive and I don't want to make a second request to the DB. It would be so much easier to just create duplicate result set array in PHP but I can't figure out how to do it.

Thanks in advance.

laserlight

Option #1: Use [man]mysql_data_seek/man before starting the second loop.

Option #2: Copy the result set into an array, e.g.,

$array = array();
while ($row = mysql_fetch_assoc($result)) {
    $array[] = $row;
}

Option #3: Combine the two loops into one, which may be the most efficient method, but not necessarily the most maintainable.

NeoGeo

Yes but then you can't run mysql_fetch_array() on a regular array, only a result set array. This means you have to re-engineer mysql_fetch_array() or actually define the indexes: $array[1], $array[2] rather than $data['name'], $data['price'], etc.

There are several ways to get around this, all of them either overly complicated (combine statements in one loop, store in regular array) or DB intensive (multiple queries).

It seems like it's so obvious that this is a very necessary feature how could PHP not contain such functionality?

laserlight

Yes but then you can't run mysql_fetch_array() on a regular array, only a result set array. This means you have to re-engineer mysql_fetch_array() or actually define the indexes: $array[1], $array[2] rather than $data['name'], $data['price'], etc.

No, you can still use $data['name'], $data['price'], etc. mysql_fetch_array() returns an array with duplicate indices (both numeric and associative). If you provide MYSQL_ASSOC as the second argument, or use mysql_fetch_assoc() as in my example, it will return an array with associative indices only.

There are several ways to get around this, all of them either overly complicated (combine statements in one loop, store in regular array) or DB intensive (multiple queries).

Combining the statements into one loop is more efficient since it reduces two or three passes over the result set into one pass. Storing the result set in an array of arrays is not complicated, as I demonstrate below. mysql_data_seek() does not involve multiple queries, but a movement of the internal result set pointer.

It seems like it's so obvious that this is a very necessary feature how could PHP not contain such functionality?

What exactly is the functionality that you are looking for?

EDIT:
Here is a concrete example of using mysql_data_seek:

$query = "select * from products";
$result = mysql_query($query);

while ($product = mysql_fetch_assoc($result)) {
    echo $product['name'] . ': $' . $product['price'] . "<br />\n";
}

mysql_data_seek($result, 0);

while ($product = mysql_fetch_assoc($result)) {
    echo $product['name'] . ' costs $' . $product['price'] . "<br />\n";
}

Here is a concrete example of copying the result set into an array:

$query = "select * from products";
$result = mysql_query($query);

$products = array();
while ($product = mysql_fetch_assoc($result)) {
    $products[] = $product;
}

foreach ($products as $product) {
    echo $product['name'] . ': $' . $product['price'] . "<br />\n";
}

foreach ($products as $product) {
    echo $product['name'] . ' costs $' . $product['price'] . "<br />\n";
}

How are they any more complicated or DB intensive than what you have in mind?

NeoGeo

Thanks just what I was looking for!