Why you should not use mysql_fetch_assoc more than 1 time?

Some people say you should not use mysql_fetch_assoc more than one time, why is that?

e.g.: I want to display two tables one with users who paid for membership, other with users who did not, so instead of querying database 2 times I query开发者_如何学JAVA it one time and get $result variable with both types of users then I run loop mysql_fetch_assoc and see if list['membership'] = 'paid' then echo ...

Second time I loop loop mysql_fetch_assoc and see if list['membership'] = 'free' then echo ...

What uses less resources considering I got about equal amount of users who registered and unregistered.

Think of your query result set as a sausage, and mysql_fetch_assoc() as a knife that slices off a piece of that sausage. Every time you fetch a row, another piece of sausage is cut off, and it's always a NEW piece of sausage. You can't go and cut off a previously cut piece, because it's been eaten already.

Quote by Typer85 (link):

Please be advised that the resource result that you pass to this function can be thought of as being passed by reference because a resource is simply a pointer to a memory location.

Because of this, you can not loop through a resource result twice in the same script before resetting the pointer back to the start position.

For example:

<?php

// Assume We Already Queried Our Database.

// Loop Through Result Set.

while( $queryContent = mysql_fetch_row( $queryResult ) {

    // Display.

    echo $queryContent[ 0 ];
}

// We looped through the resource result already so the
// the pointer is no longer pointing at any rows.

// If we decide to loop through the same resource result
// again, the function will always return false because it
// will assume there are no more rows.

// So the following code, if executed after the previous code
// segment will not work.

while( $queryContent = mysql_fetch_row( $queryResult ) {

    // Display.

    echo $queryContent[ 0 ];
}

// Because $queryContent is now equal to FALSE, the loop
// will not be entered.

?>

The only solution to this is to reset the pointer to make it point at the first row again before the second code segment, so now the complete code will look as follows:

<?php

// Assume We Already Queried Our Database.

// Loop Through Result Set.

while( $queryContent = mysql_fetch_row( $queryResult ) {

    // Display.

    echo $queryContent[ 0 ];
}

// Reset Our Pointer.

mysql_data_seek( $queryResult );

// Loop Again.

while( $queryContent = mysql_fetch_row( $queryResult ) {

    // Display.

    echo $queryContent[ 0 ];
}

?>

Of course you would have to do extra checks to make sure that the number of rows in the result is not 0 or else mysql_data_seek itself will return false and an error will be raised.

Also please note that this applies to all functions that fetch result sets, including mysql_fetch_row, mysql_fetch_assos, and mysql_fetch_array.

When someone says you can't call mysql_fetch_assoc() twice, they mean against the same resource. The resource result you pass in to mysql_fetch_assoc() is done by reference. You'll need to reset the position of the pointer before you can use mysql_fetch_assoc() a second time.

EDIT: And to do so, try using mysql_data_seek().

It appears that what you want to do is to treat the query result as an array (rows) of arrays(fields). But that's not really what the mysql library provides. What I will often do is in fact copy the rows into an array of arrays (just loop on mysql_fetch until empty) and then do what I want with my own rowset using array functions that PHP provides for the purpose. This also minimizes the amount of time the table is locked.