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

Question

I want to loop through the result set of the following query:

select uid from userbase

I am currently employing the following loop, but I can get only the first value.

$i = 0;
$output = mysqli_query($mysqli, "select uid from userbase");
while ($row = $output->fetch_array()) {
    $deviceToken = $row[$i];
    echo $deviceToken;
    $i++;
}

What might be the problem? Is it fetch_array()?

Answer 1

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.