[SOLVED] Script never indicates it ends, loading 11,000 line csv

HawkCode · June 18, 2007

???

I have a script, PHP 4, that loads an 11,000 line csv file into a MySql data base. Running it loacally It completes and gives me the stats that I echo at the end.

Running on the server it does put in all the records, but never gives me the stats, there fore giving the illusion that it is in an endless loop.

If I cut the file down it ends as expected?

Any ideas?

Thanks

HawkCode · June 18, 2007

Update,

I added the line:

set_time_limit(3600);

It had no effect.

Thanks

Rich

cooldude832 · June 18, 2007

can i see the code?

GingerRobot · June 18, 2007

When you cut down the file, how much do you cut it down by? Unless your server/hosting is very good, it could simply be that it it taking far longer to execute the script.

cooldude832 · June 18, 2007

if thats the case it will error out after 30 seconds and tell you

GingerRobot · June 18, 2007

Not if the time limit wasn't at the default of 30 seconds - and not if the time limit has been changed.

HawkCode · June 18, 2007

Ok here's the code. It actuall takes less than 5 minutes to run on server.

When I cut down the source file I cut it down to about 25% of the original.

The data goes go in ok, when I ran it on my machine I did it in Zend.

function import_access () {
set_time_limit(3600);
global $Main;
$path_to_csv = "WebSiteAccess.csv";
$csv_delimiter = ","; 


$NewAddedcount = 0;
$Skipcount = 0;
$PrimaryChangeCount = 0;
$SecondaryChangeCount = 0;
$AccessLevelCount = 0;
$CheckSum = 0;
$LastPID = 0;
$PIDCount = 0;
$PreviousNew = 0;
echo "About to open:".$path_to_csv . "\n";
$handle = fopen ($path_to_csv,"r");
$Cnt = 0;
$CharsToRemove = array(" ", "-", "'", ".");
$LastLogin = "";
$LastLoginCount = 0;
while (($data = fgetcsv ($handle, 1000, $csv_delimiter)) !== FALSE) {

	$CheckSum = $NewAddedcount + $Skipcount + 
							$PrimaryChangeCount + $SecondaryChangeCount + $AccessLevelCount;

	$PeopleID = mysql_escape_string($data[0]); 				//tblPeople.[Person ID]

	//$login = ereg_replace("[^A-Za-z0-9]", "", $login);
	$ChapterCode = mysql_escape_string($data[1]);													//tblPeople.ChapterCode,
	$MembershipNumber = mysql_escape_string($data[2]);			//tblPeople.MemberNumber, 
	$Address1 = mysql_escape_string($data[3]);			//tblPeopleAddresses.Address1
	// For xCart PW use addslashes(text_crypt($data[4]));
	$Address2 = mysql_escape_string($data[4]);				//tblPeopleAddresses.Address2
	$City = mysql_escape_string($data[5]);						//tblPeopleAddresses.City
	$State = mysql_escape_string($data[6]);						//tblPeopleAddresses.State
	$Zip = mysql_escape_string($data[7]);							//tblPeopleAddresses.Zip
	$Country = mysql_escape_string($data[8]);
	$Title = mysql_escape_string($data[9]);						//tblPeople.Title
	$FName = mysql_escape_string($data[10]);					//tblPeople.[First Name]
	$LName = mysql_escape_string($data[11]);					//tblPeople.[Last Name]
	$NameSuffix = mysql_escape_string($data[12]);			//tblPeople.[Name Suffix]
	$EMail = mysql_escape_string($data[13]);					//tblPeople.Email
	$Phone = mysql_escape_string($data[14]);					//tblPeopleAddresses.Phone
	$Fax = mysql_escape_string($data[15]);						//tblPeopleAddresses.Fax
	$AddressType = mysql_escape_string($data[16]);		//tblAddressTypes.[Address Type Desc]
	$PrimaryAddr = mysql_escape_string($data[17]);		//tblPeopleAddresses.Primary
	$CardSent = mysql_escape_string($data[18]);				//tblPeople.CardsSent
	$MemStatus = mysql_escape_string($data[19]);			//tblMembership.Status
	$AccessLevel = mysql_escape_string($data[20]);
	if ($AccessLevel == null) {
		$AccessLevel = 0;
	}



	$OldLogin = $LName . $MembershipNumber;
	$OldLogin = StrToLower(ereg_replace("[^A-Za-z0-9]", "", $OldLogin));


//		if ($PeopleID == 31702) {
//			Echo "Stop\n";
//		}
	if ($PeopleID == $LastPID) {
		$PIDCount++;
	}else {
		$PIDCount = 1;
		$PreviousNew = 0;
	}


	if ($MemStatus == 1){
		$Password = $ChapterCode . $MembershipNumber;
	} else if ($PrimaryAddr ==1){
		$Password = strtolower($LName) . $Zip;
	}
	$Password = crypt($Password,"aspe");
	$sql = "SELECT * FROM accesslevel WHERE PeopleID = " . $PeopleID;
	$result = mysql_query($sql, $Main )or die(mysql_error());
	$row_result = mysql_fetch_assoc($result);
	$num_rows = mysql_num_rows($result);
	if ($num_rows == 0):{			// New person Add to accesslevel db
		$NewAddedcount++;
		$PreviousNew = 1;

		$InsertSQL = "INSERT INTO accesslevel(PeopleID, EMail, Password, AccessLevel,ASPEMemberID, 
									FName, LName, Title, NameSuffix, Phone, FirstLogin, OldLogin )
			VALUES(" . $PeopleID . ", '$EMail', '$Password', '$AccessLevel', '$MembershipNumber', '$FName', '$LName',
				'$Title', '$NameSuffix', '$Phone', 1, '$OldLogin')";
		mysql_query($InsertSQL)
				or die("<br>Error doing insert:".mysql_error());

		if ($PrimaryAddr == 1): {
			$updateSQL = sprintf("UPDATE accesslevel SET PAddress1='%s', PAddress2='%s',
											 PCity='%s', PState='%s', PZip='%s', PCountry='%s' 
											 WHERE PeopleID=%s",
												$Address1, $Address2, $City, $State, $Zip, $Country, $PeopleID);

		} 
		else: { 
			$updateSQL = sprintf("UPDATE accesslevel SET SAddress1='%s', SAddress2='%s',
											 SCity='%s', SState='%s', SZip='%s', SCountry='%s'
											 WHERE PeopleID=%s",
												$Address1, $Address2, $City, $State, $Zip, $Country, $PeopleID);

		}
		endif;
		$Result1 = mysql_query($updateSQL) or die(mysql_error());
	}	
	else: {

		//1st Check if Address Changed, if so update inc AccessLevel

		if (($PrimaryAddr == 1) and ($row_result['PAddress2'] !=  stripslashes($Address2)) and ($PIDCount < 3)): {
			$updateSQL = sprintf("UPDATE accesslevel SET PAddress1='%s', PAddress2='%s',
											 PCity='%s', PState='%s', PZip='%s', PCountry='%s', AccessLevel=%s 
											 WHERE PeopleID=%s",
												$Address1, $Address2, $City, $State, $Zip, $Country, $AccessLevel, $PeopleID);

			if ($PreviousNew == 0) {
				$PrimaryChangeCount++; 	
			 } 
			$Result1 = mysql_query($updateSQL) or die(mysql_error());
		} 
		elseif (  ($row_result['SAddress2'] != stripslashes($Address2)) and 
							($PrimaryAddr == 0)	 and ($PIDCount < 3)):  { 
			$updateSQL = sprintf("UPDATE accesslevel SET SAddress1='%s', SAddress2='%s',
											 SCity='%s', SState='%s', SZip='%s', SCountry='%s', AccessLevel=%s
											 WHERE PeopleID=%s",
												$Address1, $Address2, $City, $State, $Zip, $Country, $AccessLevel, $PeopleID);
			if ($PreviousNew == 0) {
				$SecondaryChangeCount++;													
			}
			$Result1 = mysql_query($updateSQL) or die(mysql_error());
		} //All info same, Now check accesslevel
		elseif (($AccessLevel != $row_result['AccessLevel'])  and ($PIDCount < 3)): {   
			$updateSQL = sprintf("UPDATE accesslevel SET  AccessLevel=%s
											 WHERE PeopleID=%s",$AccessLevel, $PeopleID);
			if ($PreviousNew == 0) {
				$AccessLevelCount++;
			}
			$Result1 = mysql_query($updateSQL) or die(mysql_error());


		}
		endif;

	}	
	endif;
	$LastPID = $PeopleID;
	if (		$CheckSum == ($NewAddedcount + $Skipcount + 
							$PrimaryChangeCount + $SecondaryChangeCount + 
							$AccessLevelCount)) {
		if ($PreviousNew == 0) {
			$Skipcount++;
		}
	}
} 


echo "Total New Added: " . $NewAddedcount ."<br>";
echo "        Skipped: " . $Skipcount ."<br>";
echo " Prim Addr Chng: " . $PrimaryChangeCount ."<br>";
echo " Sec Addre Chng: " . $SecondaryChangeCount ."<br>";
echo " AccessLvl Chng: " . $AccessLevelCount ."<br>";
echo "<h3>finished Inserted: $count</h3>"; //this is more usefull than it looks
fclose($handle);
};

HawkCode · June 19, 2007

More Info:

The script takes 5:40 to run. I determined this by doing a SQL Count(*) and knowing how many records would be inserted, 6,390 Records.

Any ideas????

Thanks

per1os · June 19, 2007

It could be a memory limit issue in PHP. It can only allocate a certain amount of memory.

Also a trick I learned is that sometimes the browser cuts out on you, so like every 100 or 200 records do something like this:

<?php

for ($i=1;$i<2000;$i++) {
    if ($i%200) {
        echo '.';
        sleep(1); // echo the dot and sleep the script for 1 second
    }
}
?>

That will send output to the browser making it not timeout and think it is still alive.

HawkCode · June 19, 2007

More: I cut the 11,000 line csv in half and it took 1:39 to run AND it displayed the stats at the end.

Maybe for this initial load I have to do it in pieces? Seems silly.

Rich

per1os · June 19, 2007

More: I cut the 11,000 line csv in half and it took 1:39 to run AND it displayed the stats at the end.

Maybe for this initial load I have to do it in pieces? Seems silly.

Rich

Shouldn't have to, just have to take the time to figure out whats holding it up. The question is, is it worth your time to figure out for this one time, or will this be a recurring event that it would be worth your time?

If this is only a "one time" deal than I would just go with cutting it in half and be done. If it will reoccur than take the time now to figure it out.

HawkCode · June 19, 2007

I think it's definatly worth it, just to know what the cause is for the future!

I Added:

	$TotalCOunt++;
	if ($TotalCOunt % 100) {
		echo '.';
      sleep(1); 
	}

It is not outputting anything to server, this just slows it way down...

HawkCode · June 19, 2007

I meant to the browser.... Sorry

HawkCode · June 19, 2007

I killed the script with that added code, after 20 minutes it had only inserted 600 records.

per1os · June 19, 2007

Bump the 100 up to 1000 that should work a bit faster. 100 is kinda intense, sorry about that.

HawkCode · June 19, 2007

I just realized, I made an error, the above code is wrong, I forgot the "== 0"

Duh!

HawkCode · June 19, 2007

OK, after changing to code to:

<?php
   $TotalCOunt++;
   if ($TotalCOunt % 1000 == 0) {
echo '.';
        sleep(1); 
  }

?>

It ran fast, but still no output to browser...

per1os · June 19, 2007

Did the script fully complete?

It may not actually "output" to the browser, the goal is for the script to run all the way through.

HawkCode · June 19, 2007

I know it completed because the total number of records inserted into the table was correct.

When I ran the Half sized CSV it output the stats to the browser fine.

per1os · June 19, 2007

So than it worked and the topic is solved ???

HawkCode · June 19, 2007

No Not solved. When the script ends it prints out stats, when I run it locally in Zend Debug it works fine. If I cut the csv down to half the size, 5,000 lines it works.

When run with the full csv 11,000 lines, the script never outputs the stats, BUT IT DOES COMPLETE, because I look at the total number of records in the MySql with Navicat and it has finished inserting the proper number of records.

So no it is definatly not solved.

Rich

per1os · June 19, 2007

So it seems the browser essentially stops itself at a certain point, probably around 3 minutes.

What might work is to try storing the stats in a session variable and than redirect to a page, once completed that displays the stats, or maybe try to write them to a file?

Another option, and I am not sure if this will work, but you could create the function to check the size of the file, if the file is bigger than x amount of bytes you split the file in half and run the first part, display the stats and than run the second part, that may keep the browser alive and kicking.

HawkCode · June 19, 2007

I thought of the session var idea, I will try that and let you know.

GingerRobot · June 19, 2007

You could also try flushing the content to the browser at certain points throughout the script?

HawkCode · June 19, 2007

The session var going to new page didn't work. Runs ok on my machine locally.

How do you flush the contents of the browser, I'll try any suggestion.

Sign In

[SOLVED] Script never indicates it ends, loading 11,000 line csv

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information