Saving an array to a csv file

[rseigel]

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

[requinix]

The "something obvious" would be the mysql_query() to actually execute $result.

 

And try to use the newer and better mysqli or PDO extensions instead of older and deprecated mysql extension and its mysql_*() functions.

[PravinS]

try using this class

 

http://www.php-guru.in/2013/export-mysql-data-to-csv-file-using-php/

[rseigel]

The "something obvious" would be the mysql_query() to actually execute $result.

 

And try to use the newer and better mysqli or PDO extensions instead of older and deprecated mysql extension and its mysql_*() functions.

 

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.

[rseigel]

try using this class

 

http://www.php-guru.in/2013/export-mysql-data-to-csv-file-using-php/

 

Thanks. I tried - it didn't work as expected - I gave up.

 

Looks like it has potential though. Bookmarked.

[rseigel]

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.

[rseigel]

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

Edited August 11, 2013 by rseigel

[requinix]

Earlier the problem was that

// Write all the user records to the spreadsheet
foreach($results as $row)

$results is not an array but a resource you use to get rows. It's not actually all the rows.

 

Now the problem is you're trying to get values from $row that you never retrieved with the query.

And you're also

or die(mysql_error());

mixing PDO with mysql. And that "or die" is actually in the wrong spot (you haven't executed the query yet). And you should never "or die" for anything in the first place.