0

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.

John Smith
  • 6,129
  • 12
  • 68
  • 123
  • 2
    Can you not include `$myConditionsMet` into your `SELECT` query and then just run a big `INSERT ... SELECT` wholly on the MySQL server? – Nick May 03 '20 at 22:56
  • unfortunatly not. Its a big JSON string which will be filtered out by Php – John Smith May 04 '20 at 07:19
  • 1
    Have you tried using a second connection for the inserts? – Nick May 04 '20 at 07:21
  • @Nick not yet, but if you are sure it could work, I will soon – John Smith May 04 '20 at 11:30
  • 1
    Not certain, but it shouldn't be too expensive to try? The other alternative is to chunk up the reads and writes into quantities that will fit into your memory space, and that would seem like a lot more effort... – Nick May 04 '20 at 11:46

0 Answers0