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);

Answer 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);

Answer 2

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;
?>