PHP - Multiple while($row = mysql_fetch_array($variable)) { } ERRORS

Question

Okay my syntax can probably be described without the code. Basically it should be easy...but never is.

I have 2 loops in a row...basically the same thing. I SELECT * every variable from my database, and then I need to build a 2 layer javascript based on two sep. variables.

So

I have:

while ($row = mysql_fetch_array($myVariable)) {

// do events

}

then after that

while ($row2 = mysql_fetch_array($myVariable)) {

// do events

}

For some reason it's completely returning NOTHING on the second one...is there some where I need to reset my array, did it possibly end and then I can't just restart. Such a basic question, but I can't figure out what else could be wrong for the life of me. So thats the question, is that a general issue to just do that twice in a row and expect PHP to start over each time at the beginning of $myVariable which holds my SELECT statement?

Thanks...will check all day and update as needed. First timer to Stack Overflow i'd love to see the help.

Solution 1

The problem is that the query returns a resource. This resource has it's own internal pointer (counter to remember which row to return). mysql_fetch_array will advance that internal pointer and return each row from the resource one at a time. once it is at the end (no more rows left), it returns false. so on the last loop, $row equals false which exits the while loop. The pointer is not reset though. So on the next while loop, you call mysql_fetch_array, the pointer is at the end and so it returns false which completely bypasses the second while loop. What you should do, is loop through the data once and store each row into an array. Then you can copy/loop over the array as much as you want and everything works as expected.

$data = array();
while ($row = mysql_fetch_array($myVariable)) {
    $data[] = $row;
}
//now $data has all the rows. a simple foreach will loop over the data.
foreach($data as $row){
    //do events
    print_r($row);
}

//and you can loop over data again and again.
foreach($data as $row){
    //do events
    print_r($row);
}

Solution 2

Before the second loop, try using mysql_data_seek():

mysql_data_seek($myVariable, 0);

The pointer in the recordset needs to be reset, otherwise it will still indicate that it's at the end.

It may be a better option, as ZiTAL points out, to store your records in an array and just iterate over the array.

Comments

[Wiseguy]

I get the impression that the asker's loops are not nested. But if they were, you have a good point.

[Joe]

I mainly posted this in case they were nested, and then totally forgot to mention that haha. Good catch :)

[Angus Walker]

I prefer this option. There is no need to put the data into an array when you can just reset the pointer back to the start. Basically you are just creating two arrays when you only need one and so using more memory!

[Wiseguy]

@AngusWalker I guess I should have qualified "better" in terms of potential readability/maintainability. As written, it's ambiguous.