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
– Dan Bracuk, Commented Dec 10, 2013 at 20:32
It has one, but it made it a bit complicated so I left it out. — Tyler Wall
– Tyler Wall, Commented Dec 10, 2013 at 22:03

Mihai · Accepted Answer · 2013-12-10 22:12:51Z

1

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.

edited Dec 10, 2013 at 22:12

answered Dec 10, 2013 at 20:30

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tyler Wall Over a year ago

I forgot to add the 2nd part of the question: adding order_items_info to the items array?

Mihai Over a year ago

I dont see anything order_items_info anywhere in your tables.

Tyler Wall Over a year ago

Sorry, it was a bit confusing. I've updated it to make a little more sense.

gbejic · Accepted Answer · 2013-12-10 20:32:09Z

0

select * from orders join order_items on (orders.id = order_id)

answered Dec 10, 2013 at 20:32

gbejic

3021 silver badge5 bronze badges

Collectives™ on Stack Overflow

MySQL Query - produce array within result

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related