I am using codeigniter. mysql is the database. I have a query as shown below and it works perfectly.
$this->db->select(
"orders.book_id,products.product_id,
products.price,orders.user_id,
products.parent_id,orders.booked_date
"
);
$this->db->from("orders");
$this->db->join('order_details', 'orders.book_id = order_details.book_id');
$this->db->join('products', 'order_details.product_id = products.product_id');
I need to format the result and i know that can be done from PHP using some loop. But i have to do it using mysql for better performance.
I need to get a multi dimensional array with parent key as the key. Within that another set of arrays with product key as the key. The sample array i wish to get is written below. Is it possible to get the response in that format without looping through the result? Preferably using mysql itself?
Array
(
//5 is parent id
[5] => Array (
//10 is product id
[10] => Array(
[0] => stdClass Object
(
[book_id] => 345
[product_id] => 10
[price] => 12
[user_id] => 0
[parent_id] => 5
[booked_date] => 02-08-2016
)
)
[7] => Array(
[0] => stdClass Object
(
[book_id] => 343
[product_id] => 7
[price] => 12
[user_id] => 34
[parent_id] => 5
[booked_date] => 02-08-2016
)
)
)
[1] => Array (
[3] => Array(
(
[book_id] => 341
[product_id] => 3
[price] => 11
[user_id] => 34
[parent_id] => 1
[booked_date] => 02-08-2016
)
)
[11] => Array(
[0] => stdClass Object
(
[book_id] => 343
[product_id] => 11
[price] => 12
[user_id] => 34
[parent_id] => 1
[booked_date] => 02-08-2016
)
[1] => stdClass Object
(
[book_id] => 335
[product_id] => 11
[price] => 11
[user_id] => 34
[parent_id] => 1
[booked_date] => 02-08-2016
)
)
)
)