Magento export mysql result to csv

Question

I am trying to run two mysql queries and export the results into a CSV.

My issue is the second query I have is being written to a new row instead of just onto the same rows that the result from the prior query gets written to.

I also can not join the two queries because the second query I am running might not have a matching row. Below is my code

   //Load Magento
  require_once('app/Mage.php'); //Path to Magento
  umask(0);
  Mage::app();

  //database connection
 $connection = Mage::getSingleton('core/resource')->getConnection('core_read');

$sql = "SELECT sales_flat_order.entity_id, sales_flat_order.created_at, sales_flat_order.increment_id AS OrderID, 
        sales_flat_order.status, sales_flat_order_address.firstname, sales_flat_order_address.lastname, 
        sales_flat_order_address.city, sales_flat_order_address.region, sales_flat_order_address.postcode, 
        sales_flat_order_payment.method AS payment_method, sales_flat_order_payment.base_amount_paid AS pmt, sales_flat_order_payment.base_amount_authorized AS charged, 
        sales_flat_order_payment.base_shipping_captured AS shipping_invoice
        FROM sales_flat_order, sales_flat_order_payment, sales_flat_order_address
        WHERE MONTH(sales_flat_order.created_at) = 3 AND sales_flat_order.status IN('complete','closed') AND 
        sales_flat_order.entity_id = sales_flat_order_payment.entity_id AND sales_flat_order.entity_id = sales_flat_order_address.parent_id";
$list = $connection->fetchAll($sql);  

//$headers = array();
$headers = array("entity_id", "created_at", "OrderID", "Status", "firstName", "LastName", "City", "State", "Zipcode", "Pmt Method", "PMT", "Charged", "Shipping Invoice", "TaxCode", "TaxRate", "Tax Amt");

$fp = fopen('php://output', 'w');
if ($fp && $list) 
{
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="salestax_report.csv"');
    header('Pragma: no-cache');
    header('Expires: 0');
    fputcsv($fp, $headers);
    foreach ($list as $fields) 
    {

        fputcsv($fp, $fields);

        $entity_id = $fields['entity_id'];
        $sqlb = "SELECT code AS taxcode, percent AS taxrate, amount as taxamount FROM sales_order_tax 
                 WHERE order_id = $entity_id";
        $listb = $connection->fetchAll($sqlb); 
        foreach ($listb as $fieldsb) 
         {
           fputcsv($fp, $fieldsb);
         }


    }
    die;
}

Answer 1

Regarding your question: you should put all your fields into the same array before writing to the $fp handle. The reason being that fputcsv() inherently writes complete lines to the file.

fputcsv() formats a line (passed as a fields array) as CSV and write it (terminated by a newline)

However...

You could also do this a lot more efficiently by using JOIN statements to get all your results in one query rather than iterating over the second query. To address your issue of not all records existing across each table, you can just use a LEFT JOIN.

SELECT
  sfo.entity_id,
  sfo.created_at,
  sfo.increment_id AS OrderID, 
  sfo.status,
  sfoa.firstname,
  sfoa.lastname, 
  sfoa.city,
  sfoa.region,
  sfoa.postcode, 
  sfoa.address_type,
  sfop.method AS payment_method,
  sfop.base_amount_paid AS pmt,
  sfop.base_amount_authorized AS charged, 
  sfop.base_shipping_captured AS shipping_invoice,
  sot.code AS tax_code,
  sot.percent AS taxrate,
  sot.amount AS taxamount
FROM
  sales_flat_order sfo
INNER JOIN
  sales_flat_order_payment sfop
    ON sfop.parent_id = sfo.entity_id
INNER JOIN
  sales_flat_order_address sfoa
    ON sfoa.parent_id = sfo.entity_id
LEFT JOIN
  sales_order_tax sot
    ON sot.order_id = sfo.entity_id
WHERE
  MONTH(sfo.created_at) = 3
  AND sfo.status IN('complete','closed')

It was not clear to me whether you needed both the billing and shipping addresses from each order because your record set does not indicate which is which, so I added that column as well, but you could obviously select for just the type you wanted pretty easily.