Why my PHP loop got broken every time?

Question

I am a beginner to PHP. I am trying to insert large amount of items into database. I am using for loop and mysqli_query commands.

My pseudo-code is something like this:

for ($i=0; $i<50000; $i++) {
  mysqli_query($con, "INSERT INTO users (name, age) VALUES ('$name','$age'); ") ;

}

I have my code working a few hundred loops and then poof: Fatal error: Maximum execution time of 30 seconds exceeded in xxx on line xxx

For me, only solution is to manually increment my counter every time after loop breaks.

Is there any other solution? Please, help!

Check how many records are there and do the math how many you need to insert? — Royal Bg, Sep 25 '14 at 13:13
(Tip) Don't query whilst in a loop. Create the query within the loop, and execute out of the loop. That'll make it 1 query, rather than 50k. — ʰᵈˑ, Sep 25 '14 at 13:13
First remove semicolon at the end of query and use cron for big process — Rakesh Sharma, Sep 25 '14 at 13:13
Is the script timing out? Try the set_time_limit() function. Calling set_time_limit(0) will remove any time limits for execution of the script. — Jay Blanchard, Sep 25 '14 at 13:14
Why are you adding the same data to the database 50000 times?! — i alarmed alien, Sep 25 '14 at 13:14
Add error checking after the query: `mysqli_query($con, "...") or die (mysqli_error($con));`, and tell us the error message. — Barmar, Sep 25 '14 at 13:20
Some hosting services have a limit on the number of queries you can do in an hour. You're probably running into it, and the error message would confirm this. — Barmar, Sep 25 '14 at 13:22

score 5 · Answer 1 · answered Sep 25 '14 at 13:15

Don't do a query for every record one-at-time. You can do multiple inserts with one statement. The example below does 50 at one time. You can probably safely increase that to 100 or even 500.

$counter = 0;
$query = 'INSERT INTO users (name, age) VALUES ';
for ($i=0; $i<50000; $i++) {
  $counter++;
  $sql .= "('$name','$age'),"; 
  // execute query every 50 records
  if ($counter % 50 === 0) {
      $sql = rtrim($sql, ',');
      mysqli_query($con, $query . $sql) ;
      $sql = '';
  }
}

Seth McClaine · Answer 2 · 2014-12-11T15:26:51.107

0

Throw a try catch

for ($i=0; $i<50000; $i++) {
  try {
    mysqli_query($con, "INSERT INTO users (name, age) VALUES ('$name','$age'); ") ;
  } catch ($e) {
    // You can log $e here if you want
    // It would also probably be good to print out your query 
    // so you can go back and try it again later
  }

}

edited Dec 11 '14 at 15:26

answered Sep 25 '14 at 13:14

Seth McClaine

9,142
6
38
64

`mysqli_query()` doesn't signal errors, you need to use `or die(mysqli_error($con))` after the call. – Barmar Sep 25 '14 at 13:19
What is `catche`? A typo? – ʰᵈˑ Sep 25 '14 at 13:21
Yes it should be `catch` – Seth McClaine Dec 11 '14 at 15:25

score 0 · Answer 3 · answered Jan 06 '15 at 21:08

The most helpful answer was the comment from Jay Blanchard:

Try the set_time_limit() function. Calling set_time_limit(0) will remove any time limits for execution of the script.

I have inserted set_time_limit(0) at the beginning of the page, and my PHP script now works perfectly!

Why my PHP loop got broken every time?

3 Answers3