CODEWITHSUNDEEP
php
user752336
user752336

Reputation: 23

maximum execution time of 30 seconds exceeded php

When I run my script I receive the following error before processing all rows of data.

maximum execution time of 30 seconds exceeded

After researching the problem, I should be able to extend the max_execution_time time which should resolve the problem.

But being in my PHP programming infancy I would like to know if there is a more optimal way of doing my script below, so I do not have to rely on "get out of jail cards".

The script is:

1 Taking a CSV file

2 Cherry picking some columns

3 Trying to insert 10k rows of CSV data into a my SQL table

In my head I think I should be able to insert in chunks, but that is so far beyond my skillset I do not even know how to write one line :\

Many thanks in advance

<?php
function processCSV()
{
    global $uploadFile;
    include 'dbConnection.inc.php';
    dbConnection("xx","xx","xx");
    $rowCounter = 0;
    $loadLocationCsvUrl = fopen($uploadFile,"r");   
    if ($loadLocationCsvUrl <> false)
    {
        while ($locationFile = fgetcsv($loadLocationCsvUrl, ','))
        {
            $officeId = $locationFile[2];
            $country = $locationFile[9];
            $country = trim($country);
            $country = htmlspecialchars($country);
            $open = $locationFile[4];
            $open = trim($open);
            $open = htmlspecialchars($open);
            $insString = "insert into countrytable set officeId='$officeId', countryname='$country', status='$open'";
                switch($country)
                {
                    case $country <> 'Country':
                        if (!mysql_query($insString))
                        {
                            echo "<p>error " . mysql_error() . "</p>";    
                        }
                        break;
                }
            $rowCounter++;
        }
        echo "$rowCounter inserted.";
    }
    fclose($loadLocationCsvUrl);
}
processCSV();
?>

Upvotes: 2

Views: 3421

Answers (3)

chx
chx

Reputation: 11790

First, in 2011 you do not use mysql_query. You use mysqli or PDO and prepared statements. Then you do not need to figure out how to escape strings for SQL. You used htmlspecialchars which is totally wrong for this purpose. Next, you could use a transaction to speed up many inserts. MySQL also supports multiple interests.

But the best bet would be to use the CSV storage engine. http://dev.mysql.com/doc/refman/5.0/en/csv-storage-engine.html read here. You can instantly load everything into SQL and then manipulate there as you wish. The article also shows the load data infile command.

Upvotes: 4

DanielB
DanielB

Reputation: 20230

Well, you could create a single query like this.

$query = "INSERT INTO countrytable (officeId, countryname, status) VALUES ";
$entries = array();
while ($locationFile = fgetcsv($loadLocationCsvUrl, ',')) {
    // your code
    $entries[] = "('$officeId', '$country', '$open')";
}

$query .= implode(', ', $enties);
mysql_query($query);

But this depends on how long your query will be and what the server limit is set to.

But as you can read in other posts, there are better way for your requirements. But I thougt I should share a way you did thought about.

Upvotes: 1

Jerry Saravia
Jerry Saravia

Reputation: 3837

You can try calling the following function before inserting. This will set the time limit to unlimited instead of the 30 sec default time.

set_time_limit( 0 );

Upvotes: 0

Related Questions