Page Not Loading when Importing MySQL Data

[berridgeab]

Hi,

 

I got a problem which I have spent the past two hours trying to figure out but just can't work out where im going wrong.

 

Basically I have a CSV file with about 3,500 - 4,500 records that need to be imported into a MySQL table everyday. Here is the function I have created to do the Import: -

 

function InsertIntoDatabaseJobInfo($filename)
{
        
     	$handle = fopen("$filename", "r");
$Record = 0;
$query = "TRUNCATE job_info";
mysql_query($query) or die(mysql_error());

 while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
     	{
       		$import="INSERT into job_info(OnlineJobImportDate, JobNumber, DateJobRaised, EngineerAccNo, CallType, ModelNo, DOP, DirectlyBooked, EngineerVisitDate, CustomerSurname, FlatNo, Address, Address2, Address3, Address4, Postcode) 
values(now(),'$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]','$data[8]','$data[9]','$data[10]','$data[11]','$data[12]','$data[13]','$data[14]')"; 
   		$Record++; 
       		mysql_query($import) or die(mysql_error());
     	}	
     fclose($handle);
 echo "Delete done, old data deleted.<BR>Import done, $Record Rows added.";
}

 

This function basically gets passed a Filename, and then will check if that filename exists in the local Download folder. If the file exists then it opens the CSV file and puts it line by line into the MySQL Database.

 

This function Works fine on my Local Machine, be there 1 Row in the CSV file, or 4000 rows, it will import the data into MySQL perfectly and report the success message to the user.

 

However, when I upload it to the Server, if the CSV file is anything bigger than say 1200-1400 lines/records, the page will just sit there Loading. If I make sure the CSV is under 1200 lines/records, then it works fine (Inputs the data and brings up the success message).

 

If it is over this amount (Say I try to put 2000 Records in), then it will sit there as if it is trying to load the page (Loding icon continues animation) but never actually loads anything. Its like its timeing out. However if I check the MySQL database after I attempt it, the data will be there, it just doesn't execute the last part of the script where it brings up the Success message.

 

Another thing I think I should mention is, the process of importing about 3000 CSV records takes about 3-4 minutes using this function. As long as the CSV takes no longer a minute to run (60 seconds, Approximately 1200 records) then the data is input and php outputs the sucess message to the user. If it takes ANY LONGER than 60 seconds, this is where the problem occurs, the data is put into the database, but it never omits the success message to the user. Sorry this is such a long winded first post but I dont know where else to turn. I can suppply more information if nesssacary.

[php_dave]

Do you have error reporting on?

 

I would guess that your max script execution time is being breached..  i think the default is 30 seconds.

 

[berridgeab]

Do you have error reporting on?

 

I would guess that your max script execution time is being breached..  i think the default is 30 seconds.

 

 

Thank you for your fast response, thats what I thought, so I changed the PHP settings in the INI file, restarted the server and confirmed they were implemented using phpinfo(): -

 

;;;;;;;;;;;;;;;;;;;
; Resource Limits ;
;;;;;;;;;;;;;;;;;;;

max_execution_time = 240     ; Maximum execution time of each script, in seconds
max_input_time = 240	; Maximum amount of time each script may spend parsing request data
memory_limit = 16M      ; Maximum amount of memory a script may consume (8MB)

;;;;;;;;;;;;;;;;;;
; Fopen wrappers ;
;;;;;;;;;;;;;;;;;;

; Whether to allow the treatment of URLs (like http:// or ftp://) as files.
allow_url_fopen = off

; Define the anonymous ftp password (your email address)
;from="john@doe.com"

; Define the User-Agent string
; user_agent="PHP"

; Default timeout for socket based streams (seconds)
default_socket_timeout = 240


[MySQL]
; Allow or prevent persistent links.
mysql.allow_persistent = On

; Maximum number of persistent links.  -1 means no limit.
mysql.max_persistent = -1

; Maximum number of links (persistent + non-persistent).  -1 means no limit.
mysql.max_links = -1

; Default port number for mysql_connect().  If unset, mysql_connect() will use
; the $MYSQL_TCP_PORT or the mysql-tcp entry in /etc/services or the
; compile-time value defined MYSQL_PORT (in that order).  Win32 will only look
; at MYSQL_PORT.
mysql.default_port =

; Default socket name for local MySQL connects.  If empty, uses the built-in
; MySQL defaults.
mysql.default_socket =

; Default host for mysql_connect() (doesn't apply in safe mode).
mysql.default_host =

; Default user for mysql_connect() (doesn't apply in safe mode).
mysql.default_user =

; Default password for mysql_connect() (doesn't apply in safe mode).
; Note that this is generally a *bad* idea to store passwords in this file.
; *Any* user with PHP access can run 'echo get_cfg_var("mysql.default_password")
; and reveal this password!  And of course, any users with read access to this
; file will be able to reveal the password as well.
mysql.default_password =

; Maximum time (in secondes) for connect timeout. -1 means no limit
mysql.connect_timeout = 300

; Trace mode. When trace_mode is active (=On), warnings for table/index scans and
; SQL-Errors will be displayed.
mysql.trace_mode = Off

 

 

However this has not helped. Further to my first post, I have managed to upload the Web application on another Local testing machine that runs IIS and not Apache and it works on that as well with no problems (Imports data and shows user the success message). I have fed all 3800 Lines of the complete CSV file into the MySQL database on both local machines, so I know there are no values in the CSV that would cause the MySQL Query to mess up. For some bizzare reason, if the script exceeds more than 60 seconds of inputting data, it just stops. There is no error message in PHP, nothing generated in the Log files of MySQL, im stumped. However the fact that it only does this after 60 seconds (And it works on two different local testing servers, one configured for IIS, one for Apache) tells me its a setting somewhere, I just don't know where. Any further thoughts or comments would be appreciated.