hyeteck

this would only work if you have a windows webserver though. Is there anyway to do it on a Linux machine? thanks

Hi, Is it possible to convert from one file type to another using php? For example, can you convert a doc to a pdf with php? thanks.

I only have one statement in there. Its the following one: $rs = mysql_query("LOAD DATA LOCAL INFILE 'outputfile.txt' REPLACE INTO TABLE products (pID, pPrice, pInStock, pMapprice, pWholesalePrice, pSell, pDisplay)") or die(mysql_error());

Hi, I am running mysql version 5.0.51-log. I have the following script. function update_prices() { $fullString = ""; $handle = @fopen("items.txt", "r"); if($handle && count(file("items.txt"))>1000) { $buffer = fgets($handle, 4096); while (!feof($handle)) { $buffer = fgets($handle, 4096); $chunks = explode(" ", $buffer); $prodId = $chunks[0]; $prodPrice = $chunks[1]; $prodPriceW = $prodPrice; $prodMap = $chunks[2]; $prodQuant = $chunks[3]; $prodSell = 1; $prodDisplay = 1; if($prodPrice==0) { $prodSell = 0; $prodDisplay = 0; //$result = mysql_query("UPDATE products SET pSell=0, pDisplay=0 WHERE pID='$prodId'") or die(mysql_error()); } else { if($prodPrice < 10) { $prodPrice *= 1.8; } elseif($prodPrice >= 10 && $prodPrice < 25) { $prodPrice *= 1.7; } elseif($prodPrice >= 25 && $prodPrice < 50) { $prodPrice *= 1.6; } elseif($prodPrice >= 50 && $prodPrice < 100) { $prodPrice *= 1.5; } elseif($prodPrice >= 100 && $prodPrice < 200) { $prodPrice *= 1.3; } elseif($prodPrice >= 200 && $prodPrice < 300) { $prodPrice *= 1.09; } elseif($prodPrice >= 300 && $prodPrice < 400) { $prodPrice *= 1.08; } elseif($prodPrice >= 400 && $prodPrice < 500) { $prodPrice *= 1.13; } elseif($prodPrice >= 500 && $prodPrice < 600) { $prodPrice *= 1.12; } elseif($prodPrice >= 600 && $prodPrice < 700) { $prodPrice *= 1.11; } elseif($prodPrice >= 700 && $prodPrice < 800) { $prodPrice *= 1.10; } elseif($prodPrice >= 800 && $prodPrice < 900) { $prodPrice *= 1.09; } elseif($prodPrice >= 900 && $prodPrice < 1000) { $prodPrice *= 1.08; } else { $prodPrice *= 1.07; } $prodPrice = floor($prodPrice); $prodPrice += 0.99; print $prodId . "\n"; $fullString .= $prodId . "," . $prodPrice . "," . $prodQuant . "," . $prodMap . "," . $prodPriceW . "," . $prodSell . "," . $prodDisplay . "\n"; //$result = mysql_query("UPDATE products SET pPrice=IF(pFreight = 1,'$prodPrice + 90','$prodPrice'), pInStock='$prodQuant', pMapprice='$prodMap', pWholesalePrice='$prodPriceW' WHERE pID='$prodId'") or die(mysql_error()); } } } fclose($handle); $fh = fopen("outfile.txt", 'w') or die("can't open file"); fwrite($fh, $fullString); fclose($fh); $rs = mysql_query("LOAD DATA LOCAL INFILE 'outputfile.txt' REPLACE INTO TABLE products (pID, pPrice, pInStock, pMapprice, pWholesalePrice, pSell, pDisplay)") or die(mysql_error()); } and its spitting out the following error: The used command is not allowed with this MySQL version I couldn't find anything about not allowing the LOAD DATA LOCAL INFILE command to run on my mysql version.

nevermind, i got it, thanks. I'm going to try this out now and see what kind of performance i get compared to running 17,000 separate queries.

hmm, im a little confused. Lets say i have the following data ProdID Quantity Price 5 22 15.99 Can you show me a sample datafile that would update the above data for that ProdID?

All the IDs have different values so trying to group together the IDs would not work. I like the idea of checking which values need to be changed to limit the number of queries that way. What i really like most is your idea of LOAD DATA INFILE. Pre-processing the data file would not be an issue, i just gotta figure out the format the data needs to be in. looking here http://dev.mysql.com/doc/refman/5.0/en/load-data.html i don't see information about using UPDATE in the data infile.

Hi, is there a way to combine 17,000 UPDATE queries into 1 query? I have a php script that runs through a while loop and updates a price field for 17,000 rows, hence 17,000 queries. Since i'm on a share host, the MySQL server is on a different machine so sending 17,000 queries takes time. Is there a way to combine them together? Here is my script. function update_prices() { $pri = array(0, 10, 25, 50, 100,200, 300, 400, 500, 600, 700,800, 900, 1000); $f = array(1.8,1.7,1.6,1.5,1.3,1.09,1.08,1.13,1.12,1.11,1.1,1.09,1.08,1.07); $file = "items.txt"; $contents = file($file); if(count($contents)>1000) { foreach($contents as $buffer) { $chunks = explode(" ", $buffer); $prodId = $chunks[0]; $prodPrice = $chunks[1]; $prodPriceW = $prodPrice; $prodMap = $chunks[2]; $prodQuant = $chunks[3]; if($prodPrice==0) { $result = mysql_query("UPDATE products SET pSell=0, pDisplay=0 WHERE pID='$prodId'") or die(mysql_error()); } else { $c=14; while ($pri[--$c] > $prodPrice); $prodPrice *= $f[$c]; $prodPrice = floor($prodPrice); $prodPrice += 0.99; print $prodId . "\n"; $query = "UPDATE products SET pPrice= IF(pFreight = 1,'$prodPrice + 90','$prodPrice'), pInStock='$prodQuant', pMapprice='$prodMap', pWholesalePrice='$prodPriceW' WHERE pID='$prodId'"; $result = mysql_query($query) or die(mysql_error()); } // end of prodPrice == 0 else } // end of foreach loop } // end of count > 1000 } // end of function thanks

btw, the following line has a slight issue. while ($pri[--$c] > $a); it should be while ($pri[--$c] > $prodPrice);

thanks guys, both of those code snippets really increased the speed. Removing the extra mysql statement really made a very big difference. It runs in 5 seconds on a VPS host but on a shared host it takes 20 minutes. I guess thats because the MySQL server is on the localhost for the VPS but on a shared host, the mysql server is on a different machine. Is there anyway to consolidate all the queries (about 17,000 of them) into one big query? I think this would improve performance on the shared host account because it would only need to send 1 query to the mysql machine instead of sending 17,000 queries.

the while loop is what is taking 99% of the time. The IF statement that reads the file to check the number of lines is actually really quick.

Hi, I'm trying to speed up my code but can't think of any other way to do so. Let me explain what the function below is doing. I have a tab delimited text file with 17,000 lines of data. There is a product id, price, and quantity on each line. I am reading in this data and updating the price and quantity to the corresponding product Id in my mysql database. I need to speed up the process as much as possible. Any ideas on what else i can do to the code below to increase performance? function update_prices() { $handle = @fopen("items.txt", "r"); if($handle && count(file("items.txt"))>1000) { $buffer = fgets($handle, 4096); while (!feof($handle)) { $buffer = fgets($handle, 4096); $chunks = explode(" ", $buffer); $prodId = $chunks[0]; $rs_getfreight = mysql_query("SELECT pFreight FROM products WHERE pID = '$prodId'"); if(mysql_num_rows($rs_getfreight)==1) { $prodPrice = $chunks[1]; $prodPriceW = $prodPrice; $prodMap = $chunks[2]; $prodQuant = $chunks[3]; if($prodPrice==0) { $result = mysql_query("UPDATE products SET pSell=0, pDisplay=0 WHERE pID='$prodId'") or die(mysql_error()); } else { if($prodPrice < 10) { $prodPrice *= 1.8; } elseif($prodPrice >= 10 && $prodPrice < 25) { $prodPrice *= 1.7; } elseif($prodPrice >= 25 && $prodPrice < 50) { $prodPrice *= 1.6; } elseif($prodPrice >= 50 && $prodPrice < 100) { $prodPrice *= 1.5; } elseif($prodPrice >= 100 && $prodPrice < 200) { $prodPrice *= 1.3; } elseif($prodPrice >= 200 && $prodPrice < 300) { $prodPrice *= 1.09; } elseif($prodPrice >= 300 && $prodPrice < 400) { $prodPrice *= 1.08; } elseif($prodPrice >= 400 && $prodPrice < 500) { $prodPrice *= 1.13; } elseif($prodPrice >= 500 && $prodPrice < 600) { $prodPrice *= 1.12; } elseif($prodPrice >= 600 && $prodPrice < 700) { $prodPrice *= 1.11; } elseif($prodPrice >= 700 && $prodPrice < 800) { $prodPrice *= 1.10; } elseif($prodPrice >= 800 && $prodPrice < 900) { $prodPrice *= 1.09; } elseif($prodPrice >= 900 && $prodPrice < 1000) { $prodPrice *= 1.08; } else { $prodPrice *= 1.07; } $prodPrice = floor($prodPrice); $prodPrice += 0.99; $row_freight = mysql_fetch_assoc($rs_getfreight); if($row_freight['pFreight']==1) $prodPrice += 90; print $prodId . "\n"; $result = mysql_query("UPDATE products SET pPrice='$prodPrice', pInStock='$prodQuant', pMapprice='$prodMap', pWholesalePrice='$prodPriceW' WHERE pID='$prodId'") or die(mysql_error()); } } } fclose($handle); } } thanks

im still trying to get a hang of this php stuff myself, but try this, it should work. $creepinfo = mysql_query ("SELECT * FROM creep_data WHERE minuserlvl < '$user[\'lvl\']' AND maxuserlvl > '$user[\'lvl\']' ORDER BY id rand()");

hi, Is it possible to display an image that is above the root directory using php? for example if i have an image in /home/user/uploads/test.jpg and lets say my root directory for my site is /home/user/public_html/ is it possible to display test.jpg on /home/user/public_html/index.html ? i am trying to create a secure file upload script and i think not having the file be directly accessed by the public is one of the main steps.

anybody?