Streamline PHP Coding

[wanamakermedia]

This script was build to take a ZIP file that has been uploaded to our server, extract the TXT files within (which are always the same) and INSERT the contents of those TXT files into a MySQL DB that we have setup. Is there a way to stream line this coding and help it run faster? One of the TXT files in the ZIP has over 600,000 records and takes nearly two minutes to INSERT into the DB. Any help would be greatly appreciated!

 

<?
## DIRECTORY
$zipdirectory = "/home/ndn/www/data/getauto/";
$maindirectory = "/home/ndn/www/data/";	

## FILE NAME
if(!empty($_GET['altdate'])){
	$zipfile = $zipdirectory ."". $_GET['altdate'] .".zip";		
} else {
	$zipfile = $zipdirectory ."". date("ymd") .".zip";
}

## DOES FILE EXIST?
if(file_exists($zipfile)) {

	## EXTRACT ZIP FILE
	exec('unzip ' . escapeshellcmd($zipfile) . ' -d ' . $zipdirectory);

	## OPEN DIRECTORY
	$handler = opendir($zipdirectory);

	## GET ALL FILES IN DIRECTORY
	while ($file = readdir($handler)) {

		## REMOVE ZIP FILES AFTER 7 DAYS
		$oldzipfile = date("ymd", strtotime("-7 days")) .".zip";
		$entireoldzipfile = $zipdirectory ."". $oldzipfile;
		if(strtoupper(substr($file, -3, 3)) == "ZIP" && strtoupper($file) == strtoupper($oldzipfile)) { unlink($entireoldzipfile); }

		## IS FILE IN DIRECTORY A TXT FILE?
		if($file != '.' && $file != '..' && strtoupper(substr($file, -3, 3)) == "TXT") {

		## APPEND DIRECTORY TO FILE
		$fullfile = $zipdirectory ."". $file;

			## OPEN FILE
			$open = fopen($fullfile, 'r');

			## READ CONTENTS
			$contents = fread($open, filesize($fullfile));

			## CLOSE FILE
			fclose($open);

			## SEPARATE LINES
			$contents = explode("\n", $contents);

			## GET COLUMN COUNT FOR TEMP DB
			$columns = count(explode("\",\"", $contents[0]));

			## SET TEMP TABLE NAME
			$tmptable = strtolower(substr($file, 0, (strlen($file)-4)));

			## CONNECT TO DATABASE
			mysql_connect("localhost", "ndn_admin", "admin") or die(mysql_error());
			mysql_select_db("ndn_newdealernetwork") or die(mysql_error());

			## TRUNCATE EXISTING TABLE
			mysql_query("TRUNCATE TABLE ". $tmptable ."") or die(mysql_error());

			## CREATE INSERT STATEMENTS FROM FILE
			foreach($contents as $var) {
				if(!empty($var)) {
					$var = str_replace("\\", "\\\\", $var);
					$sql = "INSERT INTO `". $tmptable ."`  VALUES(". $var .")";
					mysql_query($sql) or die(mysql_error());
				}
			}

			## OPTIMIZE NEW TABLE
			mysql_query("OPTIMIZE TABLE ". $tmptable ."") or die(mysql_error());				

			## DELETE WORKING FILE
			unlink($fullfile);

			## UNSET TABLE NAME
			unset ($tableSQL);				

		}
	}

	## CLOSE DIRECTORY
	closedir($handler);

	## DELETE ERROR LOGS	
	unlink("error_log");

} else {

	## FILE DOES NOT EXIST
	echo "Zip File Missing!";

}
?>

[per1os]

Not with that many records. Even if you were to do a dump to a MySQL DB, that many records will take a while no matter what.

[Barand]

Have a look at MySql's LOAD DATA INFILE command. It's infinitely (almost) faster than inserting records.