SQL to get order items from Magento?

SQL to get order items from Magento?

July 30, 2026 · Illustrated author avatarBy Stefan Bogdanescu
SQL to get order items from Magento?

Handling Ordered Physical Items in Magento via SQL

When dealing with e-commerce platforms like Magento, it’s common to require direct access to order data for shipping purposes or integration with external systems. This often involves querying the database to retrieve specific information about ordered items without relying on the core framework components.

The Challenge: Multiple Product Types and IDs

The primary challenge lies in handling products that contain options, as they appear multiple times in the sales_flat_order_item table under different product types (simple, bundle, configurable, etc.) with varying product IDs. This multiplicity can lead to confusion when attempting to extract a unified list of items for shipping.

Retrieving Order Items via Direct SQL Query

Let’s examine how to fetch ordered physical items using an SQL query that retrieves the required fields: name, SKU, quantity ordered, price, weight, and dimensions (if available).

$items = [];

$sql = "
SELECT oi.name,
       oi.sku,
       oi.qty_ordered,
       oi.price,
       oi.weight
FROM {$db->prefix}sales_flat_order_item AS oi
WHERE oi.order_id = ". (int)$order_entity_id ."
";

if ($result = $mysqli->query($sql)) {

  while ($row = $result->fetch_assoc()) {

    $items[] = [
      'name'                   => $row['name'],
      'sku'                    => $row['sku'],
      'quantity'               => (float)$row['qty_ordered'],
      'unit_price'             => (float)$row['price'],
      'unit_weight'            => (float)$row['weight'],
    ];
  }

  $result->close();
}

Addressing the Price Issue with Simple Products

If you filter the query to include only simple products by adding AND product_type = 'simple', you might encounter an issue where the price is not present in the resulting records. This discrepancy arises because the Magento framework calculates prices dynamically based on the selected options for non-simple products.

Best Practices and Further Enhancements

To ensure a smoother process, consider the following best practices:

  1. Caching: If you perform frequent database queries, implement caching mechanisms (like Memcached or Redis) to reduce the load on your database server.

  2. Performance Optimization: Optimize your SQL queries by indexing relevant columns in the sales_flat_order_item table to improve query performance.

For an in-depth guide on optimizing Magento’s performance and understanding its database schema, I recommend checking out our comprehensive resource on Magento Database Optimization.

Conclusion

Retrieving order items from the Magento database via SQL queries provides a flexible approach when direct access to ordered physical products is required. By carefully crafting your queries and considering performance optimizations, you can efficiently extract the necessary information for shipping or integration purposes.

Remember that while this method offers direct control, it also carries responsibilities such as maintaining data consistency and handling potential changes in Magento’s database schema across versions.

In cases where a more integrated approach is desired, it may be beneficial to explore Magento’s APIs (like SOAP or REST) or its PHP framework for a balance between flexibility and ease of use.

Catalog Database Configuration Orders

Our network