What is the difference between iterating a result set object with a foreach versus calling mysqli_fetch_assoc() in a while loop?

Question

I have a bit of dilemma, In PHP both foreach loop and while loop seem to do exactly the same thing in this situation:

foreach ($execute->result as $item) {
    echo $item['user_pass'] . '<br />';
}

AND

while ($row = mysqli_fetch_assoc($execute->result)) {
    echo $row['user_pass'] . '<br />';
}

• My question is are there any real differences?
• When would be better to use one over another or is it fine to use both?
• Does any of these two give you potential greater flexibility?

Answer 1

In this case they're exactly equivalent. The mysqli_result implements the Traversable interface (as documented here: http://www.php.net/mysqli_result ) so it can be iterated using a foreach loop as well.

I suppose that, internally, calling foreach on a mysqli_result object performs the calls to mysqli_fetch_assoc as well.

I don't think it makes any difference to use the first method or the second; however, the first one looks prettier and "more PHP5" to me!
Speaking of flexibility, the first one is certainly more flexible as well, as you can use that code with any object that implements Traversable and not just mysqli_result.

Answer 2

I found this:

for vs foreach vs while which is faster for iterating through arrays in php

the answer states that a difference in speed is so minimal that hardly matters which you use.

I like using foreach loops to iterate over returned information because I have access to the keys and values, which can be handy depending on the needed use.

$returned = array(array("userName" => "Tim"),array("userName" => "Bob"), array("userName" => "Guy"));

foreach($returned as $L1)
{
  foreach($L1 as $L2 => $val)
  {
      if($L2 === "userName")
      {

         echo 'Hello: ' . $val . "<br />";

      }

  }

}

I guess it depends on what values you need, how much control you need, and where you need it.

Answer 3

Iterating a query result set object via foreach:

1. More concise syntax.
2. No need to write/call iterated "_fetch" methods.
3. The ability to use "array destructuring syntax" in the signature to narrow the row payload or otherwise manipulate value assignments.
   foreach ($result as ['fav_column' => $fav]) { echo "$fav\n"; }
4. Row data can only be accessed using array syntax with associative keys.
   It is trivial, though, to convert row arrays to row objects in the loop body.
   And arguably, you could nominate increasing numeric column aliases from 0 -- then the array could be considered "indexed" ...meh.
   SELECT col_name AS '0', another_col AS '1' ...
5. If a row "counter" variable is advantageous while iterating, declaring $i (or some other name) as the variable to hold the first level keys will conveniently perform the incrementation.
   foreach ($result as $i => $row) { ... }

Iterating a query result set object via while:

1. Less concise syntax.

2. More versatile tooling to dictate the keys /data types (indexed, associative, or both), and the payloads depending on the fetch method mode (there are many).
   while ($row = $result->fetch_row()) { ... } // indexed row data
   while ($row = $result->fetch_array()) { ... } // both indexed & assoc data
   while ($obj = $result->fetch_object()) { ... } // rows as objects

3. If additional loop-breaking conditions are appropriate, then they can be cleanly baked into the while() condition.
   while ($someFlag === true && $row = mysqli_fetch_assoc($result)) { ... }

4. If a row counter variable is needed, it must be declared and incremented manually by some other process.
   $i = 0; while ($row = mysqli_fetch_assoc($result)) { ... ++$i; }

---

Ultimately, my advice would be to use a foreach() unless you have a sound reason to use while() with a _fetch() call.

---

Topical note: Source

8.0.0 mysqli_result implements IteratorAggregate now.
Previously, Traversable was implemented instead.