rseigel

Thanks for the kick in the right direction. I actually ended up using: SELECT p.reference AS sku, IFNULL(p.price - sp.reduction,p.price) AS price, sa.quantity, mpo.asin1 AS 'product-id', 'ASIN' AS 'product-id-type', p.condition AS 'condition-type' FROM product p LEFT JOIN specific_price sp USING (id_product) JOIN stock_available sa USING (id_product) JOIN marketplace_product_option mpo USING (id_product) WHERE mpo.asin1 > '' which actually eliminates NULL and blank values. Off to the next challenge.....

Actually that should have read: Now the only thing I need to do is eliminate all records where mpo.asin1 doesn't exist or is NULL.

Wicked. That works. Now the only thing I need to do is eliminate all records where mpo.asin1 doesn't exist.

ok.....sorted the alias names part.... SELECT p.reference AS sku, p.price - sp.reduction AS price, sa.quantity, m.asin1 AS 'product-id', 'ASIN' AS 'product-id-type', p.condition AS 'condition-type' FROM product AS p, specific_price AS sp, stock_available AS sa, marketplace_product_option AS m WHERE p.id_product = sp.id_product AND p.id_product = sa.id_product AND p.id_product = m.id_product Still confused ablut the left join. Do I need 3 left joins in the SELECT?

Ok...thanks,. I get what you mean....I just can't get the syntax correct. Any chance of a bit more of a hint?

I have the following SELECT that almost works: SELECT product.reference AS sku, product.price - specific_price.reduction AS price, stock_available.quantity, marketplace_product_option.asin1 AS 'product-id', 'ASIN' AS 'product-id-type', product.condition AS 'condition-type' FROM product, specific_price, stock_available, marketplace_product_option WHERE product.id_product = specific_price.id_product AND product.id_product = stock_available.id_product AND product.id_product = marketplace_product_option.id_product The problem is that sometimes there is no entry of specific_price.reduction (it's not ZERO - it just doesn't have a row in the table at all for that particular product). What I want to do is if specific_price.reduction exists then do the math (product.price - specific_price.reduction) otherwise just use product.price as the value. I'd like to do this INLINE in the SELECT rather than resorting to PHP. I hope that makes sense. Ron

Stop the presses: Tried something different: $conn = new PDO("mysql:host=localhost;dbname=db_name", 'db_user', 'password'); $sql = ("SELECT product.reference, product.price - specific_price.reduction, stock_available.quantity, marketplace_product_option.asin1, 'ASIN', product.condition FROM product, specific_price, stock_available, marketplace_product_option WHERE product.id_product = specific_price.id_product AND product.id_product = stock_available.id_product AND product.id_product = marketplace_product_option.id_product") or die(mysql_error()); $results = $conn->query($sql); // Pick a filename and destination directory for the file // Remember that the folder where you want to write the file has to be writable $filename = "amazon-export.csv"; // Actually create the file // The w+ parameter will wipe out and overwrite any existing file with the same name $handle = fopen($filename, 'w+'); // Write the spreadsheet column titles / labels fputcsv($handle, array('sku','price','quantity','product-id','product-id-type','condition-type')); // Write all the user records to the spreadsheet foreach($results as $row); { fputcsv($handle, array($row['sku'], $row['price'], $row['quantity'], $row['product-id'], $row['product-id-type'], $row['condition-type'])); echo $row; } // Finish writing the file fclose($handle); Now getting: Notice: Undefined index: sku in /home/aonewebs/public_html/atest.php on line 36 Notice: Undefined index: price in /home/aonewebs/public_html/atest.php on line 36 Notice: Undefined index: product-id in /home/aonewebs/public_html/atest.php on line 36 Notice: Undefined index: product-id-type in /home/aonewebs/public_html/atest.php on line 36 Notice: Undefined index: condition-type in /home/aonewebs/public_html/atest.php on line 36 Array

Actually it's even worse than that.... Warning: Invalid argument supplied for foreach() in /home/aonewebs/public_html/atest.php on line 32 Notice: Undefined variable: row in /home/aonewebs/public_html/atest.php on line 34 Notice: Undefined variable: row in /home/aonewebs/public_html/atest.php on line 34 Notice: Undefined variable: row in /home/aonewebs/public_html/atest.php on line 34 Notice: Undefined variable: row in /home/aonewebs/public_html/atest.php on line 34 Notice: Undefined variable: row in /home/aonewebs/public_html/atest.php on line 34 Notice: Undefined variable: row in /home/aonewebs/public_html/atest.php on line 34 Notice: Undefined variable: row in /home/aonewebs/public_html/atest.php on line 35 Yikes.....help please.

Thanks. I tried - it didn't work as expected - I gave up. Looks like it has potential though. Bookmarked.

Ok...that was embarassing. I've got this far now (changed a few things around). $sql = ("SELECT product.reference, product.price - specific_price.reduction, stock_available.quantity, marketplace_product_option.asin1, 'ASIN', product.condition FROM product, specific_price, stock_available, marketplace_product_option WHERE product.id_product = specific_price.id_product AND product.id_product = stock_available.id_product AND product.id_product = marketplace_product_option.id_product") or die(mysql_error()); $results = mysql_query($sql); // Pick a filename and destination directory for the file // Remember that the folder where you want to write the file has to be writable $filename = "amazon-export.csv"; // Actually create the file // The w+ parameter will wipe out and overwrite any existing file with the same name $handle = fopen($filename, 'w+'); // Write the spreadsheet column titles / labels fputcsv($handle, array('sku','price','quantity','product-id','product-id-type','condition-type')); // Write all the user records to the spreadsheet foreach($results as $row) { fputcsv($handle, array($row['sku'], $row['price'], $row['quantity'], $row['product-id'], $row['product-id-type'], $row['condition-type'])); echo $row; } // Finish writing the file fclose($handle); and I get this error: Warning: Invalid argument supplied for foreach() in /home/aonewebs/public_html/atest.php on line 32 Banging my head against the wall here.

I'm haveing a heck of a time trying to figure this out. All I want to do is save a SELECT array (mysql) to a CSV file. Here's what I'm trying. It $result = "SELECT product.reference AS sku, product.price - specific_price.reduction AS price, stock_available.quantity, marketplace_product_option.asin1 AS 'product-id', 'ASIN' AS 'product-id-type', product.condition AS 'condition-type' FROM product, specific_price, stock_available, marketplace_product_option WHERE product.id_product = specific_price.id_product AND product.id_product = stock_available.id_product AND product.id_product = marketplace_product_option.id_product;"; $fp = fopen('/home/aonewebs/public_html/amazon-export.csv', 'w+'); if ($fp && $result) { while ($row = mysql_fetch_row($result)) { fputcsv($fp, array_values($row)); } } creates an empty file (even though therer are 3000 lines to that array. I'm sure I'm missing something obvious. Any and all help greatly appreciated. Ron

Answered my own question. It's as simple as: SELECT product.reference AS sku, product.price - specific_price.reduction AS price, stock_available.quantity, product.ean13 AS 'product-id', 'ASIN' AS 'product-id-type', product.condition FROM product, specific_price, stock_available WHERE product.id_product = specific_price.id_product AND product.id_product = stock_available.id_product; Sometimes just asking the question get's the old pumpkin working.

I'm trying to run the following SELECT to get the data I need from my database. SELECT product.reference AS sku, product.price - specific_price.reduction AS price, stock_available.quantity, product.ean13 AS 'product-id', 'ASIN' AS 'product-id-type', product.condition FROM product, specific_price, stock_available WHERE product.id_product = specific_price.id_product = stock_available.id_product; PRoblem is that it takes too long to execute. I'm sure it's because I'm pulling from 3 different tables. Does anyone have a way to optimize this so it will work. I know all the field names are correct and everything worked until IO added that third table. Thanks,

The other table didn't have indexes. I did this: mysql_query("DROP TABLE IF EXISTS tmp_price_compare") or die(mysql_error()); mysql_query("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()); mysql_query("CREATE INDEX indx_tmp ON tmp_price_compare (supplier_reference)") or die(mysql_error()); and BOOM. The SELECT is blazing fast. tmp_price_compare is in another script that runs once an hour through a cron. Thanks for the tip.

I have the following code: mysql_connect("localhost", "username", "password") or die(mysql_error()); mysql_select_db("database") or die(mysql_error()); error_reporting(E_ALL); ini_set('display_errors', 1); mysql_query("DROP TABLE IF EXISTS tmp_discontinued") or die(mysql_error()); mysql_query("CREATE TABLE IF NOT EXISTS tmp_discontinued ( supplier_reference varchar(32) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8") or die(mysql_error()); $result = mysql_query("INSERT INTO tmp_discontinued (supplier_reference) SELECT product_supplier_reference FROM product_supplier") or die(mysql_error()); $result2 = mysql_query('SELECT * FROM tmp_discontinued WHERE tmp_discontinued.supplier_reference NOT IN (SELECT supplier_reference FROM tmp_price_compare) ORDER BY tmp_discontinued.supplier_reference') or die(mysql_error()); echo "DISCONTINUED PRODUCTS<br /><br />"; while($row = mysql_fetch_array($result)) { echo "{$row['supplier_reference']}<br />"; } echo "<br /><br />"; The table temp_discontinued is created and populated. No problems there. The issue is that I get the following error: Lost connection to MySQL server during query This table has around 20,000 records. Does anyone see any way to optimize this so I won't get the lost connection? Thanks

Not behind a proxy. If I change the URL it brings up a local version of google's home page.

Looking at their server config: max_execution_time 30 memory_limit 32M Do either or both of these create a problem?

Finally we have a error message to deal with. Warning: file_get_contents(http://www.azuregreenw.com/StockInfo.csv): failed to open stream: Connection timed out in /home1/aonewebs/public_html/AG_inventory_update.php on line 45 Does this mean there's nothing I can do unless I can convince my supplier to whatever controls that?

Tried that as well. Same result. This should be s o simple. I'm doing the exact same thing with 2 other suppliers so far. Is it possible it's something to do with their configuration on their end?

This is what I have so far: error_reporting(E_ALL); ini_set('memory_limit', '4096M'); $csv = file_get_contents('http://www.azuregreenw.com/StockInfo.csv'); if ($csv === false) { echo 'File not good!</br>'; } else { echo $csv; } No other erros are thrown. Just: File not good! I'm at a loss at this point. Thanks for all the help. Hopefully this creates an aha moment for someone.

So..... $csv = file_get_contents('http://www.azuregreenw.com/StockInfo.csv'); if ($csv === false) { echo 'File not good!'; } else { echo $csv; } Gets me the response: File not good! Where to next?

No. Let me try to be clearer. My supplier has a daily updated file that they store on their server. It essentially is for updating inventory counts on my web site. I'm trying to get that file (by any means possible) and use it to update my inventory. I have the code in place that will do that. I'm just having trouble copying the file from their server to mine. Hopefully that makes more sense.

Thanks for your answer. Does it matter that the file I'm moving is on a remote server? In other words....I'm trying to copy/move/whatever a csv file from my suppliers website to mine.

I have the following line: PHP Code: copy ('http://www.azuregreenw.com/StockInfo.csv', 'AG.csv'); It works with other suppliers I grab inventory files from but not this one. The file is not copied. I've added this to the top of my script: ini_set('post_max_size', '16M'); ini_set('upload_max_filesize', '16M'); This file is 1.31 MB if that makes a difference. Their phpinfo file is at http://www.azuregreenw.com/phpinfo.php Does anyone know what may stop it from copying and what I might be able to do to make it work?

It sure was. Thanks Jessica and thank you very much shlumph. All is right in the universe again.