01-07-2010, 03:12 PM
[eluser]underzen[/eluser]
My brain is fried and I'm having a problem. I have 2 tables: orders and orders_items. The orders table contains the following fields: id, order_date. The orders_items table contains the following fields: id, order_id, product_id. What I want to do is for every id in the orders table join it with order_id in the orders_items table.
The query looks like this:
The code looks like this:
The output that I get is this:
The result I am trying to get is for 2 orders to be returned with the multiple associated product_id's from the orders_details table. So for order id 84 i want return the two product_id's from the orders_details table of 1 & 3. See below for an idea of the result I'm looking for.
My brain is so fried I'm not sure if this post will make sense to you all.
Thanks so much!!!!!
Eric
My brain is fried and I'm having a problem. I have 2 tables: orders and orders_items. The orders table contains the following fields: id, order_date. The orders_items table contains the following fields: id, order_id, product_id. What I want to do is for every id in the orders table join it with order_id in the orders_items table.
The query looks like this:
Code:
SELECT `orders`.`id`, `orders`.`order_date`, `orders_items`.`order_id`, `orders_items`.`product_id`
FROM (`orders`)
JOIN `orders_items` ON `orders_items`.`order_id` = `orders`.`id`
WHERE `orders`.`customer_id` = '19'
The code looks like this:
Code:
$query = $this->db->select('orders.id, orders.order_date, orders_items.order_id, orders_items.product_id')->from('orders')->join('orders_items','orders_items.order_id = orders.id' ,'')->where('orders.customer_id' , 19)->get();
The output that I get is this:
Code:
Array
(
[0] => Array
(
[id] => 84
[order_date] => 2010-01-04 15:07:00
[order_id] => 84
[product_id] => 1
)
[1] => Array
(
[id] => 84
[order_date] => 2010-01-04 15:07:00
[order_id] => 84
[product_id] => 3
)
[2] => Array
(
[id] => 85
[order_date] => 2010-01-07 13:24:02
[order_id] => 85
[product_id] => 1
)
)
The result I am trying to get is for 2 orders to be returned with the multiple associated product_id's from the orders_details table. So for order id 84 i want return the two product_id's from the orders_details table of 1 & 3. See below for an idea of the result I'm looking for.
Code:
Array
(
[0] => Array
(
[id] => 84
[order_date] => 2010-01-04 15:07:00
[order_id] => 84
[product_id] => 1
[product_id] => 3
)
[1] => Array
(
[id] => 85
[order_date] => 2010-01-07 13:24:02
[order_id] => 85
[product_id] => 1
)
)
My brain is so fried I'm not sure if this post will make sense to you all.
Thanks so much!!!!!
Eric