Page 1 of 1

How To Streamline Coding

Posted: Fri Mar 16, 2007 2:40 pm
by WanamakerStudios
pickle | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


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!

[syntax="php"]
<?
	## 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", "xxxUSERxxx", "xxxPASSxxx") 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!";
	
	}
?>

pickle | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Fri Mar 16, 2007 2:46 pm
by pickle
You're connecting to the database through each file. You should only connect once.

You could also use file_get_contents() which would eliminate the need for your fopen(), fread(), fclose(), and explode().

You're also making a new query for each line. Maybe if you consolidated 100 or so lines into 1 query it would reduce the over head.

Finally, I removed your database credentials from your code.

Posted: Fri Mar 16, 2007 2:50 pm
by WanamakerStudios
Thanks for removing the credentials ... I didn't even think about that!

And what would be the best way to consolidate multiple INSERT lines into one?

Posted: Fri Mar 16, 2007 2:54 pm
by pickle
Well multiple inserts work like so:

Code: Select all

INSERT
INTO
  myTable
  (field1,
   field2,
   field3)
VALUES
  (1.1,
   1.2,
   1.3),
  (2.1,
   2.2,
   3.3)
So for every line you loop through, rather than build an entire query, just build the value list & tack it onto the end of the query. Then, every 100 lines or so, strip the trailing ',', run the query, & reset the query.

Posted: Fri Mar 16, 2007 2:59 pm
by WanamakerStudios
I'll give that a shot ...

As for the file_get_contents() ... I get an out of memory error ...

Posted: Fri Mar 16, 2007 3:08 pm
by pickle
Weird - I'm not sure why that would be. The array you read in from file_get_contents() should be the same size as the one you get from explode(). You could try increasing the memory limit if you wanted, but I think the largest speed up will be from reducing the number of queries you send.

Posted: Fri Mar 16, 2007 3:09 pm
by WanamakerStudios
Im working on that now ... I'll let you know how it turns out! Thanks!