Page 1 of 1

Loop of queries fails

Posted: Tue Dec 18, 2007 2:22 am
by allanr
feyd | 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]


Hello everyone.  I am loading a text file into a MySQL table on my local machine.  I am using the code below.  The process stops at approx 70,000 records and does not finish.  But, there is no error - no "something went wrong" or error in the mysql log.  If I run the file for 50,000 records then run the file again, the table will have the full 100,000 and finish properly both times.  If I run the file once using the loop to run 2 rounds of 50,000 the table will only have 65,000 records.  Is it possible there is a limit on the number of queries that can run in one session?  How else can I run the full file of 360,000 records?

Thank you for your help!

Code: Select all

<?php
$files_root = "path";

$file = $files_root."sample.csv";
$sep = "\",\"";

 		$fp = fopen($file,"r");
		$chunklen = 0;
 		$chunk[0] = fgets($fp,2048);
            $headers = explode($sep, $chunk[0]);
            $headers = str_replace("\"", "", $headers);
            $headers = str_replace("'", "", $headers);
    $numheaders = count($headers);
    echo $numheaders;
    echo "<br />";
   // Check Valid  FILE
    if ($headers[0] != "text") {
        return false;
        exit;
    }
		$chunklen=strlen($chunk[0]);
    echo $chunklen."<br />";

 		echo "Now data <br />";

$host = "localhost";
$user = "motta";
$pass = "xxxxxx";
$dbname = "mottst";
				
				$i = 0;
		    while ($i < 2) {
				$j = 0;
					while ($j < 50000) {
		
$conn = mysql_connect($host,$user,$pass);
// echo $conn;
mysql_select_db($dbname,$conn);
 		$chunk[0] = fgets($fp,2048);
            $fields = explode($sep, $chunk[0]);
            $fields = str_replace("\"", "", $fields);
            $fields = str_replace("'", "", $fields);

	  		$query_start = "ref_id,vin,vehicle_code,make,model, body_style,model_year,v_features,price, max_mileage,ext_color,long_description,is_certified, transmission,doors,engine_size, comp_postcode,dealer_username";
    		$query_val = "'{$fields[0]}','{$fields[1]}','{$fields[2]}', '{$fields[3]}','{$fields[4]}','{$fields[5]}', '{$fields[6]}','{$fields[7]}','{$fields[8]}', '{$fields[9]}','{$fields[10]}','{$fields[11]}', '{$fields[12]}','{$fields[13]}','{$fields[16]}', '{$fields[18]}','{$fields[20]}','{$fields[23]}'";


				$query = "insert into temp_tbl(" . $query_start . ") values (" . $query_val . ")";
				if (mysql_query($query, $conn)) {
					} else {
					echo "something went wrong at ".$j;
					}				

			$j++;
} //end of inner while $j


mysql_close($conn);
			
			$i++;
			
} // end of outer while

 	  fclose($fp);


?>

feyd | 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]

Posted: Tue Dec 18, 2007 9:01 am
by John Cartwright
There are a couple settings that should probably be set when doing large file operations, although not necessary sounds like it may help you.

Code: Select all

error_reporting(E_ALL); //always include this
ini_set('memory_limit', '128mb');
set_time_limit(0);
ignore_user_abort();
then on each loop let it sleep for a couple milliseconds to catch it's processors breathe

Code: Select all

usleep(100000); // one tenth of a second I believe

Issue Solved

Posted: Tue Dec 18, 2007 4:44 pm
by allanr
Thank you. The set_time_limit(0) worked. Sorry about missing the tags - I will read how to post. Thank you again.