PHP array to MYSQL insertion stops after 11,000 rows

Question

currently, I'm getting a 2-dimensional PHP array of data by downloading a csv file from an URL and parsing it with str_getcsv().

After that, I loop through each line and insert the values to the corresponding SQL columns:

foreach($array as $a=>$value){

    //defining variables

    $sql = "INSERT INTO Ranking (ra, na, sc, kd, wi, ki, de, sh, ti)
           VALUES ('$ra','$na','$sc','$kd','$wi','$ki','$de','$sh','$ti')";

    $conn->query($sql);
}

$conn->close();

I have about 16,000 lines of data but weirdly, the insertion stops after exactly 10,999 rows. Thats about 929 kb.

I can't find a reason since my hosting provider states that there is no row limit. I don't think that the execution of the PHP file stops due to an execution time limit because after this insertion command the array also gets stored locally and that happens about 4 seconds after the file gets downloaded.

Does the foreach()-loop have some kind of a time limit? Does it stop after a certain number of executions? Would it work if I only connected once with just ONE sql query?

UPDATE:

Turns out the sql import time exceedet.

Fatal error: Maximum execution time of 10 seconds exceeded
in /home/mainhost/public_html/phpMyAdmin/libraries/import/sql.php on line 118

(output of manual insertion try in phpmyadmin)

What do i do? split the input to two queries?

Do you display PHP errors? They tell you nothing? Does `count($array)` gives a result > 10,999? — , Aug 17 '15 at 15:34
Oh god, you're actually inserting in a loop? Don't ever do that. Read [this](http://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql). — Andrei, Aug 17 '15 at 15:35
You do have a limited amount of execution time and also memory you can use. Run this script from the CLI to determine if your script is dying, or if it is the processor. — Madness, Aug 17 '15 at 15:35
I don't display errors but the file only gets executed successfully by the server anyways (cronjob) due to different file paths. I could try that later. And I know that the length is > than 10,999 because of the locally stored file — Innerwolf, Aug 17 '15 at 15:37
`I know that the length is > than 10,999 because of the locally stored file` => How do you know there hasn't been an error when reading the file with PHP? Though it's not the most likely error. — , Aug 17 '15 at 15:43
@Andrew I agree, but it is actually possible to let someone know about something that you know about without phrasing it like they're stupid for not already knowing it. — Don't Panic, Aug 17 '15 at 15:46

score 2 · Answer 1 · answered Aug 17 '15 at 15:41

I do not know if the following would help matters - you might need to tweak it but as said above, inserting that many times in a loop ....

Build the sql after populating the values array and execute once.

$values=array();
foreach( $array as $a => $value ){
    $values[]="('$ra','$na','$sc','$kd','$wi','$ki','$de','$sh','$ti')";
}
$sql="INSERT INTO `Ranking` (`ra`,`na`,`sc`,`kd`,`wi`,`ki`,`de`,`sh`,`ti`)
      values ".implode( ','.PHP_EOL, $values ).";";

$conn->query( $sql );

This didn't work out of the box, but I'll try tweaking it until it does. Looks Promising! — Innerwolf, Aug 17 '15 at 15:56

score 1 · Answer 2 · answered Aug 17 '15 at 15:42

1

I know this doesn't directly answer your question, but a better work-around would be this:

$sql = '';

foreach($array as $a=>$value){

    //defining variables

    $sql .= "INSERT INTO Ranking (ra, na, sc, kd, wi, ki, de, sh, ti)
           VALUES ('$ra','$na','$sc','$kd','$wi','$ki','$de','$sh','$ti')";

}

$conn->query($sql);

$conn->close();

The reason being that you then only have 1 round trip to the DB. Regardless of if this is just a local connection or not, it's still more efficient to batch your inserts together. This should get you around the limitation you're seeing at the same time.

answered Aug 17 '15 at 15:42

Laurence Frost

2,759
3
28
45

Unfortunately, this did not execute. – Innerwolf Aug 17 '15 at 15:51
Aren't you missing a `;` between each `INSERT` query? – Aug 17 '15 at 15:54
Sorry yes you are right. Row should read: $sql .= "INSERT INTO Ranking (ra, na, sc, kd, wi, ki, de, sh, ti) VALUES ('$ra','$na','$sc','$kd','$wi','$ki','$de','$sh','$ti');"; – Laurence Frost Aug 17 '15 at 16:14

score 0 · Accepted Answer · answered Aug 17 '15 at 21:05

Came up with a solution!

Since looping through all of the rows turned out to be not the right way and querying the array as a whole exceeded the import time, I combined them, so that i.e. 6000 rows get imported at once and this worked just fine for me!

foreach($array as $a=>$value){
    $i += 1;
    if(!fmod($i / 6000, 1) == 1){
        $sql = substr($sql, 0, -1);
        $conn->query($sql);
        $sql = "INSERT INTO Ranking (rank, name, score, kd, wins, kills, deaths, shots, time) VALUES ";
    }

    //defining variables

    $sql .= "('$ra',' $na ','$sc','$kd','$wi','$ki','$de','$sh','$ti'),";
}

$sql = substr($sql, 0, -1);

Have a good day!

PHP array to MYSQL insertion stops after 11,000 rows

3 Answers3