mysql_fetch_array while loop

Question

I have a while loop that contains another while loop. Both loops are iterating over different mysql result sets. The problem is that the second time the outer while loop calls the inner while loop it doesn't run. Does Mysql_fetch_row discard itself after it has been iterated over?

Here is what the code looks like:

$counter = 0;
while ($r = mysql_fetch_row($result)){
    $col = "";
    $val = "";
    while($d = mysql_fetch_row($dictionary)){
        $key = $d[0];
        for($i= 0; $i< count($tableColumNames); $i++){
            if($tableColumNames[$i] == $key){
                $col .= "`".$d[1]."` ,";
                $val .= "`".$r[$i]."` ,";
                /* echo $tableColumNames[$i]." table ColumnName <br>";
                echo $d[1]." key<br>"; */               
            }           
        }
        echo $key."round".$counter."<br>";
    }
    var_dump ($col);
    var_dump ($val);
    $counter++;
    echo $counter;
}

And here is what the output is like: You can see that the $result holds four records and the output is showing that the loop is working correctly. However, the inner loop over the $dictionary result set doesn't run the second time $result is being iterated over. Any ideas why? I tried to use mysql_fetch_array as well but still the same result.

Thanks

enter image description here

How many rows are returned in the `$dictionary` result resource? Each time you call one of the `mysql_fetch*()` functions, the result resource's internal pointer is advanced. If you intend to use the same row over and over, you would need to be rewinding the resource with `mysql_data_seek()`, but better would be to store all its rows in an array first and use that to loop over (rather than doing your main logic while looping the result resource) — Michael Berkowski, Jun 08 '14 at 01:39
_BUT_... please also show the two queries that create those two result resources. Are the queries related such that they could be combined into a single JOIN query, thereby eliminating a lot of complex loop logic? — Michael Berkowski, Jun 08 '14 at 01:40
mysql_data_seek() worked. It's not going to live in an application, I just need some help reorganising a datasource, and am using php to help me do it. So mysql_data_seek() will do fine. Thanks! — Linda Keating, Jun 08 '14 at 01:41
Ok. You should go ahead and post your own answer below indicating how you solved it. And a standard disclaimer: The old `mysql_*()` functions are deprecated and shouldn't be used for new code. Consider switching to MySQLi or PDO if possible -> [Why shouldn't I use `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) — Michael Berkowski, Jun 08 '14 at 01:43

Vishnu · Answer 1 · 2014-06-08T04:17:13.780

When ever you are calling mysql_fetch_row($dictionary) It gives you a particular column details in the row and it deletes it from the $dictionary array.

So there are no elements for next use.

Instead of declaring $dictionary outside declare it in while loop. It will solve your problem.

$counter = 0;
while ($r = mysql_fetch_row($result)){
$col = "";
$val = "";
$dictionary=("Select * from database");//your own logic
while($d = mysql_fetch_row($dictionary)){
    $key = $d[0];
    for($i= 0; $i< count($tableColumNames); $i++){
        if($tableColumNames[$i] == $key){
            $col .= "`".$d[1]."` ,";
            $val .= "`".$r[$i]."` ,";
            /* echo $tableColumNames[$i]." table ColumnName <br>";
            echo $d[1]." key<br>"; */               
        }           
    }
    echo $key."round".$counter."<br>";
}
var_dump ($col);
var_dump ($val);
$counter++;
echo $counter;
}

or you can use mysql_data_seek().

mysql_fetch_array while loop

1 Answers1