Understanding fetch_assoc()

Question

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.

Answer 1

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.

Answer 2

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;

}