rseigel

I've tried everything I can think of to make it work your way but I can't get it.

Well, it works. I get what you're saying Jessica. I'm just at the point if it works why mess with it.

Here's the final result for reference. while($row = mysql_fetch_array($result)) { echo "<a href=https://www.bnfusa.com/item/item.lasso?dsc2="; echo $row['supplier_reference']; echo ">"; echo $row['supplier_reference']; echo"</a><br />"; } Thanks.

Thanks Jessica. You are always awesome at pointing me in the right direction without spoon feeding it. Great way to learn. The not closing the opening <a> tag was the one that I was overlooking. Here's the final code that works: while($row = mysql_fetch_array($result)) { echo "<a href=https://www.bnfusa.com/item/item.lasso?dsc2="; echo $row['supplier_reference']; echo ">"; echo $row['supplier_reference']; echo"</a><br />"; } Thanks again.

I'm using the following code: $result = mysql_query('SELECT * FROM tmp_price_compare WHERE tmp_price_compare.supplier_reference NOT IN (SELECT product_supplier_reference FROM product_supplier)') or die(mysql_error()); echo "NEW PRODUCTS<br /><br />"; while($row = mysql_fetch_array($result)) { echo "<a href=https://www.mysupplier.com/item/item.lasso?dsc2=" . $row['supplier_reference'] . echo $row['supplier_reference'] . echo"</a><br />"; } The SELECT works perfectly and results are as expected. I'm having trouble getting the link coded correctly. I'm sure it's something silly. I was hoping someone with more experience than I could spot it. Thanks, Ron

Thanks very much for this. I'm going to try it that way as well so I have some more knowledge for the future.

Ok, so this is what I'm trying: $result = mysql_query('SELECT * FROM tmp_price_compare WHERE tmp_price_compare.supplier_reference NOT IN (SELECT product_supplier_reference FROM product_supplier)') or die(mysql_error()); echo "NEW PRODUCTS<br /><br />"; while($row = mysql_fetch_array($result)) { echo "<a href=https://www.mysupplier.com/item/item.lasso?dsc2=" . $row['supplier_reference'] . echo $row['supplier_reference'] . echo"</a><br />"; } The SELECT itself works perfectly. I'm just having trouble with the output. I'm sure it's something simple. I'm just not seeing the problem. Anyone? Thanks, Ron

That works perfectly. Thanks a million. Now I just need to figure out how to display it.

I have two tables that I want to compare. One is a temporary table that includes the product id, quantity, retail price and wholesale price (product feed from my supplier). The other is a permanent table that includes among other things the same product id. I want to see if an id is in the temp table and not the other one (meaning it's a new product). I also want to see if an id is in the permanent table and not in the temp table (meaning it's a discontinued product). I'd like to be able to output these to the web page so that I can address them. I've searched and I'm getting all kinds of different ideas around the web. Can anyone point me in the right direction? Some sample code, a link, a kick in the right direction - any and all answers are much appreciated. Thanks, Ron

Yikes! I've done some digging on this one and I'm confused. I found this: http://redbonzai.com/update-multiple-rows-in-a-single-query/ Is that a good example to go by for what you're talking about?

Thanks for your help and your patience. I'm stoked that this is working now. Now off to the next part....

For the record '1600SLIM' is the first supplier_reference that contains letters instead of all numbers.

Thanks a million for the help fixing my code. Here's what I have now: ini_set('auto_detect_line_endings', true); $handle = fopen("GeneratedList.csv", "r+"); $contents = file('GeneratedList.csv'); $header = true; while (($data = fgetcsv($handle, 100000, ",")) !== FALSE) { if ($header == true) { $header = false; continue; } $supplier_reference = $data[0]; $quantity = $data[1]; $price = $data[2]; $wholesale_price = $data[3]; mysql_query("UPDATE stock_available, product SET stock_available.quantity = $quantity WHERE stock_available.id_product = product.id_product AND product.supplier_reference = '$supplier_reference'") or die(mysql_error()); mysql_query("UPDATE stock_available, product_attribute SET stock_available.quantity = $quantity WHERE stock_available.id_product_attribute = product_attribute.id_product_attribute AND product_attribute.supplier_reference = '$supplier_reference'") or die(mysql_error()); $query = "INSERT INTO tmp_price_compare (supplier_reference,quantity,price,wholesale_price) VALUES($supplier_reference,$quantity,$price,$wholesale_price)"; mysql_query($query) or die("Query: $query<br>Error: " . mysql_error()); } fclose($handle); The ewrror I'm getting now is: Query: INSERT INTO tmp_price_compare (supplier_reference,quantity,price,wholesale_price) VALUES(1600SLIM,64,15.95,8.33) Error: Unknown column '1600SLIM' in 'field list' Any ideas? The values are there. Weird.

Another good question. No good answer. This is a mismash of code that I've pieced together searching and asking questions.

AHA!!! Now I see what you mean. Query: INSERT INTO tmp_price_compare (supplier_reference,quantity,price,wholesale_price) VALUES(,,, Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',,' at line 2 I'm assuming this means that all values are empty?

Easy answer - cause it works and I don't know how to do what you're suggesting. Any hints (or flat out solutions) on how to improve my code is always appreciated.

I feel silly for having to ask but what do you mean by this? I know what echo is - just not sure what to do with it code wise here.

Of course I didn't get an error since I forgot the die code. Here's the error I get: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',,' at line 2

ACK!! One more issue related to this. $handle = fopen("GeneratedList.csv", "r+"); $contents = file('GeneratedList.csv'); $header = true; while (($data = fgetcsv($handle, 100000, ",")) !== FALSE) { if ($header == true) { $header = false; continue; } $num = count($data); for ($c=0; $c < $num; $c++) { if ($c == 1) { $supplier_reference = $data[($c-1)]; } if ($c == 2) { $quantity = $data[($c-1)]; mysql_query("UPDATE stock_available, product SET stock_available.quantity = $quantity WHERE stock_available.id_product = product.id_product AND product.supplier_reference = '$supplier_reference'") or die(mysql_error()); mysql_query("UPDATE stock_available, product_attribute SET stock_available.quantity = $quantity WHERE stock_available.id_product_attribute = product_attribute.id_product_attribute AND product_attribute.supplier_reference = '$supplier_reference'") or die(mysql_error()); } if ($c == 3) { $price = $data[($c-1)]; } if ($c == 4) { $wholesale_price = $data[($c-1)]; } mysql_query("INSERT INTO tmp_price_compare (supplier_reference,quantity,price,wholesale_price) VALUES($supplier_reference,$quantity,$price,$wholesale_price"); } } fclose($handle); The INSERT doesn't give any errors but it also doesn't insert any values into the table. Any ideas?

This I did not know. Thanks.

The following query works fine when I do it phpmyadmin. DROP TABLE IF EXISTS tmp_price_compare; CREATE TABLE IF NOT EXISTS tmp_price_compare ( supplier_reference varchar(32) DEFAULT NULL, quantity int(10) NOT NULL DEFAULT '0', price decimal(20,6) NOT NULL DEFAULT '0.000000', wholesale_price decimal(20,6) NOT NULL DEFAULT '0.000000' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; When I try to put it in my code though like so: mysql_query("DROP TABLE IF EXISTS tmp_price_compare; CREATE TABLE IF NOT EXISTS tmp_price_compare ( supplier_reference varchar(32) DEFAULT NULL, quantity int(10) NOT NULL DEFAULT '0', price decimal(20,6) NOT NULL DEFAULT '0.000000', wholesale_price decimal(20,6) NOT NULL DEFAULT '0.000000' ) ENGINE=InnoDB DEFAULT CHARSET=utf8;") or die(mysql_error()); it gives me the following error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE IF NOT EXISTS tmp_price_compare ( supplier_reference varchar(32' at line 2 Any thoughts? No doubt it's something simple. I'm still learning. Thanks, Ron

We have a winner!!!!! Thanks a million salathe. That works perfectly. My inventory updating (for this supplier at least) is finished. Now I can just pop it in a cron and life is good. Cheers, Ron

Here's a link to the file: http://a1webshopping.com/GeneratedList.csv

Nice! We're getting there. This code: $handle = fopen("GeneratedList.csv", "r+"); //ini_set('auto_detect_line_endings', true); // $contents = file('GeneratedList.csv'); fgetcsv($handle, 100000, ","); while (($data = fgetcsv($handle, 100000, ",")) !== FALSE) { $num = count($data); for ($c=0; $c < $num; $c++) { if ($c == 1) { $supplier_reference = $data[($c-1)]; } if ($c == 2) { $quantity = $data[($c-1)]; mysql_query("UPDATE stock_available, product SET stock_available.quantity = $quantity WHERE stock_available.id_product = product.id_product AND product.supplier_reference = '$supplier_reference'") or die(mysql_error()); mysql_query("UPDATE stock_available, product_attribute SET stock_available.quantity = $quantity WHERE stock_available.id_product_attribute = product_attribute.id_product_attribute AND product_attribute.supplier_reference = '$supplier_reference'") or die(mysql_error()); } if ($c == 3) { } if ($c == 4) { } } } fclose($handle); works perfectly on the clean csv file with headers that I saved with calc. Sometimes the simplest solutions are the best. Now when I try to get the file dynamically with the following code: copy('http://www.mysupplier.com/utilities/cgen.lasso?an8=10012538&fmt=csv', 'GeneratedList.csv'); // $handle = fopen("GeneratedList.csv", "r+"); ini_set('auto_detect_line_endings', true); $contents = file('GeneratedList.csv'); fgetcsv($handle, 100000, ","); while (($data = fgetcsv($handle, 100000, ",")) !== FALSE) { $num = count($data); for ($c=0; $c < $num; $c++) { if ($c == 1) { $supplier_reference = $data[($c-1)]; } if ($c == 2) { $quantity = $data[($c-1)]; mysql_query("UPDATE stock_available, product SET stock_available.quantity = $quantity WHERE stock_available.id_product = product.id_product AND product.supplier_reference = '$supplier_reference'") or die(mysql_error()); mysql_query("UPDATE stock_available, product_attribute SET stock_available.quantity = $quantity WHERE stock_available.id_product_attribute = product_attribute.id_product_attribute AND product_attribute.supplier_reference = '$supplier_reference'") or die(mysql_error()); } if ($c == 3) { } if ($c == 4) { } } } fclose($handle); Something I've noticed. When I grab the file dynamically and open it with Notepad++ without touching it with calc I get: "Item Number","Available Inventory","Retail Price","Each Price" "03657","451","12.95","2.77" "101","778","23.95","3.28" "10212","42","144.00","48.00" "103","783","26.95","3.65" "10312","19","180.00","63.16" "105","1182","23.95","2.90" "107","171","25.95","3.70" "118","2878","25.95","3.69" "119","166","24.95","3.48" "12512","25","240.00","84.20" "12612","17","216.00","75.80" "127","0","24.95","3.24" "128","8614","23.95","3.35" "129","5366","23.95","3.28" "130","844","30.95","5.48" "131","40","19.95","2.90" "132","1985","23.95","3.27" If I open it with calc and save it as a csv first and then open it with Notepad++ I get: Item Number,Available Inventory,Retail Price,Each Price 3657,451,12.95,2.77 101,778,23.95,3.28 10212,42,144,48 103,783,26.95,3.65 10312,19,180,63.16 105,1182,23.95,2.9 107,171,25.95,3.7 118,2878,25.95,3.69 119,166,24.95,3.48 12512,25,240,84.2 12612,17,216,75.8 127,0,24.95,3.24 128,8614,23.95,3.35 Could it be as simple as stripping the quotes from around the values before processing the values. I'm thinking I could add something to these lines: $supplier_reference = $data[($c-1)]; and $quantity = $data[($c-1)]; to strip the quotes. Any thoughts on how I could do that? Thanks, Ron

Ok, I've narrowed it down. If I download the csv manually and chop the header off and upload it works. If I download the csv manually and leave the header as is it doesn't. Here's the code I'm using on a clean csv file saved with calc with the header line intact. $handle = fopen("GeneratedList.csv", "r+"); //ini_set('auto_detect_line_endings', true); // $contents = file('GeneratedList.csv'); $header = 'true'; while (($data = fgetcsv($handle, 100000, ",")) !== FALSE) { if ($header = 'true') { $header = 'false'; break; } $num = count($data); for ($c=0; $c < $num; $c++) { if ($c = 1) { $supplier_reference = $data[($c-1)]; } if ($c = 2) { $quantity = $data[($c-1)]; mysql_query("UPDATE stock_available, product SET stock_available.quantity = $quantity WHERE stock_available.id_product = product.id_product AND product.supplier_reference = '$supplier_reference'") or die(mysql_error()); mysql_query("UPDATE stock_available, product_attribute SET stock_available.quantity = $quantity WHERE stock_available.id_product_attribute = product_attribute.id_product_attribute AND product_attribute.supplier_reference = '$supplier_reference'") or die(mysql_error()); } if ($c = 3) { } if ($c = 4) { } } } fclose($handle); Sooooo...all I can figure out is that either the code to skip the header isn't right or for some reason the csv file is corrupted until I remove the header manually. Help. Thanks, Ron