Is this a PHP or MySQL bug?

Question

$allUsersResult = mysql_query("SELECT * FROM users");

// the purpose of this line was to grab the first row for use 
// separately in a different area than the while loop
$user = mysql_fetch_assoc($allUsersResult);


while($users = mysql_fetch_assoc($allUsersResult)){

 // the first row is not available here

}

So is this a bug or is it my fault for doing it wrong?

PS: this is only for example. I'm not using both $user and the while loop right next to each other like this, they are used in different places in the script.

Answer 1

You need to drop

$allUsers = mysql_fetch_assoc($allUsersResult);

It's taking your first result row.

Answer to new question: No. It is not a design flaw in PHP. It's a flaw in your program design. You need to rethink what you are doing.

Why do you need the first value separated out? Are you relying on it to be a specific row from your table all of the time? If you alter your table schema it's very possible that the results will be returned to you using some other sorted order.

Perhaps if you tell us what you are trying to do we can give you some design suggestions.

Answer 2

It is your fault. By calling $allUsers = mysql_fetch_assoc($allUsersResult); first, you already fetch the first row from the result set. So just remove that line, and it should work as expected.

edit: Per request in comment.

$user = mysql_fetch_assoc($allUsersResult);
if ( $user ) // check if we actually have a result
{
    // do something special with first $user

    do
    {
        // do the general stuff with user
    }
    while( $user = mysql_fetch_assoc($allUsersResult) );
}

Answer 3

Which is considered as bad code by some IDEs (two statements in one row). Better:

$allUsersResult = mysql_query("SELECT * FROM users");
$user = mysql_fetch_assoc($allUsersResult);

while($user){

    // do stuff
    doStuff($user)

    // at last: get next result
    $user = mysql_fetch_assoc($allUsersResult)


}

Answer 4

I'll go ahead and answer my own question on this one:

    $allUsersResult = mysql_query("SELECT * FROM users");

    // transfer all rows to your own array immediately
    while($user = mysql_fetch_assoc($allUsersResult)){
       $allUsers[] = $user;
     }

    // use first row however you like anywhere in your code
    $firstRow = $allUsers[0];

    // use all rows however you like anywhere in your code
    foreach($allUsers as $user){
      // do whatever with each row ($user). Hey look they're all here! :)
    }

Answer 5

When you use mysql_fetch_assoc(), you are basically retrieving the row and then advancing the internal result pointer +1.

To better explain, here is your code:

$allUsersResult = mysql_query("SELECT * FROM users");
//Result is into $allUsersResult... Pointer at 0


$user = mysql_fetch_assoc($allUsersResult);
// $user now holds first row (0), advancing pointer to 1 


// Here, it will fetch second row as pointer is at 1...
while($users = mysql_fetch_assoc($allUsersResult)){

 // the first row is not available here

}

If you want to fetch the first row again, you do not need to run the query again, simply reset the pointer back to 0 once you have read the first row...

$allUsersResult = mysql_query("SELECT * FROM users");
//Result is into $allUsersResult... Pointer at 0


$user = mysql_fetch_assoc($allUsersResult);
// $user now holds first row (0), advancing pointer to 1 

// Resetting pointer to 0
mysql_data_seek($allUsersResult, 0);

// Here, it will fetch all rows starting with the first one
while($users = mysql_fetch_assoc($allUsersResult)){
  // And the first row IS available
}

PHP Documentation: mysql_data_seek()