How to iterate 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") or die(mysqli_error($mysqli));
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.

Answer 2

You should try using the fetch() function instead and you need not initialize a variable which you'll for this as while will display all the rows that are increment affected by your query.

So your new code should be;

while($row = $output->fetch()){

        $deviceToken = $row['uid'];
        echo '$deviceToken<br>';
}

Answer 3

You need to define a array and store your data into array inside loop . Use MYSQLI_ASSOC no need for incremented value

$deviceToken=array();
while ($row = $output->fetch_array(MYSQLI_ASSOC)) {
    $deviceToken[] = $row['uid'];
}
print_r($deviceToken);
for($i=0;$i<=count($deviceToken);$i++){
echo $deviceToken[$i];
}