Importing text file into MySQL via PHP script

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

Well if you cant use LOAD DATA IN FILE I would read the text file, and write the sql to another, eg

sql.txt:


INSERT INTO table1 ( field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field11, field12, field13, field14, field15, field16, field17) VALUES ('value1', 'value2')..

And

exec("mysql -uusername -ppassword < sql.txt");

:slight_smile: HTH

Dangermouse, thanks for the input. I’ve got a question about this though.

In your exec statement, how do you tell MySQL which database and table to use for the INSERT statements?

on the exec command line, you can add parameters for the hostname and database name

e.g.


exec("mysql -uusername -ppassword -h<hotsname> -D<databasename> < sql.txt");

or in the sql.txt file you can add


CONNECT <databasename> <hostname>

The inserts are being made into the table specified in the INSERT command, in this example, the tables called table1

Hope this helps

Thanks for your input!

I’ve been poking around in some of the tools my ISP offers. phpMyAdmin is one of them and it offers an import tool that (based on the options it asks for) seems to be using a LOAD DATA INFILE command. So maybe this command is available on my ISPs server. I’ve changed my exec() command to read:

exec(“mysql -u<user> -p<pwd> -hlocalhost -D<database> < LOAD DATA INFILE ‘/<path>/test.txt’ INTO TABLE ‘test’ FIELDS TERMINATED BY ‘’ LINES TERMINATED BY ‘\r’”);

However, this doesn’t work. Additionally, I can’t find a way to get the error returned so I can at least get some direction on troubleshooting. Is there a way to return the error generated so I can better troubleshoot this?

Thanks again all!!

Im confused, does test.txt contain SQL, like i explained earlier, or does it contain the data you want to load?

If the first, then use exec(“mysql -u<user> -p<pwd> -hlocalhost -D<database> < /<path>/test.txt”);

if the latter use mysql_connect() etc and mysql_query(“LOAD DATA INFILE …”)

The text file only contains the data. No SQL statements. My origional script reads the datafile and builds the INSERT statements and executes them. When using the mysql_query statement you mentioned before I get an error Resource ID#2, but I have not been able to find anything specific about that.

craig-o

Sounds like you’re getting a bit confused.

The Solution

  1. Read the test.txt file (as you currently do)
  2. Create the SQL INSERT statements (as you currently do)
  3. Write these statements to a file - test.sql, as Dangermouse suggests
  4. when you have completed reading test.txt and writing test.sql, run the command
    php exec('mysql -u<user> -p<pwd> -hlocalhost -D<database> < /path/to/test.sql');(

Hope this makes things clearer

Ok, I get it. As I said in my first post, I’m a bit new to this.

Is there a way to return any errors generated by exec(). The PHP manual doesn’t suggest any way, but I’m hopeful. Presently, I can create the file, but it’s not being read into the database at all.

Thanks for all your help!

Found my error with the exec() command, but it would still be nice to know what errors this generated if there were any. One last question for now. Why is this more efficient? I know that’s kind of wide open, but I’d like to know for my own knowledge. It seems though that we are doing the same thing just in a diffferent way.

Thanks everyone for your help!

Im guessing its quicker for mysql to parse it itself than another process to talk to mysql.

If you want any errors, shell_exec() returns the full output from mysql as a string where as exec() is only the last line.

If you use mysql_query() then you can use mysql_error() :slight_smile: