Understanding fetch_assoc()

13
votes

I am trying to understand how/why fetch_assoc works the way it does. I have the following piece of code: 

$results = $connectToDb->fetch("SELECT * FROM customer");
$resultsArray = $results->fetch_assoc();
print_r($resultsArray);  //print_r 1

while($row = $results->fetch_assoc()){
    print_r($row);       //print_r 2
}

The query returns 3 rows from a table. Why does the 1st print_r return only the 1st row of the queried data but the 2nd print_r returns all 3? How does putting fetch_assoc into a while loop tell it to do the action more than once? I read that fetch_assoc returns either an associative array or NULL but I'm struggling to understand how the while loop "tells" fetch_assoc to fetch the next row, if that makes sense?

Thank you.

2
phpmethods
Why does putting something in a loop make something happen more than once? - Phylogenesis
The meaning of a while statement is simple. It tells PHP to execute the nested statement(s) repeatedly, as long as the while expression evaluates to TRUE. The value of the expression is checked each time at the beginning of the loop, so even if this value changes during the execution of the nested statement(s), execution will not stop until the end of the iteration (each time PHP runs the statements in the loop is one iteration). Sometimes, if the while expression evaluates to FALSE from the very beginning, the nested statement(s) won't even be run once. - Daan
php.net/manual/en/control-structures.while.php - RiggsFolly
The $results object returned from the database is an iterator ('cursor'). An iterator has a 'current row'. Every time you fetch_assoc() it returns the current row and automatically advances to to the next row. So, the while loop does not tell the database to return the next row, the fetch_assoc() does that, the while loop ends when it gets an empty row. - Ryan Vincent
Correct! - The counter is in the $result object :) It is advance whenever you fetch a row from the result set. see: The Iterator interface. for an example of the PHP iterator. All iterators have the same idea. - Ryan Vincent

2 Answers

22
votes

Lets try to understand your code and how it works:

$results = $connectToDb->fetch("SELECT * FROM customer");

A variable $results has a collection of rows which are returned by a query. The size of the collection can be from 0 to n.

$resultsArray = $results->fetch_assoc();

This line fetch the first element from the collection. If the collection is empty it will return NULL.

while($row = $results->fetch_assoc()){
}

It can be decoupled in the following steps:

  1. Calculate $row = $results->fetch_assoc() and return array with elements or NULL.
  2. Substitute $row = $results->fetch_assoc() in while with gotten value and get the following statements: while(array(with elements)) or while(NULL).
  3. If it's while(array(with elements)) it resolves the while condition in True and allow to perform an iteration.
  4. If it's while(NULL) it resolves the while condition in False and exits the loop.
2
votes

I think it useful:

function get_posts(){
$posts = array();
if ($result = $mysqli->query("SELECT  id, post_userid, name, lang, country, post_image, post_date, post_date_updated FROM posts ORDER BY `post_date` DESC ;")) {

    while ($row = $result->fetch_assoc())
    {
        $posts[$row['id']] = $row ;   
    }
} else {
    printf("Prepared Statement Error: %s\n", $mysqli->error);
}
return $posts;

}