Two While Loops, Second Returns Empty Array?

Question

I'm experimenting with MySQLi and using the following code to check differences for how I should approach my array formatting/usage for fetch_array(MYSQLI_ASSOC);

here is my code:

include "Database.php"; 

$ArrayQuery = $mysqli->query("SELECT * FROM accountinformation");


while ($ArrayResults = $ArrayQuery->fetch_array(MYSQLI_ASSOC))
{
    echo count($ArrayResults);
    echo "<br>";
}
echo "<br><br><bR><br>";
    $Empty = array();
while ($ArrayResult = $ArrayQuery->fetch_array(MYSQLI_ASSOC))
{
    foreach ($ArrayResult AS $ArrayRes)
    {
        $Empty[] = $ArrayRes;
    }
}

print_r($Empty);

The problem is, that i'm using the same Variable for my while loop, the first one returns 3 then 3 Which is expected.

But the problem is, with my second query; it returns a blank array

array( ) when print_r();

and when I do:

while ($ArrayResult = $ArrayQuery->fetch_array(MYSQLI_ASSOC))
{
    print_r($ArrayResult);
}

For my Second while loop, it returns nothing for output.

I have checked my variables $ArrayResults and $ArrayResult are not duplicates, they are in fact unique.

Why is my second while loop returning nothing when my first one is working?

Update

When I produce a second query into the mixture with a different starting variable:

$ArrayQuer = $mysqli->query("SELECT * FROM accountinformation");

and modify my second while loop:

while ($ArrayResult = $ArrayQuer->fetch_array(MYSQLI_ASSOC))
{
    print_r($ArrayResult);
}

I get the expected output? So is it a case of MySQLi not allowing the same parameters to be used twice throughout the script?

Why do you expect it to fetch anything after it has already finished fetching all the results? Rewind it to the first row using $ArrayQuery->data_seek(0), or just use the already fetched result array. — user1233508
– user1233508, Commented Dec 23, 2012 at 5:51
@DCoder shoudln't it load up the data within my $ArrayQuery to be used in another while loop? I understand it's not exactly in best practice to use a situation like this; but i'm just finding out the kinks within MySQLI and seeing what would work; to what doesnt. Because if i move over to mysql_* functions; It would produce the expected output by using $ArrayQuery Twice in two different while loops. — user1902584
– user1902584, Commented Dec 23, 2012 at 5:55
Look at the activity in your first code sample: 1) run a query 2) fetch all of its results 3) fetch all of its results again. Your update inserts another step, "run another query", before step 3, and you get the result you expect. This is completely identical to the way mysql_* functions would handle the situation. — user1233508
– user1233508, Commented Dec 23, 2012 at 5:59
But I don't see the point in running two queries to return the exact same thing. I wont use this setup in a production environment; but this is for my own tweaks and kinks book. — user1902584
– user1902584, Commented Dec 23, 2012 at 6:02
$ArrayQuery internally keeps track of the index of the next row to be fetched. When your first while loop ends, $ArrayQuery has fetched all the results and this index is equal to the number of rows fetched. Your second while loop then asks it to for each remaining result, but there are no further results to give, because the first loop took it all and the index says so. When you rewind $ArrayQuery using Daryl Gill's answer (which is exactly what I suggested in my first comment), this internal index is reset and the second loop starts from the first row again. — user1233508
– user1233508, Commented Dec 23, 2012 at 6:14

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

mysqli_data_seek

Adjusts the result pointer to an arbitrary row in the result

while ($ArrayResults = $ArrayQuery->fetch_array(MYSQLI_ASSOC))
    {
        echo count($ArrayResults);
        echo "<br>";
    }
    echo "<br><br><bR><br>";
    mysqli_data_seek($ArrayQuery,0); // Addition Made Here
        $Empty = array();
    while ($ArrayResult = $ArrayQuery->fetch_array(MYSQLI_ASSOC))
    {
        foreach ($ArrayResult AS $ArrayRes)
        {
            $Empty[] = $ArrayRes;
        }
    }
    print_r($Empty);

To re-use an already fetched array; you should use mysqli_data_seek(); (Notice I have added it above your `$Empty Variable) This should be the problem.

See the manual here:

http://us.php.net/manual/en/mysqli-result.data-seek.php

Think of this scenario; Why would you re-buy something you already own?

Fits perfectly in your case

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 23, 2012 at 5:59

Daryl Gill

5,52511 gold badges41 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Two While Loops, Second Returns Empty Array?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related