Codeigniter: create multidimensional array of result

Question

I am trying to get multidimensional array that is grouped by product.

Here is sql that I have tried:

$sql = "SELECT * from order_detail OD\n"
    . "LEFT JOIN product as pro ON pro.product_id = OD.product_id\n"
    . " LEFT JOIN `order` as o ON o.order_id = OD.order_id \n"
    . "WHERE OD.order_id IN (1,4) AND OD.user_id =2\n"
    . "group by OD.product_id\n"
    . "order By OD.Product_ID, OD.Order_ID";

This sql gives following result :

Array
(
    [0] => Array
        (
            [order_detail_id] => 1
            [user_id] => 2
            [order_id] => 1
            [product_id] => 13
            [product_name] => Prodict Test
        )

    [1] => Array
        (
            [order_detail_id] => 2
            [user_id] => 2
            [order_id] => 1
            [product_id] => 12
            [product_name] => Product One
        )

    [2] => Array
        (
            [order_detail_id] => 3
            [user_id] => 2
            [order_id] => 1
            [product_id] => 11
            [product_name] => Fodod
        )

    [3] => Array
        (
            [order_detail_id] => 34
            [user_id] => 2
            [order_id] => 4
            [product_id] => 13
            [product_name] => Prodict Test
        )

    [4] => Array
        (
            [order_detail_id] => 35
            [user_id] => 2
            [order_id] => 4
            [product_id] => 12
            [product_name] => Product One1
        )

    [5] => Array
        (
            [order_detail_id] => 36
            [user_id] => 2
            [order_id] => 4
            [product_id] => 11
            [product_name] => Fodod
        )

)

And This is strecture I want:

Array
        (
            [0] => Array
                (

                    [product_id] => 13
                    [product_name] => Prodict Test
                        ['order'] => Array
                            (
                                [0] => Array
                                        (
                                            [order_detail_id] => 1
                                            [user_id] => 2
                                            [order_id] => 1
                                        )
                                [1] => Array
                                        (
                                            [order_detail_id] => 34
                                            [user_id] => 2
                                            [order_id] => 4
                                        )
                            )
                )

            [1] => Array
                    (
                        [product_id] => 12
                        [product_name] => Product One
                            ['order'] => Array
                            (
                                [0] => Array(
                                                [order_detail_id] => 2
                                                [user_id] => 2
                                                [order_id] => 1
                                            )
                                [0] => Array(
                                                [order_detail_id] => 35
                                                [user_id] => 2
                                                [order_id] => 4
                                            )
                            )
                    )

        )

Can anyone suggest me how I can get it done?

Here is my table strecture

Order Table

+----------+---------+------------+
| order_id | user_id | invoice_no |
+----------+---------+------------+
| 1        | 2       | INV-1      |
| 2        | 3       | INV-2      |
| 3        | 5       | INV-3      |
| 4        | 2       | INV-4      |
| 5        | 5       | INV-5      |
| 6        | 3       | INV-6      |
+----------+---------+------------+

order_detail Table

+-----------------+---------+----------+------------+
| order_detail_id | user_id | order_id | product_id |
+-----------------+---------+----------+------------+
|               1 |       2 |        1 |         13 |
|               2 |       2 |        1 |         12 |
|               3 |       2 |        1 |         11 |
|               4 |       2 |        1 |          9 |
|               5 |       2 |        1 |          8 |
|               6 |       2 |        1 |          7 |
|               7 |       2 |        1 |          6 |
|              34 |       2 |        4 |         13 |
|              35 |       2 |        4 |         12 |
|              36 |       2 |        4 |         11 |
|              37 |       2 |        4 |          9 |
+-----------------+---------+----------+------------+

prodcut table

+------------+---------------+
| product_id | product_name  |
+------------+---------------+
|          1 | Product One   |
|          2 | Product Two   |
|          3 | Product Three |
|          4 | Product Four  |
|          5 | Product Five  |
|          6 | Product Six   |
|          7 | Fodod         |
|          8 | Pepsi         |
|          9 | Product Onea  |
|         10 | Fodod         |
|         11 | Fodod         |
|         12 | Product One1  |
|         13 | Prodict Test  |
+------------+---------------+

Note : I have tried both of following solution form stackoverflow question, but didn't work form me

Solution 1

Solution 2

Why use a generic sql? CI has MVC support I think, did you use (related) models for your tables? — user169600
– user169600, Commented Mar 19, 2015 at 7:38

Sudhir · Accepted Answer · 2015-03-19 07:32:35Z

0

Make a loop association using left join. Make inner loop & join. Manage your query like this

$this->db->select('*');
$this->db->from('exp_incoming_order');
$this->db->order_by('id','desc');
$this->db->where('member_id',12);
$query1 = $this->->db->get();
$data=array();
foreach($query1->result_array() as $key_1=>$result) 
    {
    foreach($result as $key=>$value1) 
    {
        $data[$key_1][$t1][$key]=$value1;
    }
}
foreach($data as $single=>$part) {
    foreach($tables as $table=>$value2) {
        $this->db->select('*');
        $this->db->from('order_detail');
        $this->db->where('order_id',$part['exp_incoming_order']['order_id']);
        $query2 = $this->db->get();
        foreach($query2->result_array() as $key2=>$result2) 
        {
            foreach($result2 as $key3=>$value3) 
            {
                $data[$single][$table][$key2][$key3]=$value3;
            }
        }
    }
}

answered Mar 19, 2015 at 7:32

Sudhir

83312 silver badges33 bronze badges

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

1 Comment

Sudhir Over a year ago

$t1 is first table name

Collectives™ on Stack Overflow

Codeigniter: create multidimensional array of result

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related