How do I iterate over the results in a MySQLi result set?

by

You will notice while researching the PHP manual at https://php.net/manual/en/mysqli-result.fetch-array.php that fetch_array() has the default behavior of generating a result set that contains both indexed and associative keyed elements (MYSQLI_BOTH).

You could use either MYSQLI_ASSOC …

while ($row = $output->fetch_array(MYSQLI_ASSOC)) {
    echo $row['uid'];
}

or MYSQLI_NUM…

while ($row = $output->fetch_array(MYSQLI_NUM)) {
    echo $row[0];
}

That said, there is actually an easier, more brief, and more efficient way because MySQLi’s query() can be used as an iterable object. The step of calling fetch_array() on every iterated row can be completely omitted. You can write your $output into a foreach() and away you go (refer to column values by the associative key).

foreach ($output as $row) {
    echo $row['uid'];
}

I do recommend that you use all “object oriented” syntax rather than procedural or a mix of styles. “Object oriented” syntax is more brief and in my opinion it is easier to read.

Finally, the way that your code is constructed, $i starts at 0 and increments with every row. However, your result set (with both styles of keys) will look something like this…

[
    0 => [0 => 1, 'uid' => 1],
    1 => [0 => 2, 'uid' => 2],
    2 => [0 => 3, 'uid' => 3]...
]

Your first iteration works because $output[0][0] (aka $row[0]) exists.
Your second iteration doesn’t work because $output[1][1] (aka $row[1]) doesn’t exist.
Nor does the third iteration with $output[2][2] (aka $row[2]) doesn’t exist. And so on.
You see, the iteration was truly the part that fouled up your script.

More Related Contents:

  1. Single result from database using mysqli
  2. Converting MySQL result array to JSON [duplicate]
  3. mysqli_fetch_array while loop columns
  4. Retrieving Multiple Result sets with stored procedure in php/mysqli
  5. mysqli query results to show all rows
  6. Using a variable outside of the while loop (scope)
  7. PHP – Flushing While Loop Data with Ajax
  8. Object of class mysqli_result could not be converted to string
  9. SELECT COUNT(*) AS count – How to use this count
  10. Mysqli update throwing Call to a member function bind_param() error [duplicate]
  11. How to execute two mysql queries as one in PHP/MYSQL?
  12. mysqli bind_param() expected to be a reference, value given
  13. Should I manually check for errors when calling “mysqli_stmt_prepare”?
  14. mysqli_real_escape_string() expects exactly 2 parameters, 1 given
  15. What is the difference between MySQL, MySQLi and PDO? [closed]
  16. Mysql No connection could be made because the target machine actively refused it
  17. How to connect to MySQL database in PHP using mysqli extension?
  18. How do I count query result rows?
  19. Run a PHP script every second using CLI
  20. Bind Param with array of parameters
  21. How do you use IN clauses with mysqli prepared statements [duplicate]
  22. Fatal error: Uncaught exception ‘mysqli_sql_exception’ with message ‘No index used in query/prepared statement’
  23. How to fetch all in assoc array from a prepared statement?
  24. Fatal error: Call to undefined function mysqli_result()
  25. Why I am getting an error when using w3school tutorial? [duplicate]
  26. Using wildcards in prepared statement
  27. New containing div after every 3 records
  28. How to remove the fatal error when fetching an assoc array
  29. Fatal error: Call to undefined method mysqli_stmt::get_result()
  30. Strange problem with the length of the field gotten from database [duplicate]

Leave a Comment