[SOLVED] fwrite over ftp+wrote code+now need help

jakebur01 · January 12, 2009

I wrote this page. It is it hacking out the calculations and writing it to a txt file on a unix server. It works perfect if I limit the selection to only a few rows. But, I need it to loop through 100,000 rows. It gives me a cgi or server not responding error even when I LIMIT 1000 rows.

What can I do to get my code below to process 100,000 rows? Also, should ($fp= fopen("ftp://user:pass@myip/u/ssc/price/$filename", "a") be inside or outside the while loop?

$filename=$_POST['filename'];
$path=$_POST['path'];
$tablename=$_POST['tablename'];
$header=$_POST['header'];
$list=$_POST['list'];
$dealer=$_POST['dealer'];
$distributor=$_POST['distributor'];
$central=$_POST['central'];
$part=$_POST['part'];
ini_set('max_execution_time', '999'); 


$db = mysql_connect('localhost', 'u', 'p') or die(mysql_error()); 
mysql_select_db('d') or die(mysql_error()); 
if($header==YES)
{
mysql_query("DELETE FROM {$tablename} LIMIT 1");
echo"Removing header....<br /><br />";
}

mysql_query("DELETE FROM {$tablename} WHERE $list='0'");

echo"Removing rows where the retail price is zero.<br /><br />Writing File...  Please wait..";

$result = mysql_query("SELECT * FROM {$tablename}");

while ($row = mysql_fetch_assoc($result)) {
$code="1038";
$partnumber=$row["$part"];
$targetChars=array('"', '$', ','); 
$partnumber=str_replace($targetChars, "", $partnumber); 

$tbp="TBP";
$stdpack="1";
$zero="0";
$listprice=$row["$list"];
$listprice=str_replace($targetChars, "", $listprice); 
$listprice = number_format($listprice, 2, '.', '');

    $cost=$row["$central"];
$cost=str_replace($targetChars, "", $cost); 
	$cost = number_format($cost, 2, '.', '');

	 $cost2=$row["$central"];
$cost2=str_replace($targetChars, "", $cost2);

$dealerprice=$row["$dealer"];
$dealerprice=str_replace($targetChars, "", $dealerprice);
	$dealerprice = number_format($dealerprice, 2, '.', '');


$division=".95";
$distributorprice=$cost2/$division;
	$distributorprice = number_format($distributorprice, 2, '.', '');
    
    //$row["$distributor"];
   


$outputstring = $code. "\t".$partnumber."\t".$tbp."\t".$tbp."\t".$stdpack."\t".$zero."\t".$zero."\t".$listprice."\t".$listprice."\t".$cost."\t".$dealerprice."\t".$dealerprice."\t".$dealerprice."\t".$dealerprice."\t".$dealerprice."\t".$distributorprice."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\n";

//print $outputstring;echo"<br />";

$fp= fopen("ftp://user:pass@myip/u/ssc/price/$filename", "a");



fwrite($fp, $outputstring, strlen($outputstring));


}

DarkSuperHero · January 12, 2009

you want that fopen ouside the loop. everytime the stuff inside the loop executes your opening the file again...so if your loop is 10,000rows...then your file is opening 10,000 times.....right ?

premiso · January 12, 2009

The following method will not work in all browsers. It also may take a while to run but here it is:

<?php
$filename=$_POST['filename'];
$path=$_POST['path'];
$tablename=$_POST['tablename'];
$header=$_POST['header'];
$list=$_POST['list'];
$dealer=$_POST['dealer'];
$distributor=$_POST['distributor'];
$central=$_POST['central'];
$part=$_POST['part'];
ini_set('max_execution_time', '999');

$db = mysql_connect('localhost', 'u', 'p') or die(mysql_error());
mysql_select_db('d') or die(mysql_error());
if($header==YES)
{
   mysql_query("DELETE FROM {$tablename} LIMIT 1");
   echo"Removing header....<br /><br />";
}

mysql_query("DELETE FROM {$tablename} WHERE $list='0'");

echo"Removing rows where the retail price is zero.<br /><br />Writing File...  Please wait..";

$fp= fopen("ftp://user:pass@myip/u/ssc/price/$filename", "a"); // moved out of loop to increase performance.

$result = mysql_query("SELECT * FROM {$tablename}");
$i=0;
while ($row = mysql_fetch_assoc($result)) {
   if (($i%100) == 0) {
	echo ".";
	ob_flush();
	flush();		
   }
   $i++;
   $code="1038";
   $partnumber=$row["$part"];
   $targetChars=array('"', '$', ',');
$partnumber=str_replace($targetChars, "", $partnumber);
   
   $tbp="TBP";
   $stdpack="1";
   $zero="0";
   $listprice=$row["$list"];
   $listprice=str_replace($targetChars, "", $listprice);
   $listprice = number_format($listprice, 2, '.', '');
   
    $cost=$row["$central"];
   $cost=str_replace($targetChars, "", $cost);
      $cost = number_format($cost, 2, '.', '');
      
       $cost2=$row["$central"];
   $cost2=str_replace($targetChars, "", $cost2);
   
   $dealerprice=$row["$dealer"];
   $dealerprice=str_replace($targetChars, "", $dealerprice);
      $dealerprice = number_format($dealerprice, 2, '.', '');
      
   
   $division=".95";
   $distributorprice=$cost2/$division;
      $distributorprice = number_format($distributorprice, 2, '.', '');
   
    //$row["$distributor"];
   
   
   
   $outputstring = $code. "\t".$partnumber."\t".$tbp."\t".$tbp."\t".$stdpack."\t".$zero."\t".$zero."\t".$listprice."\t".$listprice."\t".$cost."\t".$dealerprice."\t".$dealerprice."\t".$dealerprice."\t".$dealerprice."\t".$dealerprice."\t".$distributorprice."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\n";
   
   //print $outputstring;echo"<br />";  
   
   fwrite($fp, $outputstring, strlen($outputstring));
   
   
}
fclose($fp);
?>

You will also notice I moved to fopen out of the loop to increase performance.

jakebur01 · January 12, 2009

You are right.. I moved the fopen out of the loop. It wrote the 100,000 or so rows. It took a while.

I also need to write to the file 4 more times. Where the item does not have dash, where item has "tb" concatenated to it, tb with dash, tb without dash.

Should I do this on 4 different pages or do you think it will handle it all on this one page?

premiso · January 12, 2009

You are right.. I moved the fopen out of the loop. It wrote the 100,000 or so rows. It took a while.

I also need to write to the file 4 more times. Where the item does not have dash, where item has "tb" concatenated to it, tb with dash, tb without dash.

Should I do this on 4 different pages or do you think it will handle it all on this one page?

It can handle it, just create 3 more fopen handlers $fp1 $fp2 $fp3 outside the loop and it should be fine.

jakebur01 · January 12, 2009

For some reason the deletes below are not working right. I still have part of the header in the table and I still have rows where the list price = 0.

I cannot figure out what I am doing wrong. Are those incorrect queries? I do not have an identifier for the row when deleting the header... so I was trying to get it to just delete the first row in the table if the file has a header.

if($header==YES)
{
   mysql_query("DELETE FROM {$tablename} LIMIT 1");
   echo"Removing header....<br /><br />";
}

mysql_query("DELETE FROM {$tablename} WHERE $list='0'");

jakebur01 · January 12, 2009

It does ok, it just takes 1 thousand years to process. And sometimes bombs out at the end.

Would it be faster or better to write the file on the local drive then ftp the file to the unix box at the end of the script?


$filename=$_POST['filename'];
$path=$_POST['path'];
$tablename=$_POST['tablename'];
$header=$_POST['header'];
$list=$_POST['list'];
$dealer=$_POST['dealer'];
$distributor=$_POST['distributor'];
$central=$_POST['central'];
$part=$_POST['part'];
ini_set('max_execution_time', '999');

$db = mysql_connect('xxxxxxxxx') or die(mysql_error()); 
mysql_select_db('mydatabase') or die(mysql_error()); 
if($header==YES)
{
   mysql_query("DELETE FROM {$tablename} LIMIT 1");
   echo"Removing header....<br /><br />";
}

mysql_query("DELETE FROM {$tablename} WHERE $list='$0.00'");

echo"Removing rows where the retail price is zero.<br /><br />Writing File...  Please wait..";

$fp= fopen("ftp://myconnectinfo/u/ssc/price/$filename", "a"); // moved out of loop to increase performance.

$result = mysql_query("SELECT * FROM {$tablename}");
$i=0;
while ($row = mysql_fetch_assoc($result)) {
   if (($i%100) == 0) {
	echo ".";
	ob_flush();
	flush();		
   }
   $i++;
   $code="1038";
   $partnumber=$row["$part"];
    $targetChars=array('"', '$', ',');

$partnumber=str_replace($targetChars, "", $partnumber);
   $targetChars1=array('-');
$partnumber1=str_replace($targetChars1, "", $partnumber);
   
   $tbp="TBP";
   $stdpack="1";
   $zero="0";
   $listprice=$row["$list"];
   $listprice=str_replace($targetChars, "", $listprice);
   $listprice = number_format($listprice, 2, '.', '');
   
    $cost=$row["$central"];
   $cost=str_replace($targetChars, "", $cost);
      $cost = number_format($cost, 2, '.', '');
      
       $cost2=$row["$central"];
   $cost2=str_replace($targetChars, "", $cost2);
   
   $dealerprice=$row["$dealer"];
   $dealerprice=str_replace($targetChars, "", $dealerprice);
      $dealerprice = number_format($dealerprice, 2, '.', '');
      
   
   $division=".95";
   $distributorprice=$cost2/$division;
      $distributorprice = number_format($distributorprice, 2, '.', '');
   
    //$row["$distributor"];
   
   
   
   $outputstring = $code. "\t".$partnumber."\t".$tbp."\t".$tbp."\t".$stdpack."\t".$zero."\t".$zero."\t".$listprice."\t".$listprice."\t".$cost."\t".$dealerprice."\t".$dealerprice."\t".$dealerprice."\t".$dealerprice."\t".$dealerprice."\t".$distributorprice."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\n";
   
   //print $outputstring;echo"<br />";  
   
   fwrite($fp, $outputstring, strlen($outputstring));
   
   
}
fclose($fp);

$fp1= fopen("ftp://myconnectinfo/u/ssc/price/$filename", "a"); // moved out of loop to increase performance.

$result = mysql_query("SELECT * FROM {$tablename}");
$i=0;
while ($row = mysql_fetch_assoc($result)) {
   if (($i%100) == 0) {
	echo ".";
	ob_flush();
	flush();		
   }
   $i++;
   $code="1038";
   $partnumber=$row["$part"];
   $targetChars=array('"', '$', ',');
$partnumber=str_replace($targetChars, "", $partnumber);

   $targetChars1=array('-');
$partnumber1=str_replace($targetChars1, "", $partnumber);
   
   $tbp="TBP";
   $stdpack="1";
   $zero="0";
   $listprice=$row["$list"];
   $listprice=str_replace($targetChars, "", $listprice);
   $listprice = number_format($listprice, 2, '.', '');
   
    $cost=$row["$central"];
   $cost=str_replace($targetChars, "", $cost);
      $cost = number_format($cost, 2, '.', '');
      
       $cost2=$row["$central"];
   $cost2=str_replace($targetChars, "", $cost2);
   
   $dealerprice=$row["$dealer"];
   $dealerprice=str_replace($targetChars, "", $dealerprice);
      $dealerprice = number_format($dealerprice, 2, '.', '');
      
   
   $division=".95";
   $distributorprice=$cost2/$division;
      $distributorprice = number_format($distributorprice, 2, '.', '');
   
    //$row["$distributor"];
   
   
   
   $outputstring1 = $code. "\t".$partnumber1."\t".$tbp."\t".$tbp."\t".$stdpack."\t".$zero."\t".$zero."\t".$listprice."\t".$listprice."\t".$cost."\t".$dealerprice."\t".$dealerprice."\t".$dealerprice."\t".$dealerprice."\t".$dealerprice."\t".$distributorprice."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\n";
   
   //print $outputstring;echo"<br />";  
   
   fwrite($fp1, $outputstring1, strlen($outputstring1));
   
   
}
fclose($fp1);


$fp2= fopen("ftp://myconnectinfo/u/ssc/price/$filename", "a"); // moved out of loop to increase performance.

$result = mysql_query("SELECT * FROM {$tablename}");
$i=0;
while ($row = mysql_fetch_assoc($result)) {
   if (($i%100) == 0) {
	echo ".";
	ob_flush();
	flush();		
   }
   $i++;
   $code="1038";
   $partnumber=$row["$part"];
   $targetChars=array('"', '$', ',');
$partnumber=str_replace($targetChars, "", $partnumber);
   $partnumber2=TB."$partnumber";
   $tbp="TBP";
   $stdpack="1";
   $zero="0";
   $listprice=$row["$list"];
   $listprice=str_replace($targetChars, "", $listprice);
   $listprice = number_format($listprice, 2, '.', '');
   
    $cost=$row["$central"];
   $cost=str_replace($targetChars, "", $cost);
      $cost = number_format($cost, 2, '.', '');
      
       $cost2=$row["$central"];
   $cost2=str_replace($targetChars, "", $cost2);
   
   $dealerprice=$row["$dealer"];
   $dealerprice=str_replace($targetChars, "", $dealerprice);
      $dealerprice = number_format($dealerprice, 2, '.', '');
      
   
   $division=".95";
   $distributorprice=$cost2/$division;
      $distributorprice = number_format($distributorprice, 2, '.', '');
   
    //$row["$distributor"];
   
   
   
   $outputstring2 = $code. "\t".$partnumber2."\t".$tbp."\t".$tbp."\t".$stdpack."\t".$zero."\t".$zero."\t".$listprice."\t".$listprice."\t".$cost."\t".$dealerprice."\t".$dealerprice."\t".$dealerprice."\t".$dealerprice."\t".$dealerprice."\t".$distributorprice."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\n";
   
   //print $outputstring;echo"<br />";  
   
   fwrite($fp2, $outputstring2, strlen($outputstring2));
   
   
}
fclose($fp2);




$fp3= fopen("ftp://myconnectinfo/u/ssc/price/$filename", "a"); // moved out of loop to increase performance.

$result = mysql_query("SELECT * FROM {$tablename}");
$i=0;
while ($row = mysql_fetch_assoc($result)) {
   if (($i%100) == 0) {
	echo ".";
	ob_flush();
	flush();		
   }
   $i++;
   $code="1038";
   $partnumber=$row["$part"];
   $targetChars=array('"', '$', ',');
$partnumber=str_replace($targetChars, "", $partnumber);
   $partnumber2=TB."$partnumber";
   
      $targetChars3=array('"', '$', ',','-');

   $partnumber3=TB."$partnumber";
   $partnumber3=str_replace($targetChars3, "", $partnumber3);
   
   
   $tbp="TBP";
   $stdpack="1";
   $zero="0";
   $listprice=$row["$list"];
   $listprice=str_replace($targetChars, "", $listprice);
   $listprice = number_format($listprice, 2, '.', '');
   
    $cost=$row["$central"];
   $cost=str_replace($targetChars, "", $cost);
      $cost = number_format($cost, 2, '.', '');
      
       $cost2=$row["$central"];
   $cost2=str_replace($targetChars, "", $cost2);
   
   $dealerprice=$row["$dealer"];
   $dealerprice=str_replace($targetChars, "", $dealerprice);
      $dealerprice = number_format($dealerprice, 2, '.', '');
      
   
   $division=".95";
   $distributorprice=$cost2/$division;
      $distributorprice = number_format($distributorprice, 2, '.', '');
   
    //$row["$distributor"];
   
   
   
   $outputstring3 = $code. "\t".$partnumber3."\t".$tbp."\t".$tbp."\t".$stdpack."\t".$zero."\t".$zero."\t".$listprice."\t".$listprice."\t".$cost."\t".$dealerprice."\t".$dealerprice."\t".$dealerprice."\t".$dealerprice."\t".$dealerprice."\t".$distributorprice."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\n";
   
   //print $outputstring;echo"<br />";  
   
   fwrite($fp3, $outputstring3, strlen($outputstring3));
   
   
}
fclose($fp3);


echo"<br /><br /><b>$filename</b> was written on UNIX my ip<br /><br />The path to the MTD facts price update file is /u/ssc/price/$filename";

jakebur01 · January 12, 2009

bump

jakebur01 · January 12, 2009

Would it be faster or better to write the file on the local drive then ftp the file to the unix box at the end of the script?

Anyone ???

aschk · January 12, 2009

I'd like you to explain more thoroughly what it is you're attemping to achieve.

Are you doing the following:

1) Reading a database table (for 100,000 rows).

2) Inserting each row into a file?

The one thing that strikes me as rather odd is that you're writing over ftp. Which is rather silly.

Write to a temporary file locally and then once you've finished that push it via ftp. Otherwise you're writing each line of the file across the network one at a time...

<? // The following is psuedo-code:

$tmpFile = 'tmp.txt';
$fh = fopen($tmpFile, "a");
$sql = "SELECT <rows from db>";

foreach($rows as $row){
fputcsv($fh, $row, '\t') // this is a handy PHP function!
}

fclose($tmpFile);

$ftp_connect = ftp_connect("somedomain.com");
ftp_login($ftp_connect, "username", "password");
ftp_fput($ftp_connect, "remotefilename.txt", $fh = fopen($tmpFile));
fclose($fh);
ftp_close($ftp_connect);

?>

One more thing that strikes me as odd is that you seem have to the currency indicator inside the database field value, i.e. "$1.00". Normalize your data PLEASE! That should be 2 (or 3) columns for currency_symbol, currency_name, currency_value.

jakebur01 · January 12, 2009

Man!!! What a difference that made! It wrote the file in about 3 seconds.

Thank you.

Sign In

[SOLVED] fwrite over ftp+wrote code+now need help

Recommended Posts

jakebur01

Link to comment

Share on other sites

DarkSuperHero

Link to comment

Share on other sites

premiso

Link to comment

Share on other sites

jakebur01

Link to comment

Share on other sites

premiso

Link to comment

Share on other sites

jakebur01

Link to comment

Share on other sites

jakebur01

Link to comment

Share on other sites

jakebur01

Link to comment

Share on other sites

jakebur01

Link to comment

Share on other sites

aschk

Link to comment

Share on other sites

jakebur01

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information