CODEWITHSUNDEEP
phpmysql
Mike
Mike

Reputation: 6839

500 Internal Server Error PHP timing out

I am receiving an internal server error for my PHP script timing out. I am taking 8000 rows from mySQL database and breaking each row into 3 different tables.

I did some research on stack and ran into this as a solution:

set_time_limit(0);
ignore_user_abort(1);

Now, I am just not receiving the error anymore in chrome, but still not adding to the database.

Here is a little bit of pseudo of what I am doing:

$db = new myConnectDB();
$query = "SELECT * FROM tableName";

$result = $db->query($query);
$numRows = $result->num_rows;


//beerName', '$style' , '$brewery', '$abv','$ibu','$breweryID', '$icon', '$iconBrew' , '$lat', '$long' , '$beerRating')";

for ($i=0; $i < 8000 ; $i++) { 

  //fetch associate
  $row = $result->fetch_assoc();

  //get all 13 variables from rom 

  $db2 = new myConnectDB();
  //insert some variables into 1st table
  mysqli_close($db2);

  //todo: insert BeerTable / check and 
  $db3 = new myConnectDB();
  //insert variables into second table
  mysqli_close($db3);

  //todo: insert BreweryTable / check and update
  $db4 = new myConnectDB();
  //insert variables into third table. 
  mysqli_close($db4);

}

echo $i;

Upvotes: 0

Views: 261

Answers (1)

Andy J.
Andy J.

Reputation: 354

This is bad practice to establish and break connection inside a loop. Try to refactor your code this way:

$db = new myConnectDB();
$db2 = new myConnectDB();
$db3 = new myConnectDB();
$db4 = new myConnectDB();
$query = "SELECT * FROM tableName";

$result = $db->query($query);
$numRows = $result->num_rows;

$insertQuery1 = "INSERT INTO tbl1 (col1, col2, col3) VALUES";
$insertQuery2 = "INSERT INTO tbl2 (col1, col2, col3) VALUES";
$insertQuery3 = "INSERT INTO tbl3 (col1, col2, col3) VALUES";
for ($i=0; $i < 8000 ; $i++) { 

    //fetch associate
    $row = $result->fetch_assoc();

    //get all 13 variables from rom 

    //insert some variables into 1st table
    $insertQuery1 .= '(' . $val1 . ',' . $val2 . ',' . $val3 ')';

    //todo: insert BeerTable / check and 

    //insert variables into second table
    $insertQuery2 .= '(' . $val4 . ',' . $val5 . ',' . $val6 ')';

    //todo: insert BreweryTable / check and update

    //insert variables into third table. 
    $insertQuery1 .= '(' . $val7 . ',' . $val8 . ',' . $val9 ')';
}

$db1->query($insertQuery1);
$db2->query($insertQuery2);
$db3->query($insertQuery3);

mysqli_close($db2);
mysqli_close($db3);
mysqli_close($db4);

In this concept you won't query 3 inserts for each row. You collect 3 big insert queries and execute them after the loop. So this code would work faster.

Upvotes: 2

Related Questions