CODEWITHSUNDEEP
phpmysqlunbuffered-queries
John Smith
John Smith

Reputation: 6197

How to run unbuffered SELECT and run another INSERT in the meantime? (Php, MYSQLI_USE_RESULT, Commands out of sync; you can't run this command now)

I have to filter and add records from a master table. This table has millions of records. First I was trying:

$result = mysqli_query($myConnection, 'SELECT * FROM master');
while($record = mysqli_fetch_assoc($result))
{
    if ($myConditionsMet)
    {
        mysqli_query($myConnection, 'INSERT INTO filtered VALUES blahblahblah');
    }
}

now the first problem I faced is, I got memory overflow. (Yes, I used to belived that normally Php doesn't get all the records, but one by one, so it's a minimal memory usages as one record erases the previous, but the man always learns something new everyday). I came upon a useful flag (MYSQLI_USE_RESULT), so:

$result = mysqli_query($myConnection, 'SELECT * FROM master', MYSQLI_USE_RESULT);
while($record = mysqli_fetch_assoc($result))
{
    if ($myConditionsMet)
    {
        mysqli_query($myConnection, 'INSERT INTO filtered VALUES blahblahblah');
    }
}

but this time I get a miserable error: Commands out of sync; you can't run this command now. For now on, I can't imagine what to do with it. Even I collect into an $array the records of master, still memory overflow. I can't belive I'm not allowed to run an INSERT INTO while fetching with SELECT FROM, I can't see the way why the two clashes, it doesn't make sense.

Upvotes: 0

Views: 58

Answers (0)

Related Questions