magento orders list query

Question

I want to select the list of all orders in Magento.

This is required for me to show the list of all the orders from magento in another PHP application presently I'm working on.

Also can some one write me the code using the Magento conventions such as Mage::

Im using Magento 1.4.2 version.

Thanks,
Mark

Answer 1

Try this:

select * from sales_flat_order;

That's header-level information. If you require line-item information / deeper information, you might use something like the following join:

select e.*,sfoi.* from sales_flat_order e
left join sales_flat_order_item sfoi on (e.entity_id=sfoi.order_id)

Now, this is going to duplicate (cartesian product) all of the header information along with the line-item information. If you require something further, let me know, I'm a master at Magento EAV SQL :)

Answer 2

A bit late, but this might be useful (tested in Magento CE 1.7.0.2). The code is heavily commented for guidance.

<?php
// include the core code we are going to use
require_once('app/Mage.php');
umask (0);
Mage::app('default');

// resources
$resource = Mage::getSingleton('core/resource');

// db access
$db_read = $resource->getConnection('core_read');
$db_write = $resource->getConnection('core_write');

// support table prefix if one is being used
$table_prefix = Mage::getConfig()->getTablePrefix();

// count the orders
$order_num = $db_read->fetchOne("SELECT COUNT(*) AS num FROM {$table_prefix}sales_flat_order WHERE status = 'pending'");

// get an array of the orders
$orders = $db_read->fetchAll("SELECT sales.* FROM {$table_prefix}sales_flat_order AS sales WHERE sales.status = 'pending'");

// start iterating through the orders
for($i=0; $i < intval($order_num); $i++) {

    // order id
    $orderid = $orders[$i]['entity_id'];

    // shipping address
    $order_details = Mage::getModel('sales/order')->load($orderid);
    $shippingAddress = $order_details->getShippingAddress();

    // use like so
    $shippingAddress->getPrefix());
    $shippingAddress->getFirstname();
    $shippingAddress->getLastname();
    $shippingAddress->getCompany();
    $shippingAddress->getEmail();
    $shippingAddress->getTelephone();
    $shippingAddress->getStreetFull();

    // billing address
    $order_details = Mage::getModel('sales/order')->load($orderid);
    $billingAddress = $order_details->getBillingAddress();

    // use like so
    $billingAddress->getPrefix());
    $billingAddress->getFirstname();
    $billingAddress->getLastname();
    $billingAddress->getCompany();
    $billingAddress->getEmail();
    $billingAddress->getTelephone();
    $billingAddress->getStreetFull();

    // and if you want order items, do the following
    $items = $db_read->fetchAll("SELECT 
        items.order_id AS orderid,
        items.item_id AS itemid,
        orders.total_item_count AS total_items_in_order,
        items.quote_item_id AS quoteid,
        items.created_at AS orderdate,
        items.product_type,
        items.sku AS itemcode,
        items.name AS itemname,
        items.price_incl_tax AS itemprice,
        items.tax_amount AS itemtax,
        items.discount_amount AS discount,
        items.qty_ordered AS qty_ordered,
        items.qty_shipped AS qty_shipped,
        address.email AS email,
        address.prefix AS title,
        address.firstname AS firstname,
        address.lastname AS lastname,
        address.street AS address,
        address.city AS city,
        address.region AS region,
        address.country_id AS country,
        address.postcode AS postcode,
        address.telephone AS telephone
      FROM {$table_prefix}sales_flat_order AS orders 
        JOIN {$table_prefix}sales_flat_order_item AS items 
          ON items.order_id = orders.entity_id 
        LEFT JOIN {$table_prefix}sales_flat_order_address AS address
          ON orders.entity_id = address.parent_id 
      WHERE 
        items.order_id = $orderid 
        AND address.address_type = 'shipping'
        AND orders.status = 'pending'
    ");

    foreach ($items AS $item) {
        echo $item['itemid'];
        // blah blah blah
    }
}
?>

Hope that helps someone!

Answer 3

This code uses the "Magento way" and accesses the data through the Model layer which insulates you from changes in the table structure (e.g. flat vs EAV). Create a new PHP file containing this skeleton code in the root of your Magento install (if elsewhere update the path for the first require statement).

This gives you some examples of how to add attributes to the collection, you should be able to follow the examples to add more if required. It shows you how to filter by attributes, and sort by attributes. Examples also for echo'ing out the fields that you need.

HTH,
JD

require_once 'app/Mage.php';
umask(0);
Mage::app('default');
    $orders = Mage::getResourceModel('sales/order_collection')
        ->addAttributeToSelect('*')
        ->joinAttribute('billing_firstname', 'order_address/firstname', 'billing_address_id', null, 'left')
        ->joinAttribute('billing_lastname', 'order_address/lastname', 'billing_address_id', null, 'left')
        ->joinAttribute('billing_street', 'order_address/street', 'billing_address_id', null, 'left')
        ->joinAttribute('billing_company', 'order_address/company', 'billing_address_id', null, 'left')
        ->joinAttribute('billing_city', 'order_address/city', 'billing_address_id', null, 'left')
        ->joinAttribute('billing_region', 'order_address/region', 'billing_address_id', null, 'left')
        ->joinAttribute('billing_country', 'order_address/country_id', 'billing_address_id', null, 'left')
        ->joinAttribute('billing_postcode', 'order_address/postcode', 'billing_address_id', null, 'left')
        ->joinAttribute('billing_telephone', 'order_address/telephone', 'billing_address_id', null, 'left')
        ->joinAttribute('billing_fax', 'order_address/fax', 'billing_address_id', null, 'left')
        ->joinAttribute('shipping_firstname', 'order_address/firstname', 'shipping_address_id', null, 'left')
        ->joinAttribute('shipping_lastname', 'order_address/lastname', 'shipping_address_id', null, 'left')
        ->joinAttribute('shipping_street', 'order_address/street', 'shipping_address_id', null, 'left')
        ->joinAttribute('shipping_company', 'order_address/company', 'shipping_address_id', null, 'left')
        ->joinAttribute('shipping_city', 'order_address/city', 'shipping_address_id', null, 'left')
        ->joinAttribute('shipping_region', 'order_address/region', 'shipping_address_id', null, 'left')
        ->joinAttribute('shipping_country', 'order_address/country_id', 'shipping_address_id', null, 'left')
        ->joinAttribute('shipping_postcode', 'order_address/postcode', 'shipping_address_id', null, 'left')
        ->joinAttribute('shipping_telephone', 'order_address/telephone', 'shipping_address_id', null, 'left')
        ->joinAttribute('shipping_fax', 'order_address/fax', 'shipping_address_id', null, 'left')

        ->addFieldToFilter('status', array("in" => array(
            'complete',
            'closed')
            ))

        ->addAttributeToFilter('store_id', Mage::app()->getStore()->getId())
        ->addAttributeToSort('created_at', 'asc')
        ->load();
  foreach($orders as $order):
    echo $order->getIncrementId().'<br/>';
    echo $order->getShippingTelephone().'<br/>';
  endforeach;