Greetings all. I'm a bit of a hack at PHP programming, and programming in general so be gentle.
My code is not very elegant, but I have always managed to get things working.
I've got a script that gets run via CRON each night for several clients. The script reads data from a fixed length text
file and loads it into a MySQL database. It's worked fine until recently and I suspect the problem is assocaited with
a lack of efficiency. The problem I'm having is that the script runs several times for clients with a large number of
records (>30000) in their data file. The error I'm seeing is:
"HTTP request sent, awaiting response... End of file while parsing headers."
Again, this only happens on clients with a large number of records. The script isn't executed until several hours
after the datafile is uploaded, so I don't believe this is related to the upload of the datafile not being complete.
I've tried getting PHP to execute a LOAD DATA INFILE statement without any success. I have learned that some ISPs disable
this functionality due to a security issue and suspect that is the case here.
The script follows:
<?php
if (file_exists('txtfile1.txt')) {
$fd = fopen ('txtfile1.txt', "rb");
// Setup my database connection
require 'variables.php'; // Loads database connection variables from seperate file.
$db=mysql_connect ($host_name, $db_user_id, $db_pwd) or die ('I cannot connect to the database.');
mysql_select_db ($database_name);
// Delete the records from the database
$delete = mysql_query("DELETE FROM table1;");
if ($delete) {
while (!feof ($fd)) {
$buffer = fgets($fd, 1024);
$buffer = ereg_replace("'", "`", $buffer);
$field1 = substr($buffer, 0, 8);
$field2 = substr($buffer, 8, 6);
$field3 = substr($buffer, 14, 30);
$field4 = substr($buffer, 44, 3);
$field5 = substr($buffer, 47, 12);
$field6 = substr($buffer, 59, 6);
$field7 = substr($buffer, 65, 8);
$field8 = substr($buffer, 73, 7);
$field9 = substr($buffer, 80, 10);
$field10 = substr($buffer, 90, 8);
$field11 = substr($buffer, 98, 7);
$field12 = substr($buffer, 105, 2);
$field13 = substr($buffer, 107, 1);
$field14 = substr($buffer, 108, 30);
$field15 = substr($buffer, 138, 9);
$field16 = substr($buffer, 147, 18);
$field17 = substr($buffer, 165, 8);
$insert_sql = "INSERT INTO table1 ( field1, field2, field3, field4, field5, field6, field7, field8, field9, field10,
field11, field12, field13, field14, field15, field16, field17)
VALUES ('" . $field1 . "' , '" . $field2 . "', '" . $field3 . "', '" . $field4 .
"', '" . $field5 . "', '" . $field6 . "', '" . $field7 . "', '" . $field8 . "', '" . $field9 .
"', '" . $field10 . "', '" . $field11 . "', '" . $field12 . "', '" . $field13 . "', '" . $field14 .
"', '" . $field15 . "', '" . $field16 . "', '". $field17 ."');";
$result = mysql_query($insert_sql);
}
}
fclose ($fd);
}
?>
I would appreciate any insight on how I can optimize this script. I would also appreciate any impressions on my analysis of
the cause of this problem, or any other possible solutions.
Thanks!
C
