How fetch_assoc know that you want the next row from the table?


April 2019


253 time


If we would have code like this:

$result = $conn->query('SELECT username FROM users');
$row1 = $result->fetch_assoc();
echo $row1['username'], '<br>';
$row2 = $result->fetch_assoc();
echo $row2['username'], '<br>';

Second echo would show username from second row in table, but why? How fetch_assoc() know that you want the next row in return. Does it have some built-in counter that counts how many times it was called?

1 answers


It keeps a pointer into the list of rows returned. When the query returns data (i.e. when the result object is created), it retrieves all the rows returned by the query.

> row1 foo bar
  row2 baz boo
  row3 tst tmp

When you call fetch_assoc, the row the pointer points to is returned, and the pointer is advanced:

return_val = (row1, foo, bar)
increment pointer

  row1 foo bar
> row2 baz boo
  row3 tst tmp

return return_val    

This is repeated until there is nothing more to return. fetch_assoc then returns null forever.

The last part allows you to use it in a while loop automagically:

while ($row = $result->fetch_assoc()) {
    // do something with $row

When fetch_assoc returns null, the while loop terminates and execution continues outside the loop - and the complete result set has been read.