Mryntu

How do we get a list of order items and their data (name, sku, quantity, price, weight, and dimensions)? Without using Magento's own framework components.

The problem is if the order contains 5 products I would get maybe 7 items from querying the table. This is caused by the different product types (simple, group, voucher, configurable, etc.) and different product ids so cannot be combined.

$items = array();

$sql = (
 "SELECT oi.name, oi.sku, oi.qty_ordered, oi.price, oi.weight
 FROM $db->prefixsales_flat_order_item 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'],
 //'unit_length' => (float)$row['length'], // Not present?
 //'unit_width' => (float)$row['width'], // Not present?
 //'unit_height' => (float)$row['height'], // Not present?
 ];
 

 $result->close();

If I pass a filter AND product_type = 'simple' to get only the simple products, they do not contain price.

how about with this query, I try joined your query with catalog_product_entity and catalog_product_index_price tables, so you will get all products with contain price :

SELECT DISTINCT oi.name, oi.sku, oi.qty_ordered, oi.weight, 
`idx_price`.`price`
FROM sales_flat_order_item AS oi 
INNER JOIN `catalog_product_entity` AS `cpe` ON oi.sku = cpe.sku 
INNER JOIN `catalog_product_index_price` AS `idx_price` ON idx_price.entity_id = cpe.entity_id 
 WHERE oi.order_id = <order_id>

then you can add the product_type filtering on the end query like this :

AND oi.product_type = 'configurable'

or

AND oi.product_type = 'simple'

hope this can help

If you have the configuration setting for Catalog->Frontend->Use Flat Catalog Product set to ON, you can join the sales_flat_order_item table to catalog_product_flat_[#]. If you only have one (catalog_product_flat_1), that is fairly easy, but it can still be done with more if you use a Union

For one flat product table:

SELECT 
 oi.name, 
 oi.sku, 
 oi.qty_ordered, 
 oi.price, 
 oi.weight, 
 flat.width, 
 flat.height, 
 flat.length
FROM sales_flat_order_item oi
join catalog_product_flat_1 flat
on oi.product_id = flat.entity_id
WHERE oi.order_id = <order_id>;

For multiple flat product tables:

SELECT 
 oi.name, 
 oi.sku, 
 oi.qty_ordered, 
 oi.price, 
 oi.weight, 
 flat.width, 
 flat.height, 
 flat.length
FROM sales_flat_order_item oi
JOIN (
 select entity_id, width, height, length from catalog_product_flat_1
UNION DISTINCT
 select entity_id, width, height, length from catalog_product_flat_2
/*
REPEAT 2 LINES ABOVE FOR catalog_product_flat_3, catalog_product_flat_4, etc
*/
) flat
on oi.product_id = flat.entity_id
WHERE oi.order_id = <order_id>;

EDIT

This will accomplish the same goal regardless of whether you are using flat product tables:

SELECT 
 oi.name, 
 oi.sku, 
 oi.qty_ordered, 
 oi.price, 
 oi.weight,
 w.value 'width',
 h.value 'height',
 l.value 'length'
FROM sales_flat_order_item oi
JOIN catalog_product_entity_decimal w
ON oi.product_id = w.entity_id
JOIN catalog_product_entity_decimal h
ON oi.product_id = h.entity_id
JOIN catalog_product_entity_decimal l
ON oi.product_id = l.entity_id
WHERE oi.order_id = <order_id>
AND w.attribute_id = (
 select attribute_id from eav_attribute 
 where entity_type_id = 4 
 and attribute_code = 'width'
)
AND h.attribute_id = (
 select attribute_id from eav_attribute 
 where entity_type_id = 4 
 and attribute_code = 'height'
)
AND l.attribute_id = (
 select attribute_id from eav_attribute 
 where entity_type_id = 4 
 and attribute_code = 'length'
);

 SELECT 
 CONCAT(address.firstname,' ',
 address.lastname) AS Name,
 address.email AS Email,
 items.created_at AS Date,
 items.name AS Description,
 items.store_id AS Logon,
 items.name AS Category,
 items.store_id AS FeedbackDate, 
 items.sku AS ProductSearchcode,
 items.order_id AS Orderref 
 FROM sales_flat_order AS orders
 JOIN sales_flat_order_item AS items 
 ON items.order_id = orders.entity_id 
 LEFT JOIN sales_flat_order_address AS address
 ON orders.entity_id = address.parent_id
WHERE 
 items .created_at BETWEEN '2014-04-15 00:00:00' AND '2014-07-30 
 00:00:00' AND orders.status = 'complete'

Also you can add AND orders.status in ('processing', 'complete') depending upon your requirement.

its simple, use group by will give you your results:

 "SELECT oi.name, oi.sku, oi.qty_ordered, oi.price, oi.weight
 FROM sales_flat_order_item oi
 WHERE oi.order_id = ". (int)$order_entity_id . " group by sku ;"

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++