How does this basic mySQL/PHP loop work?
September 9, 2010 4:13 PM   Subscribe

Basic mySQL/PHP question. Help me understand how this simple while loop involving mysqli_fetch_array() works.

I'm currently working through this book and don't understand the mechanics behind a loop it's telling me to use.

$result = mysqli_query($link, 'SELECT joketext FROM joke');
while ($row = mysqli_fetch_array($result))
$jokes[] = $row['joketext'];

It is my understanding that mysqli_fetch_array() retrieves an array of results and stores it to $result. The next line grabs the current value of the 'joketext' column of $row[] and copies it to the $jokes array. I don't understand how this loop ever progresses though; there's no counter or anything. The code works though.

Why isn't this an infinite loop of writing the same value to the same location? There's nothing in the $jokes reference that indicates newer values should be stored to the location of x+1. What's going on here that I'm not seeing?

posted by Ziggy Zaga to Computers & Internet (10 answers total) 1 user marked this as a favorite
The secret is in the while condition. Look at the manual page for mysqli_fetch_array():

Returns an array that corresponds to the fetched row or NULL if there are no more rows for the resultset represented by the result parameter.

When mysqli_fetch_array() or mysql_fetch_row() retrieve a row or array from a result, they advance the internal pointer of the result by one. This is just something that happens by itself. When you call one of those functions on a result variable whose pointer is at the end of the results, it will return NULL and the while condition will fail.
posted by aaronbeekay at 4:18 PM on September 9, 2010

PHP is definitely not my best language, but I believe this is equivalent to:

for (a in array[]) {
do stuff

In other languages. In other words, it executes the loop once for each element in the array.

Similarly, array[]='1' will append '1' to the array. In other words, if you leave off the index the implicit operation is append.

PHP is full of "shortcut" stuff like this which is why some people complain it is hard to read (I tend to agree).
posted by wildcrdj at 4:19 PM on September 9, 2010

From the php manual on arrays:


As mentioned above, if no key is specified, the maximum of the existing integer indices is taken, and the new key will be that maximum value plus 1. If no integer indices exist yet, the key will be 0 (zero)."

So not specifying an array key on assignment moves to the next index in the array.
posted by Mister Cheese at 4:20 PM on September 9, 2010

I guess I should clarify that my answer addresses the part of the question stating "There's nothing in the $jokes reference that indicates newer values should be stored to the location of x+1."
posted by Mister Cheese at 4:22 PM on September 9, 2010

After a few minutes of Googling, the magic phrase will be "result pointer". See the manpages for functions like mysqli_result::data_seek: that's how you can reset the pointer of a result to go back over it again if you want.
posted by aaronbeekay at 4:23 PM on September 9, 2010

So look at -- the function is "mysqli_fetch_array() is an extended version of the mysqli_fetch_row()", and we look at mysqli_fetch_row() and we see it "Fetches one row of data from the result set and returns it as an enumerated array, where each column is stored in an array offset starting from 0 (zero). Each subsequent call to this function will return the next row within the result set, or NULL if there are no more rows."

So $row = mysqli_fetch_array($result) will continue to evaluate to true as long as there's a row returned, and each time you call it it will increment the internal "cursor" to the next row, or null if it's at the end.

The great thing about php is that function documentation can easily be looked up by typing and the function name, and that usually gets you somewhere useful. If a piece of code is obtuse, try that.
posted by artlung at 4:23 PM on September 9, 2010

It's also worth noting, if you weren't clear on this point, that in PHP (and other languages) assignment statements have a value: they evaluate to whatever the leftmost term's value is. For instance,
echo ($x = 3);
will result in the following output:
So once the array pointer reaches the end of the array, the whole expression evaluates to NULL and the while loop breaks.
posted by invitapriore at 4:36 PM on September 9, 2010

On review artlung pretty much said what I said.
posted by invitapriore at 4:37 PM on September 9, 2010

It makes a lot more sense now; thanks for the explanations and additional resources. It feels absurd to mark most of the responses as best but sometimes the sum of the parts is greater than the whole :)

Thanks again
posted by Ziggy Zaga at 4:38 PM on September 9, 2010

It is my understanding that mysqli_fetch_array() retrieves an array of results and stores it to $result

I think the reason this is confusing is the word 'array' in the name of the function. The function only ever returns one row of results, so if your query results in multiple rows you have to call the function multiple times. The 'array' in the name doesn't refer to the fact that it returns multiple results at once, it refers to the fact that the columns can be referenced by name in addition to numerical index. It's like "fetch_row() + associative array = fetch_array()". If you used fetch_row() instead of fetch_array() you'd have to write "$jokes[] = $row[0];". This is generally inconvenient because nobody wants to go back and look at the query and count which index number corresponds to which column, so being able to refer to a column by name instead of number is really helpful.
posted by Rhomboid at 5:52 AM on September 10, 2010

« Older Waiting for biopsy results   |   How can I stay above the fray? Newer »
This thread is closed to new comments.