wanamakermedia Posted March 16, 2007 Share Posted March 16, 2007 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!"; } ?> Link to comment https://forums.phpfreaks.com/topic/43033-streamline-php-coding/ Share on other sites More sharing options...
per1os Posted March 16, 2007 Share Posted March 16, 2007 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. Link to comment https://forums.phpfreaks.com/topic/43033-streamline-php-coding/#findComment-208994 Share on other sites More sharing options...
Barand Posted March 16, 2007 Share Posted March 16, 2007 Have a look at MySql's LOAD DATA INFILE command. It's infinitely (almost) faster than inserting records. Link to comment https://forums.phpfreaks.com/topic/43033-streamline-php-coding/#findComment-209005 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.