Databases
StackOverflow2013
Postsdbo
POTiming out while updating MySQL with PHP from a CSV
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTiming out while updating MySQL with PHP from a CSV
    primarykey
    data
    text
    <p>I need to come up with a way to make a large task faster to beat the timeout.</p> <p>I have <em>very</em> limited access to the server due to the restrictions of the hosting company.</p> <p>I have a system set up where a cron visits a PHP file that grabs a csv that contains data on some products. The csv does not contain all of the fields that the product would have. Just a handful of essential ones. </p> <p>I've read a fair number of articles on timeouts and handling csv's and currently (in an attempt to shave time) I have made a table (let's call it csv_data) to hold the csv data. I have a script that truncates the csv_data table then inserts data from the csv so each night the latest recordset from the csv is in that table (the csv file gets updated nightly). So far, no timeout problems..the task only takes about 4-5 seconds.</p> <p>The timeouts occur when I have to sift through the data to make updates to the products table. The steps that it is running right now is like this</p> <pre><code>1. Get the sku from csv_data table (that holds thousands of records) 2. Select * from Products where products.sku = csv.sku (products table also holds thousands of records to loop through) 3. Get numrows. If numrows&lt;0{no record in products, so skip}. If numrows&gt;1{duplicate entries, don't change anything, but later on report the sku} If numrows==1{Update selected fields in the products table with csv data} 4. Go to the next record in csv_data all over again </code></pre> <p>(I figured outlining the process is shorter and easier than dropping in the code.) I looked into MySQl views and stored procedures but I am not skilled enough in it to know if it will handle the 'if' statement portion.</p> <p>Is there anything I can do to make this faster to avoid the timeouts? </p> <h2><strong>edit:</strong></h2> <p>I should mention that <code>set_time_limit(0);</code> isn't doing it. And if it helps, the server uses <code>IIS7</code> and <code>fastcgi</code> Thanks for your help.</p> <p>Update after using suggestions from Jakob and Shawn:</p> <p>I'm doing something wrong. The speed is definitely faster and the csv sku is incrementing, </p> <p>but when I tried to implement Shawn's solution; the query is giving me a PHP Warning: mysql_result() expects parameter 1 to be resource, boolean error. </p> <p>Can you help me spot what I am doing wrong?</p> <p>Here is the section of code:</p> <pre><code>$csvdata="SELECT * FROM csv_update"; $csvdata_result=mysql_query($csvdata); mysql_query($csvdata); $csvdata_num = mysql_num_rows($csvdata_result); $i=0; while($i&lt;$csvdata_num){ $csv_code=@mysql_result($csvdata_result,$i,"skucode"); $datacheck=NULL; $datacheck=substr($csv_code,0,1); if($datacheck&gt;='0' &amp;&amp; $datacheck&lt;='9'){ $csv_price=@mysql_result($csvdata_result,$i,"price"); $csv_retail=@mysql_result($csvdata_result,$i,"retail"); $csv_stock=@mysql_result($csvdata_result,$i,"stock"); $csv_weight=@mysql_result($csvdata_result,$i,"weight"); $csv_manufacturer=@mysql_result($csvdata_result,$i,"manufacturer"); $csv_misc1=@mysql_result($csvdata_result,$i,"misc1"); $csv_misc2=@mysql_result($csvdata_result,$i,"misc2"); $csv_selectlist=@mysql_result($csvdata_result,$i,"selectlist"); $csv_level5=@mysql_result($csvdata_result,$i,"level5"); $csv_frontpage=@mysql_result($csvdata_result,$i,"frontpage"); $csv_level3=@mysql_result($csvdata_result,$i,"level3"); $csv_minquantity=@mysql_result($csvdata_result,$i,"minquantity"); $csv_quantity1=@mysql_result($csvdata_result,$i,"quantity1"); $csv_discount1=@mysql_result($csvdata_result,$i,"discount1"); $csv_quantity2=@mysql_result($csvdata_result,$i,"quantity2"); $csv_discount2=@mysql_result($csvdata_result,$i,"discount2"); $csv_quantity3=@mysql_result($csvdata_result,$i,"quantity3"); $csv_discount3=@mysql_result($csvdata_result,$i,"discount3"); $count_check="SELECT COUNT(*) AS totalCount FROM products WHERE skucode = '$csv_code'"; $count_result=mysql_query($count_check); mysql_query($count_check); $totalCount=@mysql_result($count_result,0,'totalCount'); $loopCount = ceil($totalCount / 25); for($j = 0; $j &lt; $loopCount; $j++){ $prod_check="SELECT skucode FROM products WHERE skucode = '$csv_code' LIMIT ($loopCount*25), 25;"; $prodresult=mysql_query($prod_check); mysql_query($prod_check); $prodnum =@mysql_num_rows($prodresult); $prod_id=@mysql_result($prodresult,0,"catalogid"); if($prodnum&lt;1){ echo "NOT FOUND:$csv_code&lt;br&gt;"; $count_sku_not_found=$count_sku_not_found+1; $list_sku_not_found=$list_sku_not_found." $csv_code";} if($prodnum&gt;1){ echo "DUPLICATE:$csv_ccode&lt;br&gt;"; $count_duplicate_skus=$count_duplicate_skus+1; $list_duplicate_skus=$list_duplicate_skus." $csv_code";} if ($prodnum==1){ ///This prevents an overwrite from happening if the csv file doesn't produce properly if ($csv_price!="" OR $csv_price!=NULL) {$sql_price='price="'.$csv_price.'"';} if ($csv_retail!="" OR $csv_retail!=NULL) {$sql_retail=',retail="'.$csv_retail.'"';} if ($csv_stock!="" OR $csv_stock!=NULL) {$sql_stock=',stock="'.$csv_stock.'"';} if ($csv_weight!="" OR $csv_weight!=NULL) {$sql_weight=',weight="'.$csv_weight.'"';} if ($csv_manufacturer!="" OR $csv_manufacturer!=NULL) {$sql_manufacturer=',manufacturer="'.$csv_manufacturer.'"';} if ($csv_misc1!="" OR $csv_misc1!=NULL) {$sql_misc1=',misc1="'.$csv_misc1.'"';} if ($csv_misc2!="" OR $csv_misc2!=NULL) {$sql_pother2=',pother2="'.$csv_misc2.'"';} if ($csv_selectlist!="" OR $csv_selectlist!=NULL) {$sql_selectlist=',selectlist="'.$csv_selectlist.'"';} if ($csv_level5!="" OR $csv_level5!=NULL) {$sql_level5=',level5="'.$csv_level5.'"';} if ($csv_frontpage!="" OR $csv_frontpage!=NULL) {$sql_frontpage=',frontpage="'.$csv_frontpage.'"';} $import="UPDATE products SET $sql_price $sql_retail $sql_stock $sql_weight $sql_manufacturer $sql_misc1 $sql_misc2 $sql_selectlist $sql_level5 $sql_frontpage $sql_in_stock WHERE skucode='$csv_code'"; mysql_query($import) or die(mysql_error("error updating in products table")); echo "Update ".$csv_code." successful ($i)&lt;br&gt;"; $count_success_update_skus=$count_success_update_skus+1; $list_success_update_skus=$list_success_update_skus." $csv_code"; //empty out variables $sql_price=''; $sql_retail=''; $sql_stock=''; $sql_weight=''; $sql_manufacturer=''; $sql_misc1=''; $sql_misc2=''; $sql_selectlist=''; $sql_level5=''; $sql_frontpage=''; $sql_in_stock=''; $prodnum=0; } } $i++; } </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDustin Campbell
    1. USDustin Campbell
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POTiming out while updating MySQL with PHP from a CSV
      1. This table or related slice is empty.
      1. VTUpMod
    1. COShow your code so I can help suggest a "lazy load handler"
      singulars
      1. POTiming out while updating MySQL with PHP from a CSV
      1. USBlake
    2. CONever grab `*` when you don't have to (this depends on HOW much data you have per row too), if all you need is to know `numrows` just grab an id, or single value and test that way.
      singulars
      1. POTiming out while updating MySQL with PHP from a CSV
      1. USJakub
    3. COJakub, thanks :) I grabbed just the product.sku field and it dropped my time to about 1.5 minutes and I have beaten the timeout YAY!!!!! So thank you, you definitely helped improve it a lot. I am also going to implement Shawn's idea to see if I can get it faster because while 1.5 minutes is good, I can see the script needing more efficiency as more products get added to the csv
      singulars
      1. POTiming out while updating MySQL with PHP from a CSV
      1. USDustin Campbell
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload