Exporting order details magento

Exporting order details magento

July 2, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Exporting order details magento

Exporting Magento Order Details: A Comprehensive Guide

Exporting order information from your Magento system is a crucial step when migrating to a new platform or performing data analysis. In this blog post, we’ll guide you through the process of exporting the specific fields you need for each order.

Understanding Your Requirements

You’ve specified a list of 26 fields that you want to export for every order in your Magento system:

  • order_id
  • sku
  • item_quantity
  • item_price
  • order_total_amount
  • created_at
  • Billing address details
  • Shipping address details
  • Order status

This information is essential for preserving the history and context of each transaction during a migration or analysis.

Refining Your SQL Query

You’ve already started with an SQL query, which is a great start. Let’s refine this to ensure it retrieves all the necessary data:

SELECT sfo.entity_id AS `order_id`, sfo.status AS `order_status`,
sfo.customer_email,
oi.product_sku AS `sku`,
oi.sku,
oi.name,
oi.quantity_ordered AS `item_quantity`,
oi.price_incl_tax AS `item_price`,
oi.row_total_incl_tax AS `item_total_amount`,
sfo.grand_total AS `order_total_amount`,
billing.firstname AS `billing_first_name`,
billing.lastname AS `billing_last_name`,
CONCAT(billing.street, ', ', billing.city) AS `billing_address`,
shipping.firstname AS `shipping_first_name`,
shipping.lastname AS `shipping_last_name`,
CONCAT(shipping.street, ', ', shipping.city) AS `shipping_address`
FROM sales_flat_order AS sfo
JOIN sales_flat_order_address AS billing ON billing.parent_id=sfo.entity_id AND billing.address_type='billing'
JOIN sales_flat_order_address AS shipping ON shipping.parent_id=sfo.entity_id AND shipping.address_type='shipping'
JOIN sales_flat_order_item as oi ON oi.order_id=sfo.entity_id

This query joins the necessary tables and selects all required fields. It uses aliases to match your specified column names.

Handling Multiple Items per Order

To handle cases where one order includes multiple items, we need to adjust our approach slightly. The sales_flat_order_item table stores each item as a separate row, linked back to the parent order by the order_id field.

Our current query already handles this correctly; it will return one row per item within an order, with all other details (billing and shipping info) repeated for every item in that order. This structure is often referred to as a “wide” or “denormalized” format.

Exporting to a CSV File

Once you have the SQL query working, the next step is to export this data into a CSV file. There are several ways to do this, depending on your technical environment:

  1. Using MySQL’s built-in CSV functionality: You can create a temporary table containing a copy of your results and then save it as a CSV file using the SELECT ... INTO OUTFILE statement.

  2. Exporting from PHPMyAdmin: If you’re using the PHPMyAdmin web interface, you can export the data directly to a CSV file with a single click after running your query.

  3. Writing a custom script in PHP or another language: You can write a script that runs the SQL query and writes the results to a file line by line.

Best Practices for Large Exports

If you have a large number of orders (thousands or millions), consider these best practices:

  • Paginate your results: Break down the export into smaller chunks based on date range or order ID range.
  • Use memory-efficient data types: If possible, use data types that require less memory in your result set to reduce the load on your server.
  • Optimize your database: Ensure your Magento installation is well-indexed and optimized for good query performance.

Conclusion

Exporting Magento order details requires a careful construction of an SQL query to ensure all necessary fields are included. By refining your initial query, you can retrieve accurate data in a format suitable for your migration or analysis needs.

Remember that the export process may take time if you have a large number of orders. Plan your export strategy carefully and test it thoroughly before performing the final export on your live system.

For more advanced Magento development topics, including migrations and data manipulation, consider exploring our resources at Laravel Company. We specialize in helping businesses optimize their e-commerce platforms using Laravel and other modern technologies.

Happy exporting!

Catalog Database Email Orders

Our network