PHP Foreach on MySQLi Result set

Question

I have this chunk of code which works on my local XAMPP testing server. The problem is, when pushed to the production server, it breaks. This isn't an issue with the database connection, and PHP/MySQL are both at 5.3 on the production server so it isn't the use of old versions of either. I'm thinking it's the use of the foreach loop instead of the more standard while loop; if it's that, why?

<?php
   $res = $mysqli->query('DESCRIBE '.$table);
   $columnCount = 0;
   echo '<ul>';
   foreach($res as $field) {
     if(in_array($field["Field"], $sheetData[0])) {
      echo '<li>';
            //var_dump($field);
      echo $field['Field'].' - '.$field['Type'];
      echo "</li>\r\n";
      $columnCount++;
    }
  }
  echo '</ul>';
?>

EDIT: To clarify, it breaks by not outputting anything at all; when inserting a simple echo statement inside the loop, it seems to not even execute it.

EDIT2: I've added an answer below which goes into slightly more detail about what the problem here actually was and why this code does actually work under certain conditions.

How does it break? What errors are you getting back from the database? — andrewsi, May 24 '13 at 15:22
check `phpinfo` and make sure the extension for mysql is enabled, and add some error checking to your code — Manse, May 24 '13 at 15:22
@ManseUK: I've checked that, and yes MySQL is enabled. There was error checking before, but now it's been removed to keep this algorithm as simple as possible. — moberemk, May 24 '13 at 15:29
You need to `$res->fetch_assoc()` from that. You can't iterate the result resource, as it isn't an array. http://www.php.net/manual/en/mysqli-result.fetch-assoc.php — Michael Berkowski, May 24 '13 at 15:29
Do you really want to `foreach()` on `$res`? Isn't that the entire result set? — brbcoding, May 24 '13 at 15:30
@moberemk - Have you checked `$mysqli->error()` to make sure the query is working? — andrewsi, May 24 '13 at 15:30
@MichaelBerkowski I've actually switched the code to that to no effect. Also, like I said in the question, doing that DOES work normally on my server without issue. — moberemk, May 24 '13 at 15:34
@moberemk I can't believe that it normally works. That isn't how MySQLi result statements work. You can do that with PDO in some circumstances, but not MySQLi - it requires a fetch call in a while loop. (unless your `$mysqli` is some extending class which queries and fetches in your `query()` call, but normal `mysqli::query()` returns a non-iterable result resource. — Michael Berkowski, May 24 '13 at 15:36
@MichaelBerkowski I completely agree with you that it SHOULDN'T work based on what I understand about MySQLi, but the fact is that it still does for whatever reason. Now I'm wondering if maybe it's a XAMPP issue where I have an extended version of MySQLi which can be iterated over; something to look into. — moberemk, May 24 '13 at 15:43
@moberemk You need to enable error reporting on the server to see what is breaking. `error_reporting(E_ALL); ini_set('display_errors', 1);` — Michael Berkowski, May 24 '13 at 15:44

score 6 · Answer 1 · answered Sep 09 '14 at 13:20

So since I asked this question ages ago I figure I should update it with some additional clarification by saying this: what I did first with the foreach loop does work. The caveat being that it only works in PHP 5.4+ as that's when the mysqli_result class implemented the Traversable interface. This means you can iterate over it using a foreach loop in later versions of PHP.

This change apparently wasn't super well-known at the time I posted my question (mid-2013) likely due to the fact that so many servers across the internet still use 5.3--likely because that's the latest version of PHP available to Ubuntu 12.x--which limits its utility to recently updated servers. But when you're in an environment that supports it this is a totally valid technique to use.

score 4 · Accepted Answer · answered May 24 '13 at 15:39

Do this instead:

if ($result = $mysqli->query('DESCRIBE ' . $table)) {
    $columnCount = 0;
    echo '<ul>';

    /* fetch associative array */
    while ($field = $result->fetch_assoc()) {
        if (in_array($field["Field"], $sheetData[0])) {
            echo "<li>$field[Field] - $field[Type]</li>\r\n";
            $columnCount++;
        }
    }

    echo '</ul>';

    /* free result set */
    $result->free();
}

PHP Foreach on MySQLi Result set

2 Answers2

Linked