Question about mysql_fetch_array

[Vitamin]

I do a query and then do a while loop to go threw it, but later down the page I need to loop threw that data again, but nothing is there. Example

 

while ($row = mysql_fetch_array($sqlmaps)) {
//some code
}
//some code
while ($row = mysql_fetch_array($sqlmaps)) {
//it dose not work here now
}

 

Do need to do that exact same query 2 times to get the data?

[radar]

You usually don't, but odds are you are overwriting that query somewhere...

 

This is generally the reason when I do my queries I do:

 

$sql = mysql_query("SELECT * FROM TABLE");

 

this gives me the mysql_query id to use as many times as I want as long as I don't overwrite $sql with anything else such as:

 

$sql = mysql_query("SELECT * FROM TABLE");

 

while ($sql = mysql_fetch_array($sql)) {

// some code

}

 

as that would overwrite the data i already had in $sql..  so in your example, you just need to ensure that you're not overwriting $sqlmaps with anything else prior to the 2nd time you're calling it.

 

When I work with arrays I personally use my custom function I wrote to coincide with the custom class I wrote to dynamically create form fields....  that function looks like:

 

<?php
function sql_md_array($query, $cnt) {
for ( $row = 0; $row < $cnt && $array = mysql_fetch_assoc($query); $row++ ) {
foreach ($array as $key => $value) {
  $mda[$row][$key] = $value;
  }
}
	return $mda;
}
?>

 

So my end result would look like:

 

<?php

$sql = mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM table");
$_query = "SELECT FOUND_ROWS() as total";
$_result = mysql_query($_query);
$_row = mysql_fetch_array($_result, MYSQL_ASSOC);
$data = sql_md_array($sql, $_row['total']); 
?>

 

this would give me the ability to call $data which would always hold that information in an array format...  basically all the function does is ensure that all results are in an array...  named it sql_md_array because well it takes my sql and turns it into a multi-dimensional array.

 

[Vitamin]

Thanks for the long in-depth post!

I ended up doing something very similar to what you described.  I checked multiple times to make sure that I was not overwriting $sqlmaps and I was not.

I did find a solution, but I wanted to look into it some more so I created a different script and did this.

error_reporting(E_ALL);
$link = mysql_connect('localhost', 'root', '');
$selectdb = mysql_select_db('replay', $link);

$sqlmaps = mysql_query('SELECT * FROM maps');

while ($row = mysql_fetch_array($sqlmaps)) {
echo $row['name'];
echo '<br />';
}
echo '<br />';

while ($row2 = mysql_fetch_array($sqlmaps)) {
echo $row2['name'];
echo '<br />';
}

 

That is the code of the entire page and it only displays the set of data once.  It also throws no errors.

I'm completely baffled.  I'm going to bed hopefully someone can explain this to me.

[PFMaBiSmAd]

To reuse a result set from a query, simply use mysql_data_seek to reset the result pointer to the first row.

 

Or you could buffer the data in an array and iterate over the array.

 

[Vitamin]

Thank you sir. Exactly what I was looking for!