File uploading and processing

[bgadberry]

Hey there folks,

 

First time poster here, forgive me if I dont include everything I should as I don't know what all you might need to possibly help me. 

 

Long story short, I created a script that will read an old .dbf file and output a CSV file to a local directory and then upload that file to a server.  The server will then process the csv file and put all the information in a database.  Now, it seems to work fine when I dont have a lot of lines, but for some reason when I have a lot of lines in the CSV, it wants to just quit.  No errors, nothing.  Just quits.  Is there some sort of limit that I might be hitting?  Ive been able to in the past, read a CSV with serveral thousands of lines no problem and currently, my CSV has 11k lines.  Nothing Ive done is different from my previous scripts I dont think.  I can post code if it would help (which I think it will). 

 

$filename='Filename goes here'; //FILE NAME
$handle = @fopen("$filename", "r");
$countme=0;
$icount=0;
$ucount=0;
while (($data = @fgetcsv($handle, 0, ";", "\"")) !== FALSE) 
{
	$bigarray[$countme]=$data;
	$countme++;
}

@fclose($handle);

//while (($data = @fgetcsv($handle, 0, ";", "\"")) !== FALSE) 
foreach($bigarray as $data)
{
	$distno=$data[0];
	$custno=$data[1];
	$orderno=$data[2];
	$orderdate=$data[3];
	$iseqno=$data[4];
	$prodno=$data[5];
	$lineno=$data[6];
	$orgqty=$data[7];
	$qty=$data[8];
	$start=$data[9];
	$basepr=$data[10];
	$price=$data[11];
	$invpr=$data[12];
	$prodtype=$data[13];
	$recall=$data[14];
	$prdesc=$data[15];
	$sbsship=$data[16];
	$sbsdate=$data[17];
	$csr=$data[18];
	$reqdate=$data[19];
	$ponumb=$data[20];
	$userid=$data[21];
	$sbsstat=$data[22];
	$comment=$data[23];
	$qtship=$data[24];
	$shipby=$data[25];
	$status=$data[26];
	$pcolor=$data[27];
	$arstat=$data[28];
	$shipvia1=$data[29];
	$shipvia2=$data[30];
	$recallc=$data[31];

	$checkorder="SELECT orderno,iseqno FROM orderlist WHERE orderno='$orderno' AND iseqno='$iseqno'";
	$checked=mysql_query($checkorder);
	$isthere=mysql_num_rows($checked);
	if ($isthere>=1) //checks if there, if greater than 1, there.
	{
		$updateorder="UPDATE orderlist SET iseqno='$iseqno', prodno='$prodno', line='$lineno', orgqty='$orgqty', qty='$qty', start='$start', basepr='$basepr', price='$price', invpr='$invpr', prodtype='$prodtype', recall='$recall', prdesc='$prdesc', sbsship='$sbsship', sbsdate='$sbsdate', csr='$csr', reqdate='$reqdate', ponumb='$ponumb', sbsstat='$sbsstat', comment='$comment', qtship='$qtship', shipby='$shipby', status='$status', color='$color' WHERE orderno='$orderno' AND iseqno='$iseqno'";
		$updated=mysql_query($updateorder);
		$ucount++;	//Update Counter
	}
	else	//if not there, add it.
	{
		$insertorder="INSERT INTO orderlist (distno, custno, orderno, thedate, iseqno, prodno, line, orgqty, qty, start, basepr, price, invpr, prodtype, recall, prdesc, sbsship, sbsdate, csr, reqdate, ponumb, userid, sbsstat, comment, qtship, shipby, status, color, arstat, shipvia1, shipvia2, recallc) VALUES ('$distno', '$custno', '$orderno', '$orderdate', '$iseqno', '$prodno', '$lineno', '$orgqty', '$qty', '$start', '$basepr', '$price', '$invpr', '$prodtype', '$recall', '$prdesc', '$sbsship', '$sbsdate', '$csr', '$reqdate','$ponumb', '$userid', '$sbsstat', '$comment', '$qtship', '$shipby', '$status', '$pcolor', '$arstat', '$shipvia1', '$shipvia2', '$recallc')";
 		$inserted=mysql_query($insertorder);
 		$icount++;	//Insert Counter

		}
	$count++;
}

 

I realize that I could read directly from the CSV and perform the action on the row itself, and either way I try, using this bigarray way or with the action on the row itself, it still just likes to quit. 

 

Also, on a side note, perhaps it has something to do with it... can anyone run an infinite loop that continues past the 24-26 milion mark?  I ran an infinite loop to see how high it would count and it always stops anywhere between 24-26 million. Unclear as to why.  Anywho. 

 

Much appreciated for any and all help.

[rhodesa]

try reading up on this: http://us.php.net/set_time_limit

 

by default, i think scripts timeout around 30 seconds

[bgadberry]

Thank you for the response.  I should have posted that, hehe I knew I would miss something.

I actually have set the time out to 0 so it wouldnt time out in addition to setting the Max_Execution_Time to 0 as well to let it run indefinitely.

That was my first guess as to why it just quit.  Other ideas where it was outputting to much information as I had it echo the row it was on etc.  Took that out, still quits randomly.

Again, appreciate the response.

[rhodesa]

first, you need to add some error checking in there. try this script:

<?php
   set_time_limit(0);
   ini_set('display_errors',1);
   error_reporting(E_ALL ^ E_NOTICE);
   
   $filename='Filename goes here'; //FILE NAME
   $handle = fopen("$filename", "r");
   $icount=0;
   $ucount=0;
   while (($data = fgetcsv($handle, 0, ";", "\"")) !== FALSE)
   {
      //Escape the mysql data
      foreach($data as $n=>$value)
        $data[$n] = mysql_real_escape_string($value);

      $distno=$data[0];
      $custno=$data[1];
      $orderno=$data[2];
      $orderdate=$data[3];
      $iseqno=$data[4];
      $prodno=$data[5];
      $lineno=$data[6];
      $orgqty=$data[7];
      $qty=$data[8];
      $start=$data[9];
      $basepr=$data[10];
      $price=$data[11];
      $invpr=$data[12];
      $prodtype=$data[13];
      $recall=$data[14];
      $prdesc=$data[15];
      $sbsship=$data[16];
      $sbsdate=$data[17];
      $csr=$data[18];
      $reqdate=$data[19];
      $ponumb=$data[20];
      $userid=$data[21];
      $sbsstat=$data[22];
      $comment=$data[23];
      $qtship=$data[24];
      $shipby=$data[25];
      $status=$data[26];
      $pcolor=$data[27];
      $arstat=$data[28];
      $shipvia1=$data[29];
      $shipvia2=$data[30];
      $recallc=$data[31];
      
      $checkorder="SELECT orderno,iseqno FROM orderlist WHERE orderno='$orderno' AND iseqno='$iseqno'";
      $checked=mysql_query($checkorder) or die(mysql_error());
      $isthere=mysql_num_rows($checked);
      if ($isthere>=1) //checks if there, if greater than 1, there.
      {
         $updateorder="UPDATE orderlist SET iseqno='$iseqno', prodno='$prodno', line='$lineno', orgqty='$orgqty', qty='$qty', start='$start', basepr='$basepr', price='$price', invpr='$invpr', prodtype='$prodtype', recall='$recall', prdesc='$prdesc', sbsship='$sbsship', sbsdate='$sbsdate', csr='$csr', reqdate='$reqdate', ponumb='$ponumb', sbsstat='$sbsstat', comment='$comment', qtship='$qtship', shipby='$shipby', status='$status', color='$color' WHERE orderno='$orderno' AND iseqno='$iseqno'";
         $updated=mysql_query($updateorder) or die(mysql_error());
         $ucount++;   //Update Counter
      }
      else   //if not there, add it.
      {
         $insertorder="INSERT INTO orderlist (distno, custno, orderno, thedate, iseqno, prodno, line, orgqty, qty, start, basepr, price, invpr, prodtype, recall, prdesc, sbsship, sbsdate, csr, reqdate, ponumb, userid, sbsstat, comment, qtship, shipby, status, color, arstat, shipvia1, shipvia2, recallc) VALUES ('$distno', '$custno', '$orderno', '$orderdate', '$iseqno', '$prodno', '$lineno', '$orgqty', '$qty', '$start', '$basepr', '$price', '$invpr', '$prodtype', '$recall', '$prdesc', '$sbsship', '$sbsdate', '$csr', '$reqdate','$ponumb', '$userid', '$sbsstat', '$comment', '$qtship', '$shipby', '$status', '$pcolor', '$arstat', '$shipvia1', '$shipvia2', '$recallc')";
         $inserted=mysql_query($insertorder) or die(mysql_error());
         $icount++;   //Insert Counter
      }
   }
   fclose($handle);
   print "SUMMARY<br>Inserted: $icount<br>Updated: $ucount";
?>

[bgadberry]

Thank you again Aaron for the response.

 

Tried the code you posted, and it still stops.  This time it stops at line 825 of reading the CSV.  If it matters, the data being put into MySQL is secure.  It comes from a reliable source that wont/shouldn't have an SQL injection trick in it. And the data wont be coming from public, but instead, from approved sources.

 

No error comes up or anything.  It just stops.  Using Firefox and no errors appear in the error console, just in case.  No errors from the server.  It just stops.  Like it doesnt want to do it anymore lol.  I'm completely confused as to why.  Any other suggestions or tests you or any others can think of would be appreciated.

 

Thanks again!

 

-Brian.

[premiso]

I know when I had to do a database that 1as 10 MB's, I could rarely do more than 500 rows at a time, So what I did was read the file 500 lines, then delete that out of the file and put it into a new file "completed". Then just refreshed the script until it was all done.

 

Unfortunately browsers do timeout and usually anything over 500 rows of processing, since reading the file, parsing the data and inserting it into SQL takes time, it is better to do it in increments, or do it via command line.

 

The reason I needed to do it via php was I needed to re-create the table structure from the old db to the new one and the old one was in csv format.

 

Another thing you could do is instead of executing the queries, write the SQL to a file then just import that file via command line also.

[bgadberry]

Thank you premiso for the ideas and input.

I think I'm going to try that idea that you had about just writing the SQL file and using the command line to import it.  Ill report back with the results and hopefully mark this thread as Solved.   Thanks again! Ill continue to check this post if anyone else has anymore ideas.  I appreciate it.

 

-Brian.

[premiso]

Unfortunately I lost all my old scripts due to a harddrive crash or else I would post it here.

 

I do know the file had to be under 2mb's and I had to use the set_time_limit (setting it to 900) then about every 100 records output a "." to the screen or else the browser itself would time out.

 

You may also be able to do this using the php command line, that way you do not have to worry about the browser timing out, just the script.

 

Anyhow, good luck.

[rhodesa]

If it matters, the data being put into MySQL is secure.  It comes from a reliable source that wont/shouldn't have an SQL injection trick in it. And the data wont be coming from public, but instead, from approved sources.

i should have been more clear. you should still use mysql_real_escape_string() in case there are any single quotes in there.

 

does it stop on the same line every time?

[bgadberry]

It doesnt, no.  It will vary each time.  I cant seem to pinpoint why it does that either.  I got this working once  to get the initial batch in and it worked through all 200+MB's of a file.  And for some reason now, it doesnt want to work.  Im wondering if something changed on GoDaddy's end, but I think that is highly unlikely as they have said nothing has changed. Im thinking a dedicated server and the command line approach will work with a cron job running the SQL statements that Premiso recommended I create, every 5-10 minutes. 

 

Just some more information, the app is trying to update a database from an older foxpro db that creates DBF files.  So my process is dbf parsed through PHP creating a CSV locally (thumbdrive server, everything works ok here.) THe resulting CSV file is uploaded to the remote server, and parsed through PHP again, updating or inserting as needed.  As MySQL isn't available remotely through GoDaddy (security reasons I assume) I have to have the remote server process the CSV rather than just a straight DBF -> MySQL like I would prefer.  I can give more code if needed, like the (thumbdrive server process).

 

Modified to add thanks Aaron for edumacatin me on the mysql_real_escape_string(), thanks.  I didnt know thats what it did.  I always used addslashes and removeslashes, but it appears I didnt this time.  Hehe.  Much appreciated.