export mysql query array using fputcsv

Question

I am trying to export the results of a sql query to CSV using fputcsv however keep getting the error "fputcsv() expects parameter 2 to be array, string given".

I followed the answer given here - Query mysql and export data as CSV in PHP - but it throws the error above. I understand that $val is a string and won't work as fputcsv needs an array but don't understand how to get each row into the csv. If I substitute $row for $val I only get the first row of the result in the csv rather than it looping. I know I'm missing something glaringly obvious just need a nudge to show me what it is please. Code is below:

// MySQLi Query
$result = mysqli_query($con, "

SELECT d.order_id AS 'Order ID',
m1.meta_value AS 'First Name',
m2.meta_value AS 'Last Name',
m3.meta_value AS 'Address1',
m4.meta_value AS 'Address2',
m5.meta_value AS 'City',
m6.meta_value AS 'State',
m7.meta_value AS 'Post Code',
f1.meta_value AS 'SKU',
d.order_item_name AS 'Product Name',
e2.meta_value AS 'Quantity',
m8.meta_value AS 'Tracking - Carrier',
m9.meta_value AS 'Tracking - Code',
m10.meta_value AS 'Tracking - Date',
m11.meta_value AS 'Tracking - Picked'

FROM dk_posts p
LEFT JOIN dk_postmeta m1 ON p.id = m1.post_id
AND m1.meta_key = '_shipping_first_name'
LEFT JOIN dk_postmeta m2 ON p.id = m2.post_id
AND m2.meta_key = '_shipping_last_name'
LEFT JOIN dk_postmeta m3 ON p.id = m3.post_id
AND m3.meta_key = '_shipping_address_1'
LEFT JOIN dk_postmeta m4 ON p.id = m4.post_id
AND m4.meta_key = '_shipping_address_2'
LEFT JOIN dk_postmeta m5 ON p.id = m5.post_id
AND m5.meta_key = '_shipping_city'
LEFT JOIN dk_postmeta m6 ON p.id = m6.post_id
AND m6.meta_key = '_shipping_state'
LEFT JOIN dk_postmeta m7 ON p.id = m7.post_id
AND m7.meta_key = '_shipping_postcode'
LEFT JOIN dk_postmeta m8 ON p.id = m8.post_id
AND m8.meta_key = 'ywot_carrier_name'
LEFT JOIN dk_postmeta m9 ON p.id = m9.post_id
AND m9.meta_key = 'ywot_tracking_code'
LEFT JOIN dk_postmeta m10 ON p.id = m10.post_id
AND m10.meta_key = 'ywot_pick_up_date'
LEFT JOIN dk_postmeta m11 ON p.id = m11.post_id
AND m11.meta_key = 'ywot_picked_up'

LEFT JOIN dk_woocommerce_order_items d ON d.order_id=m1.post_id

LEFT JOIN dk_woocommerce_order_itemmeta e1 ON d.order_item_id =     e1.order_item_id
AND e1.meta_key = '_product_id'
LEFT JOIN dk_woocommerce_order_itemmeta e2 ON d.order_item_id = e2.order_item_id
AND e2.meta_key = '_qty'

LEFT JOIN dk_postmeta f1 ON e1.meta_value = f1.post_id
AND f1.meta_key = '_sku'

WHERE post_status = 'wc-processing'
AND
order_item_type = 'line_item'
");

$row = mysqli_fetch_array($result, MYSQLI_ASSOC);

$fp = fopen('file.csv', 'w');

foreach ($row as $val) {
    fputcsv($fp, $val);
}

fclose($fp);

userlond · Accepted Answer · 2015-06-15 04:39:39Z

1

Corresponding to official manual for mysqli_fetch_array:

mysqli_fetch_array — Fetch a result row as an associative, a numeric array, or both

You coded MYSQLI_ASSOC flag, so yo get associative array for one row of data:

By using the MYSQLI_ASSOC constant this function will behave identically to the mysqli_fetch_assoc()

See examples of mysqli_fetch_assoc to clearly understand what I'm talking about.

So to put data to csv row by row, u should organize the loop, smth like this:

<?
// making your query

$fp = fopen('file.csv', 'w');
$placed_header = false;
while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
    // add header to table
    if(!$placed_header) {
        fputcsv($fp, array_keys($row));
        $placed_header = true;
    }

    // place row of data 
    fputcsv($fp, array_values($row));
}

fclose($fp);

edited Jun 15, 2015 at 4:39

answered Jun 15, 2015 at 4:07

userlond

3,8442 gold badges41 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

d1ch0t0my Over a year ago

I tried this and was thrown a load of new errors: Warning: array_keys() expects parameter 1 to be array, null given in /home/example/export/export3.php on line 75 Warning: fputcsv() expects parameter 2 to be array, null given in /home/example/export/export3.php on line 75 Warning: array_values() expects parameter 1 to be array, null given in /home/example/export/export3.php on line 80 Warning: fputcsv() expects parameter 2 to be array, null given in /home/example/export/export3.php on line 80 ...and so on :(.

userlond Over a year ago

Fixed. Replace $val in loop with you actually looped data, i.e. $row.

d1ch0t0my Over a year ago

Perfect. Thanks for your help. I understand the mysqli_fetch_assoc difference as well now so progress in my education is made :). Thanks again.

cazort Over a year ago

I was able to use this code with the output of PDO::query in place of mysqli_fetch_array, using PDOStatement::fetch in the while loop. It worked beautifully with no other modifications and no warnings or errors. Thanks!

Bruce · Accepted Answer · 2015-06-15 04:09:54Z

Export MySQL query array using fputcsv

PHP fputcsv()

$filename = "toy_csv.csv";
$fp = fopen('php://output', 'w');

$query = "$result = mysqli_query($con, "

SELECT d.order_id AS 'Order ID',
m1.meta_value AS 'First Name',
m2.meta_value AS 'Last Name',
m3.meta_value AS 'Address1',
m4.meta_value AS 'Address2',
m5.meta_value AS 'City',
m6.meta_value AS 'State',
m7.meta_value AS 'Post Code',
f1.meta_value AS 'SKU',
d.order_item_name AS 'Product Name',
e2.meta_value AS 'Quantity',
m8.meta_value AS 'Tracking - Carrier',
m9.meta_value AS 'Tracking - Code',
m10.meta_value AS 'Tracking - Date',
m11.meta_value AS 'Tracking - Picked'

FROM dk_posts p
LEFT JOIN dk_postmeta m1 ON p.id = m1.post_id
AND m1.meta_key = '_shipping_first_name'
LEFT JOIN dk_postmeta m2 ON p.id = m2.post_id
AND m2.meta_key = '_shipping_last_name'
LEFT JOIN dk_postmeta m3 ON p.id = m3.post_id
AND m3.meta_key = '_shipping_address_1'
LEFT JOIN dk_postmeta m4 ON p.id = m4.post_id
AND m4.meta_key = '_shipping_address_2'
LEFT JOIN dk_postmeta m5 ON p.id = m5.post_id
AND m5.meta_key = '_shipping_city'
LEFT JOIN dk_postmeta m6 ON p.id = m6.post_id
AND m6.meta_key = '_shipping_state'
LEFT JOIN dk_postmeta m7 ON p.id = m7.post_id
AND m7.meta_key = '_shipping_postcode'
LEFT JOIN dk_postmeta m8 ON p.id = m8.post_id
AND m8.meta_key = 'ywot_carrier_name'
LEFT JOIN dk_postmeta m9 ON p.id = m9.post_id
AND m9.meta_key = 'ywot_tracking_code'
LEFT JOIN dk_postmeta m10 ON p.id = m10.post_id
AND m10.meta_key = 'ywot_pick_up_date'
LEFT JOIN dk_postmeta m11 ON p.id = m11.post_id
AND m11.meta_key = 'ywot_picked_up'

LEFT JOIN dk_woocommerce_order_items d ON d.order_id=m1.post_id

LEFT JOIN dk_woocommerce_order_itemmeta e1 ON d.order_item_id =     e1.order_item_id
AND e1.meta_key = '_product_id'
LEFT JOIN dk_woocommerce_order_itemmeta e2 ON d.order_item_id = e2.order_item_id
AND e2.meta_key = '_qty'

LEFT JOIN dk_postmeta f1 ON e1.meta_value = f1.post_id
AND f1.meta_key = '_sku'

WHERE post_status = 'wc-processing'
AND
order_item_type = 'line_item'
");
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
while ($row = mysql_fetch_row($result)) {
    $header[] = $row[0];
}   
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename='.$filename);
fputcsv($fp, $header);

$num_column = count($header);       
$query = "SELECT * FROM toy";
$result = mysqli_query($query);
while($row = mysqli_fetch_row($result)) {
    fputcsv($fp, $row);
}
exit;
?>

Collectives™ on Stack Overflow

export mysql query array using fputcsv

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related