3

I want to run a sql query in a PHP file and save the result in a csv file (inkl. header). Unfortunately I don't get it done. It looks like I can't use mysql_* on the result. Does anyone know how to fix it?

$mageFilename = 'app/Mage.php';
require_once $mageFilename;
// Mage::setIsDeveloperMode(true);
 ini_set('display_errors', 1);
umask(0);
Mage::app();

$sql = "SELECT * from sales_flat_order"; 

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

$result = $connection->fetchAll($sql); //fetchRow($sql), fetchOne($sql),...

if (!$result) die('Couldn\'t fetch records');
$num_fields = mysql_num_fields($result);
$headers = array();
for ($i = 0; $i < $num_fields; $i++) {
    $headers[] = mysql_field_name($result , $i);
}
$fp = fopen('php://output', 'w');
if ($fp && $result) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="export.csv"');
    header('Pragma: no-cache');
    header('Expires: 0');
    fputcsv($fp, $headers);
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
        fputcsv($fp, array_values($row));
    }
    die;
}
2

Try this:

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

$list = $connection->fetchAll($sql); //fetchRow($sql), fetchOne($sql),...

$headers = array();

foreach(array_keys($list[0]) as $key){
     $headers[] = $key;
}

$fp = fopen('php://output', 'w');
if ($fp && $list) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="export.csv"');
    header('Pragma: no-cache');
    header('Expires: 0');
    fputcsv($fp, $headers);
    foreach ($list as $fields) {
        fputcsv($fp, $fields);
    }
    die;
}
  • This works like a charm. Thanks! Since this matches my question the most I'm gonna accept this as answer. I do understand (now) that you should work with collections instead direct sql queries. But my query is huge and just a select. This works better for me (at the moment) since I'm new to Magento but I'm gonna try to use collections in the future. Thanks to everyone for helping me here. Unfortunately I can't give +1 since my reputation is to low – Xavin Mar 8 '16 at 13:08
2

If you have a look at the admin controller Mage_Adminhtml_Sales_OrderController you will see the action exportCsvAction which is how the Magento core creates an order csv export.

public function exportCsvAction()
{
    $fileName   = 'orders.csv';
    $grid       = $this->getLayout()->createBlock('adminhtml/sales_order_grid');
    $this->_prepareDownloadResponse($fileName, $grid->getCsvFile());
}

If you look at the file Mage_Adminhtml_Block_Widget_Grid you will see the getCsvFile function. It might be possible to build your code to extend this block or simply mirror the functionality to get the csv export working in the same way as the admin.

1

you should use collections instead of running queries.

$mageFilename = 'app/Mage.php';
require_once $mageFilename;
// Mage::setIsDeveloperMode(true);
 ini_set('display_errors', 1);
umask(0);
Mage::app();

$collection = Mage::getModel('sales/order')->getCollection();

$firstItem = $collection->getFistItem();
if (!$firstItem->getId()) {
    echo "Nothing to export";
    die;
}

$headers = array_keys($firstItem->getData());

$fp = fopen('php://output', 'w');
if ($fp) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="export.csv"');
    header('Pragma: no-cache');
    header('Expires: 0');
    fputcsv($fp, $headers);
    foreach ($collection as $order) {
        fputcsv($fp, array_values($order->getData()));
    }
    die;
}

I haven't tested the code so look out for hidden syntax errors.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.