jakebur01

That worked great. But, I think i've singled out the code below as being the problem. The script runs in a blink when the code below is commented out. mysql_query("UPDATE {$tablename} AS a, {$tablename} AS b SET a.$list =b.$list, a.$dealer=b.$dealer, a.$distributor=b.$distributor, a.$sort=b.$sort, a.$stdpack=b.$stdpack WHERE LEFT(b.$part, CHAR_LENGTH(b.$part)-2)=a.$part AND RIGHT(b.$part, 2) IN ('-1', '-2', '-3', '-0')") or die(mysql_error());

Like this? mysql_query("DELETE FROM {$tablename} WHERE $dealer IN('', '$-', '0', '0.0', '0.00', '$ 0', '$ -', '$0.0', '$0.00', '-', ' ', '$') or $distributor IN('', '$-', '0', '0.0', '0.00', '$ 0', '$ -', '$0.0', '$0.00', '-', ' ', '$') or $list IN('', '$-', '0', '0.0', '0.00', '$ 0', '$ -', '$0.0', '$0.00', '-', ' ', '$')");

This bit of code is bogging my server down like crazy. Is there any way I could manage this so it will sort things out better? There are a few thousand rows in this table. mysql_query("UPDATE {$tablename} AS a, {$tablename} AS b SET a.$list =b.$list, a.$dealer=b.$dealer, a.$distributor=b.$distributor, a.$sort=b.$sort, a.$stdpack=b.$stdpack WHERE LEFT(b.$part, CHAR_LENGTH(b.$part)-2)=a.$part AND RIGHT(b.$part, 2) IN ('-1', '-2', '-3', '-0')"); mysql_query("DELETE FROM {$tablename} WHERE $dealer IN('', '$-', '0', '0.0', '0.00', '$ 0', '$ -', '$0.0', '$0.00', '-', ' ', '$')"); mysql_query("DELETE FROM {$tablename} WHERE $distributor IN('', '$-', '0', '0.0', '0.00', '$ 0', '$ -', '$0.0', '$0.00', '-', ' ', '$')"); mysql_query("DELETE FROM {$tablename} WHERE $list IN('', '$-', '0', '0.0', '0.00', '$ 0', '$ -', '$0.0', '$0.00', '-', ' ', '$')");

That fixed it. I am having trouble in the script as a whole bogging down the server so I had it limited to one row. Thank you all for your help.

How can I correctly use this query selecting all the rows in the $sort column where the value is 700 or above? $result = mysql_query("SELECT * FROM {$tablename} WHERE $sort > '699' LIMIT 1");

I think I found my problem. I changed $result = mysql_query("SELECT * FROM {$tablename} WHERE $sort > '699' LIMIT 1"); to $result = mysql_query("SELECT * FROM {$tablename} LIMIT 1"); And it processed that row no problem........ The variable $sort is a column... How can I correctly use this query selecting all the rows in the $sort column where the value is 700 or above?

Am I setting too many variables?

If you encoded it when you originally stored it, you cannot match a decoded password against an encoded password. $password_decoded = mysql_real_escape_string(base64_decode($_POST['password'])); change this to base64_encode and see if it works.

Where is the code where you are storing the password in the database and where you are making verified equal yes? If I see how you are storing it I may be able to help.

Thank you. I will practice more indenting. I tried commenting out the multiple queries and loops within loops. But, the progress bar in my browser gets stuck and the server is still timing out. I am posting columns to this page that I selected on the previous page. I am building a text file using the columns that were selected on the previous page. I also tried using LIMIT 1 on my query to see if I could get it to process just one row. But, that did not help. <?php $filename=$_POST['filename']; $path=$_POST['path']; $tablename=$_POST['tablename']; $header=$_POST['header']; $list=$_POST['list']; $dealer=$_POST['dealer']; $distributor=$_POST['distributor']; $sort=$_POST['sort']; $part=$_POST['part']; $stdpack=$_POST['stdpack']; ini_set('max_execution_time', '999'); $db = mysql_connect('localhost', 'user', 'password') or die(mysql_error()); mysql_select_db('database') or die(mysql_error()); if($header==YES) { mysql_query("DELETE FROM {$tablename} LIMIT 1"); echo"Removing header....<br /><br />"; } echo "filename: $filename<br />"; echo "path: $path<br />"; echo "tablename: $tablename<br />"; echo "header: $header<br />"; echo "list: $list<br />"; echo "dealer: $dealer<br />"; echo "distributor: $distributor<br />"; echo "sort: $sort<br />"; echo "part: $part<br />"; echo "stdpack: $stdpack<br />"; /* mysql_query("UPDATE {$tablename} AS a, {$tablename} AS b SET a.$list =b.$list, a.$dealer=b.$dealer, a.$distributor=b.$distributor, a.$sort=b.$sort, a.$stdpack=b.$stdpack WHERE LEFT(b.$part, CHAR_LENGTH(b.$part)-2)=a.$part AND RIGHT(b.$part, 2) IN ('-1', '-2', '-3', '-0')"); mysql_query("DELETE FROM {$tablename} WHERE $dealer IN('', '$-', '0', '0.0', '0.00', '$ 0', '$ -', '$0.0', '$0.00', '-', ' ', '$')"); mysql_query("DELETE FROM {$tablename} WHERE $distributor IN('', '$-', '0', '0.0', '0.00', '$ 0', '$ -', '$0.0', '$0.00', '-', ' ', '$')"); mysql_query("DELETE FROM {$tablename} WHERE $list IN('', '$-', '0', '0.0', '0.00', '$ 0', '$ -', '$0.0', '$0.00', '-', ' ', '$')"); echo"Removing rows where the retail price is zero.<br /><br />Writing File... Please wait.."; */ $source_file = "C:/Inetpub/Websites/mysite.com/price/$filename"; $fp= fopen("$source_file", "w"); $result = mysql_query("SELECT * FROM {$tablename} WHERE $sort > '699' LIMIT 1"); while ($row = mysql_fetch_assoc($result)) { $partnumber=$row["$part"]; $targetChars=array('"', '$', ',', '*'); $stdpack=$row["$stdpack"]; $stdpack=str_replace($targetChars, "", $stdpack); $dealercheck=$row["$dealer"]; $dealercheck=str_replace($targetChars, "", $dealercheck); $dealercheck = number_format($dealercheck, 2, '.', ''); $distributorcheck=$row["$distributor"]; $distributorcheck=str_replace($targetChars, "", $distributorcheck); $distributorcheck = number_format($distributorcheck, 2, '.', ''); $listprice=$row["$list"]; $listprice=str_replace($targetChars, "", $listprice); $listprice = number_format($listprice, 2, '.', ''); $distributor=$row["$distributor"]; $distributor=str_replace($targetChars, "", $distributor); $cost=$distributor*.95; $cost = number_format($cost, 2, '.', ''); $level1=$row["$dealer"]; $level1=str_replace($targetChars, "", $level1); $level1 = number_format($level1, 2, '.', ''); $level2=$row["$dealer"]; $level2=str_replace($targetChars, "", $level2); $level2=$level2*.96; $level2 = number_format($level2, 2, '.', ''); $level3=$row["$dealer"]; $level3=str_replace($targetChars, "", $level3); $level3=$level3*.92; $level3 = number_format($level3, 2, '.', ''); $level4=$row["$dealer"]; $level4=str_replace($targetChars, "", $level4); $level4=$level4*.9; $level4 = number_format($level4, 2, '.', ''); $level5=$row["$dealer"]; $level5=str_replace($targetChars, "", $level5); $level5=$level5*.88; $level5 = number_format($level5, 2, '.', ''); $level6=$row["$dealer"]; $level6=str_replace($targetChars, "", $level6); $level6=$level6*.85; $level6 = number_format($level6, 2, '.', ''); $c1="1075"; $c2="$partnumber"; $c3=""; $c4=""; $c5="$stdpack"; $c6=""; $c7=""; $c8="$listprice"; $c9="$listprice"; $c10="$cost"; $c11="$level1"; $c12="$level2";$c13="$level3"; $c14="$level4"; $c15="$level5"; $c16="$level6"; $c17="0"; $c18="0"; $c19="0"; $c20="0"; $c21="0"; $c22="0"; $c23="0"; $c24="0"; $c25="0"; $c26=""; if($distributorcheck==$dealercheck) { $c11="$dealercheck"; $c12="$dealercheck"; $c13="$dealercheck"; $c14="$dealercheck"; $c15="$dealercheck"; $c16="$dealercheck"; } /* else { $result2 = mysql_query("SELECT * FROM smith_prog_item WHERE `item` = $partnumber LIMIT 1"); while ($row2 = mysql_fetch_assoc($result2)) { if($row2["type"]=="NGK") { $c17="100"; $c18="$level6";} elseif ($row2["type"]=="OTHER") { $c11="$level6"; $c12="$level6"; $c13="$level6"; $c14="$level6"; $c15="$level6"; $c16="$level6"; } } } */ $outputstring = $c1. "\t".$c2."\t".$c3."\t".$c4."\t".$c5."\t".$c6."\t".$c7."\t".$c8."\t".$c9."\t".$c10."\t".$c11."\t".$c12."\t".$c13."\t".$c14."\t".$c15."\t".$c16."\t".$c17."\t".$c18."\t".$c19."\t".$c20."\t".$c21."\t".$c22."\t".$c23."\t".$c24."\t".$c25."\t".$c26."\t".$c27."\t".$c28."\t".$c29."\n"; //print $outputstring;echo"<br />"; fwrite($fp, $outputstring, strlen($outputstring)); } fclose($fp); echo"<br /><br /><a href=\"http://mysite.com/price/$filename\">Click here to preview file</a>"; ?>

Premiso made a comment one time about my indentations. How can I learn to become better at indenting. I want to learn to be a better coder.

Is it ok to have this much coding on one page?

bump. . .

How can I make my script work without getting this: CGI Timeout The specified CGI application exceeded the allowed time for processing. The server has deleted the process. I tried adding a little for loop and limiting it to take a break every 250 rows but it still does not help. mysql_query("UPDATE {$tablename} AS a, {$tablename} AS b SET a.$list =b.$list, a.$dealer=b.$dealer, a.$distributor=b.$distributor, a.$sort=b.$sort, a.$stdpack=b.$stdpack WHERE LEFT(b.$part, CHAR_LENGTH(b.$part)-2)=a.$part AND RIGHT(b.$part, 2) IN ('-1', '-2', '-3', '-0')"); mysql_query("DELETE FROM {$tablename} WHERE $dealer IN('', '$-', '0', '0.0', '0.00', '$ 0', '$ -', '$0.0', '$0.00', '-', ' ', '$')"); mysql_query("DELETE FROM {$tablename} WHERE $distributor IN('', '$-', '0', '0.0', '0.00', '$ 0', '$ -', '$0.0', '$0.00', '-', ' ', '$')"); mysql_query("DELETE FROM {$tablename} WHERE $list IN('', '$-', '0', '0.0', '0.00', '$ 0', '$ -', '$0.0', '$0.00', '-', ' ', '$')"); echo"Removing rows where the retail price is zero.<br /><br />Writing File... Please wait.."; $source_file = "C:/Inetpub/Websites/mysite.com/price/$filename"; $fp= fopen("$source_file", "w"); // get count of how many rows we'll be dealing with $total_rows = mysql_num_rows(mysql_query("SELECT * FROM {$tablename} AND $sort > '699'")); // how many rows per loop execution - you can up this too, probably like a 1000 is a good number $rows_per_loop = 250; // Get the most amount of loops possible. $total_loops = ceil($total_rows/$rows_per_loop); // just make it look nice: echo 'Running rows: <br />'; // run the loop, while loop counter is less than the total number of loops: for($i=0; $i<$total_loops; $i++) { // get the numbers for the limit, // start is just the current loop number multiplied by the rows per loop // and end is loop counter + 1, multiplied by the rows per loop $limit_start = $rows_per_loop*$i; $limit_end = $rows_per_loop*($i+1); $result = mysql_query("SELECT * FROM {$tablename} AND $sort > '699'"); $i=0; while ($row = mysql_fetch_assoc($result)) { if (($i%100) == 0) { echo "."; ob_flush(); flush(); } $i++; $partnumber=$row["$part"]; $targetChars=array('"', '$', ',', '*'); $stdpack=$row["$stdpack"]; $stdpack=str_replace($targetChars, "", $stdpack); $dealercheck=$row["$dealer"]; $dealercheck=str_replace($targetChars, "", $dealercheck); $dealercheck = number_format($dealercheck, 2, '.', ''); $distributorcheck=$row["$distributor"]; $distributorcheck=str_replace($targetChars, "", $distributorcheck); $distributorcheck = number_format($distributorcheck, 2, '.', ''); $listprice=$row["$list"]; $listprice=str_replace($targetChars, "", $listprice); $listprice = number_format($listprice, 2, '.', ''); $distributor=$row["$distributor"]; $distributor=str_replace($targetChars, "", $distributor); $cost=$distributor*.95; $cost = number_format($cost, 2, '.', ''); $level1=$row["$dealer"]; $level1=str_replace($targetChars, "", $level1); $level1 = number_format($level1, 2, '.', ''); $level2=$row["$dealer"]; $level2=str_replace($targetChars, "", $level2); $level2=$level2*.96; $level2 = number_format($level2, 2, '.', ''); $level3=$row["$dealer"]; $level3=str_replace($targetChars, "", $level3); $level3=$level3*.92; $level3 = number_format($level3, 2, '.', ''); $level4=$row["$dealer"]; $level4=str_replace($targetChars, "", $level4); $level4=$level4*.9; $level4 = number_format($level4, 2, '.', ''); $level5=$row["$dealer"]; $level5=str_replace($targetChars, "", $level5); $level5=$level5*.88; $level5 = number_format($level5, 2, '.', ''); $level6=$row["$dealer"]; $level6=str_replace($targetChars, "", $level6); $level6=$level6*.85; $level6 = number_format($level6, 2, '.', ''); $c1="1075"; $c2="$partnumber"; $c3=""; $c4=""; $c5="$stdpack"; $c6=""; $c7=""; $c8="$listprice"; $c9="$listprice"; $c10="$cost"; $c11="$level1"; $c12="$level2"; $c13="$level3"; $c14="$level4"; $c15="$level5"; $c16="$level6"; $c17="0"; $c18="0"; $c19="0"; $c20="0"; $c21="0"; $c22="0"; $c23="0"; $c24="0"; $c25="0"; $c26=""; if($distributorcheck==$dealercheck) { $c11="$dealercheck"; $c12="$dealercheck"; $c13="$dealercheck"; $c14="$dealercheck"; $c15="$dealercheck"; $c16="$dealercheck"; } else { $result2 = mysql_query("SELECT * FROM smith_prog_item WHERE `item` = $partnumber LIMIT 1"); while ($row2 = mysql_fetch_assoc($result2)) { if($row2["type"]=="NGK") { $c17="100"; $c18="$level6";} elseif ($row2["type"]=="OTHER") { $c11="$level6"; $c12="$level6"; $c13="$level6"; $c14="$level6"; $c15="$level6"; $c16="$level6"; } } } $outputstring = $c1. "\t".$c2."\t".$c3."\t".$c4."\t".$c5."\t".$c6."\t".$c7."\t".$c8."\t".$c9."\t".$c10."\t".$c11."\t".$c12."\t".$c13."\t".$c14."\t".$c15."\t".$c16."\t".$c17."\t".$c18."\t".$c19."\t".$c20."\t".$c21."\t".$c22."\t".$c23."\t".$c24."\t".$c25."\t".$c26."\t".$c27."\t".$c28."\t".$c29."\n"; //print $outputstring;echo"<br />"; fwrite($fp, $outputstring, strlen($outputstring)); } mysql_free_result($result); // show where we are at echo $limit_start,' - ',$limit_end,'<br />'; sleep(1); // give PHP a little nap to keep from overloading server ob_flush(); // as recommended by MadTechie, in case we go beyond the max execution time flush(); // add flush, to make sure it is outputted } //end for loop fclose($fp); echo"<br /><br /><a href=\"http://mysite.com/price/$filename\">Click here to preview file</a>";

That's good. Thank you.

What will it do if "SELECT * FROM prog_item WHERE `item` = $partnumber" does not return anything? Would it just skip that section? if($distributorcheck==$dealercheck) { $c11="$dealercheck"; $c12="$dealercheck"; $c13="$dealercheck"; $c14="$dealercheck"; $c15="$dealercheck"; $c16="$dealercheck"; } else { $result2 = mysql_query("SELECT * FROM prog_item WHERE `item` = $partnumber"); while ($row2 = mysql_fetch_assoc($result2)) { if($row2["type"]=="NGK") { $c17="100"; $c18="$level6";} elseif ($row2["type"]=="OTHER") { $c11="$level6"; $c12="$level6"; $c13="$level6"; $c14="$level6"; $c15="$level6"; $c16="$level6"; } } }

Cool, thanks.

I am trying to delete all the rows that do not have a value. The fields were originally formated in excel - accounting with 2 decimal places. Is there a better way to get rid of all of the rows that do not have valid prices? mysql_query("DELETE FROM {$tablename} WHERE $dealer=''"); mysql_query("DELETE FROM {$tablename} WHERE $dealer='$-'"); mysql_query("DELETE FROM {$tablename} WHERE $dealer='-'"); mysql_query("DELETE FROM {$tablename} WHERE $dealer='$0.00'"); mysql_query("DELETE FROM {$tablename} WHERE $dealer='0'"); mysql_query("DELETE FROM {$tablename} WHERE $dealer='0.00'"); mysql_query("DELETE FROM {$tablename} WHERE $dealer='0.0'"); mysql_query("DELETE FROM {$tablename} WHERE $dealer='$0.0'");

Does this look right? mysql_query("UPDATE {$tablename} AS a, {$tablename} AS b SET a.$list =b.$list, a.$dealer=b.$dealer, a.$distributor=b.$distributor, a.$sort=b.$sort, a.$stdpack=b.$stdpack WHERE LEFT(b.$part, CHAR_LENGTH(b.$part)-2)=a.$part AND RIGHT(b.$part, 2) IN ('-1', '-2', '-3', '-0')"); By the way... Thank you for all of your help!

Something like.... SELECT FROM MYTABLE WHERE PARTNUMBER ENDS WITH -3, -2, -1, OR -0 WHILE LOOP{ GET PRICE FIELD VALUES UPDATE MYTABLE SET $MYROW[listPRICE = ................. WHERE PARTNUMBER = $MYROW[PARTNUMBER] WITHOUT -1, -2,-3, OR -0 ON THE END {

But, isn't this setting all the values in the row as dashes. I am trying to take the values from the -0,-1,-2,-3 part numbers and update them into the part numbers without a dash based on the partnumber. Like..... 84-232-2 5.55 3.33 2.35 700 84-232 - 5.55 - - 700 So, after the update 84-232 would then become the same as 84-232-2 84-232 5.55 3.33 2.35 700

All of this data is in the same table.

The whole point of this is to get completely away from excel. A script we wrote for another one of these companies vendors saved them about 3 hours of time. In php all they had to do was upload the file, select which columns are which, then it makes all the calculations and ftp's it to their server. Now I am working on a file for this vendor. They always have these weird part number changes.

I am creating a little script that will take the price list our vendor gives us and turns it into a text file that will import into our inventory program. Some of the part numbers have changed to a new number. (Ex. 34-942 to 34-942-3) And they will put 34-942 as no longer available with dashes in the price fields. Well, we may still have inventory for that item and we want the updated price. So I am trying to find all the part numbers that have -0, -1, -2, or -3 appended to the end of it and apply the values to the part numbers without the -0, -1 ,-2, -3. Note: There may not always be numbers without a dash. Ex. There may be a 23-043-3, but there may not be a 23-043. I hope I am explaining better. I am trying.

I am trying to find the part numbers that have -1, -2, -3, and -0 on the end and copy the data from this row over to the part number that does not have a dash ending. This data is stored in MySQL. Example. 32-844-1 8.44 3.56 4.53 8 711 would copy to 32-844 - - 3.56 - 711