Jump to content

Saving an array to a csv file


rseigel
 Share

Recommended Posts

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

Link to comment
Share on other sites

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. :)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by rseigel
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

This thread is more than a year old. Are you sure you have something important to add to it?

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.