PHP PDO Insert Statement exceeding execution time

Question

I am inserting bulk values using prepared statements, assigning values and execution is done in loop still it is taking too long and exceeding the execution limit.

Here is my code:

if(isset($_GET['mov_id']) && isset($_GET['season']))
{
$id= $_GET['mov_id'];
$season = $_GET['season'];
$stmt = $DB_con->prepare("SELECT * FROM `mov_movies` where moviesID =:id");
$stmt->execute(array(':id'=>$id));
$row=$stmt->fetch(PDO::FETCH_ASSOC);
$imdbID = $row['IMDBid'];


if(isset($_POST['btn-addmovie']))
{
$episodes = trim($_POST['tepisodes']);

    $name = "";
     $episode = "";
     $DOR = "";
     $IMDB = "";
     $drive = "";
     $cdn = "";

     $stmt = $DB_con->prepare("DELETE FROM `mov_season` where movId =:id");
     $stmt->execute(array(':id'=>$id));
     $count = 1;

    $stmt = $DB_con->prepare("INSERT INTO `mov_season` (`movId`, `season`, `episode`, `releaseDate`, `IMDB`, `driveLink`, `cdnLink` , `title`) VALUES (:id, :season, :episode,:releaseDate, :IMDB,:drive,:cdn,:name)");
    $stmt->bindparam(":id", $id);
       $stmt->bindparam(":season", $season);
       $stmt->bindparam(":episode", $episode);  
       $stmt->bindparam(":releaseDate", $DOR);  
       $stmt->bindparam(":IMDB", $IMDB ); 
       $stmt->bindparam(":drive", $drive ); 
       $stmt->bindparam(":cdn", $cdn );
       $stmt->bindparam(":name", $name);
     while($count < $episodes){

     $name = trim($_POST['name'.$count]);
     $episode = trim($_POST['episode'.$count]);
     $DOR = trim($_POST['DOR'.$count]);
     $IMDB = trim($_POST['imdb'.$count]);
     $drive = trim($_POST['drive'.$count]);
     $cdn = trim($_POST['cdn'.$count]);

     if(($drive != "") || ($cdn = "")){



       $stmt->execute();

       $count++;
     }




    }
     $success = "Successfully Updated!";



 }
 }

Statement is taking place in if loop inside while loop. And even for only one execution it exceeds 120 sec of execution time.

how many records we talking here? Also this isn't a proper bulk insert, you're executing a statement every record, rather than say 1 statement per 1,000 records. — Jonathan, Apr 10 '17 at 05:42

score 0 · Answer 1 · edited May 23 '17 at 11:46

0

Try using bulk insert. In this you have to execute on single query which will insert multiple records

PDO Prepared Inserts multiple rows in single query

edited May 23 '17 at 11:46

Community

1
1

answered Apr 10 '17 at 05:45

Nishant Nair

1,999
1
13
18

Jonathan · Accepted Answer · 2017-04-10T05:58:47.527

0

This is your problem. You're only incrementing $count if this condition is true, which means you're probably in an infinite loop.

if(($drive != "") || ($cdn = "")){
    $stmt->execute();
    $count++;
}

Do this instead, so $count is always incremented.

if(($drive != "") || ($cdn = "")){
    $stmt->execute();
}
$count++;

edited Apr 10 '17 at 05:58

answered Apr 10 '17 at 05:49

Jonathan

2,778
13
23

PHP PDO Insert Statement exceeding execution time

2 Answers2