Reusing a MySQL query. 1

[mufka]

I'm trying to determine why mysql_fetch_array does not work on a copied query. In the example below, I try to copy $query to $query2, but when I try to use $query2, it does not come up with anything. Is there some limitation to copying the query and using it again?

I'd like to know how to get this working so I don't have to run the query twice.

$query = mysql_query(
            "SELECT name from employees ORDER BY name");
  if (!$query) {
    echo("<P>Error performing query: " .
         mysql_error() . "</P>");
    exit();
  }
//copy the query to be used later
$query2=$query;

print ('Employee name:');
print ('<br />');
	while ( $row1 = mysql_fetch_array($query) ) {
		$name = $row1['name'];
		print ($name);
		print ('<br />');
	}

print ('Employee name in caps:');
print ('<br />');
	while ( $row2 = mysql_fetch_array($query2) ) {
		$name2 = strtoupper($row2['name']);
		print ($name2);
		print ('<br />');
	}

 

[vacunita]

The problem is not the copying the value of the query variable. The $query variable does not hold any results it only holds pointer to where the results are. copying it to another variable ony makes the $query2 variable point to the same place.

However, once you step through query results once the pointer for the mysql_fetch_array functions is at the end of the results.

So when you try to use it again with the $query2 variable there are no more results to be used.

You need to issue a mysql_data_seek() and set the pointer back to to the beginning of the results so you can run through them again.

print ('Employee name in caps:');
print ('<br />');
[red]mysql_data_seek($query2,0);[/red]
    while ( $row2 = mysql_fetch_array($query2) ) {
        $name2 = strtoupper($row2['name']);
        print ($name2);
        print ('<br />');
    }

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[mufka]

That works. With that, I don't need $query2 at all, I can just reuse $query.

 

[vacunita]

Correct. I should have mentioned that.


----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.