php mysqli/multi_query cannot be proceeded more then once

Question

I've got an array of strings which are sql "input into ..." queries. Im looping through this array combining each 50 of them into a new string and then sending to mysql database. After that I reset my temporary string and prepare it for new queries.

But my array stops after first query and doesn't want to send everything so on DB server I've always got only 50 records. How can I fix my code and send everything?

code:

$data // array with sql queries 
$queryPartial = ''; // additional string for 50 queries to send

           foreach ($data as $index => $queryToSend) {

                $queryPartial .= $queryToSend;

                // send partials query
                if($index % 50 == 0)
                {

                    if($connection->multi_query($queryPartial))
                    {
                        $this->output->writeln('succesfull query number: '.$index);
                    } 
                    $queryPartial = ''; // clean string for next queries
                }


           }   
              //send the rest of the remaining queries
             if($queryPartial !== ''){

                 if($connection->multi_query($queryPartial))
                 {
                    $this->output->writeln('rest of the queries sended');
                 } 
             }

          $connection->close(); 

Answer 1

To address why your code isn't working, I'd suggest looking at the full string of $queryPartial before you submit it as a query. I bet there's a syntax error because you're not separating the statements with ; or something like that.

Looking at the query itself, instead of your code, will probably make it more clear where the mistake is.

You should also check for error status returned by the mysqli functions. Most mysqli functions return FALSE if an error occurs, and you should check $connection->error for the error message. If you don't do that, errors might occur, but you'll never know.

I would guess that you're using multi-query to save on database calls, assuming that making fewer calls will help performance.

But the cost of making the call is small compared to the cost of the transactions that insert data. There's practically no benefit to using multi_query().

The following code does the same thing, has virtually the same performance, it's much simpler code:

foreach ($data as $index => $queryToSend) {
    $result = $connection->query($queryToSend);
    if ($result === false) {
        error_log("SQL: $queryToSend ERROR: {$connection->error}");
    }
}

(always check for error status returned by mysqli functions!)

If you're trying to speed up performance of bulk inserts, using multi_query() has insignificant benefit compared to other optimizations:

• Combine multiple rows into a single INSERT statement
• Combine multiple statements into a single transaction
• Use LOAD DATA INFILE instead of INSERT

See my presentation Load Data Fast! for performance comparisons.