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? — user3849602
– user3849602, Commented Aug 17, 2015 at 15:34
Oh god, you're actually inserting in a loop? Don't ever do that. Read this. — Andrei
– Andrei, Commented Aug 17, 2015 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
– Madness, Commented Aug 17, 2015 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
– Innerwolf, Commented Aug 17, 2015 at 15:37
@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
– Don't Panic, Commented Aug 17, 2015 at 15:46

Professor Abronsius · Accepted Answer · 2015-08-17 15:41:40Z

2

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 );

answered Aug 17, 2015 at 15:41

Professor Abronsius

33.9k5 gold badges36 silver badges49 bronze badges

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

1 Comment

Innerwolf Over a year ago

This didn't work out of the box, but I'll try tweaking it until it does. Looks Promising!

Laurence Frost · Accepted Answer · 2015-08-17 15:42:48Z

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, 2015 at 15:42

Laurence Frost

3,0234 gold badges34 silver badges49 bronze badges

3 Comments

Innerwolf Over a year ago

Unfortunately, this did not execute.

user3849602 Over a year ago

Aren't you missing a ; between each INSERT query?

Laurence Frost Over a year ago

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');";

Innerwolf · Accepted Answer · 2015-08-17 21:05:37Z

0

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!

answered Aug 17, 2015 at 21:05

Innerwolf

571 silver badge7 bronze badges

Collectives™ on Stack Overflow

PHP array to MYSQL insertion stops after 11,000 rows

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related