MySQL Query - produce array within result

Question

I would like to, in one query select orders and have their items attached to them (right now I'm selecting the orders, then using a separate query selecting the order_items - this is proving very slow on a large amount of orders...

orders: id | name | total

order_items: id | order_id | price | qty

order_items_info: id | order_id | order_item_id | tracking_no

The last thing I want to do is: add my order_items_info table to the item array.

$orders = array(
    array(
        'id' => '',
        'name' => '',
        'items' => array(
            array(
                'order_item_id' => '',
                'price' => '',
                'qty' => '',
                'item_info' => array()
            ),
            array(
                'order_item_id' => '',
                'price' => '',
                'qty' => '',
                'item_info' => array()
            ),
            ...
        )
    )
);

Your order_items table needs an item_id field that joins to your item table. — Dan Bracuk
It has one, but it made it a bit complicated so I left it out. — Wallter

Mihai Mihai · Accepted Answer · 2013-12-10T20:30:46

SELECT o.id,name,total,item_info,price,qty FROM orders o 
JOIN order_items oi ON o.id=oi.order_id
JOIN order_items_info oii ON oii.order_id=o.id
AND oii.order_item_id=oi.id

Just a wild guess until you post your table info.

MySQL Query - produce array within result

2 Answers