Codeigniter:query results into associative array

Question

I have two tables in database

1.main_category fields (id,main_name);

2.sub_category fields(id,main_id, sub_name)

Here main_id is used for connecting two tables from this i want to get the result like the following array

Array
(
    [CCTV] => Array
        (
            [0] => Array
                (
                    [id] => 1
                    [main_id] => 4
                    [name] => first
                )

            [1] => Array
                (
                    [id] => 3
                    [main_id] => 4
                    [name] => second
                )

            [2] => Array
                (
                    [id] => 4
                    [main_id] => 4
                    [name] => second
                )

        )

    [Security Camera] => Array
        (
            [0] => Array
                (
                    [id] => 5
                    [main_id] => 5
                    [name] => first
                )

            [1] => Array
                (
                    [id] => 6
                    [main_id] => 5
                    [name] => second
                )

            [2] => Array
                (
                    [id] => 7
                    [main_id] => 5
                    [name] => second
                )

        )

)

Here the key of the array are main_name field which is from the main_category table and the associative array for each key contains the rows which matches the condition

where main_category.id=sub_category.main_id

I want db query to achieve the above result.is it possible with a join query?

@Vigneswaran i want the results array as my question whetehr it is achived by joinquery or other method — Blessan Kurien
– Blessan Kurien, Commented Mar 18, 2016 at 4:44
Yes it can be achieved. it is simple only you have found the result already "where main_category.id=sub_category.main_id". this is the main condition . any how i will help you . — Vigneswaran S
– Vigneswaran S, Commented Mar 18, 2016 at 4:48

NomanJaved · Accepted Answer · 2016-03-18 04:37:50Z

1

This is the structure of the join query you can change it in your own requirement.

function myfun($id){
  $query = "select main_cat.*, sub_cat.* from main_category main_cat
  Join sub_category sub_cat 
  ON main_cat.id = sub_cat.main_id
  where main_cat.id = $id";
  $data = $this->db->query($query);
  return $data->result_array();
}

Hope this will help to you.

answered Mar 18, 2016 at 4:37

NomanJaved

1,41020 silver badges39 bronze badges

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

Comments

Vigneswaran S · Accepted Answer · 2016-03-18 05:04:13Z

0

try to understand the query and make alteration as required.main_cat as table 1 and sub_category as table two . already you have gave half solution sub_category.employee_id = main_cat.employee_id

  $this->db->select("main_cat.id,main_cat.main_id,main_cat.name,sub_category.id,sub_category.main_id,sub_category.sub_name");
  $this->db->from('main_cat');
  $this->db->join('sub_category', 'sub_category.employee_id = main_cat.employee_id');
  $this->db->where('id', $id);
  $query = $this->db->get();
  return $query->result();

edited Mar 18, 2016 at 5:04

answered Mar 18, 2016 at 4:58

Vigneswaran S

2,0941 gold badge22 silver badges33 bronze badges

3 Comments

Vigneswaran S Over a year ago

@BlessanKurien: its ok. Have your problem got solved or not ?

Blessan Kurien Over a year ago

Nop:) i want to modify the result with array my array structure .Please look at my array structyre

Vigneswaran S Over a year ago

can you say the resulting value of the return array ? ie: print_r();

Divyesh · Accepted Answer · 2016-03-18 05:26:04Z

0

Hello You use codeignitor so use codeignitor mysql query structure it's best practise in future

$this->db->select("main_cat.id,main_cat.main_id,main_cat.name,sub_category.id,sub_category.main_id,sub_category.sub_name");
  $this->db->from('main_cat');
  $this->db->join_using('sub_category', 'employee_id');
  $this->db->where(tablename.'id', $id);
  $query = $this->db->get();
  return $query->result();

employee_id is common in both table so use join_using other wise you use join like this

$this->db->join('sub_category', 'sub_category.employee_id = main_cat.employee_id');

answered Mar 18, 2016 at 5:26

Divyesh

3892 silver badges12 bronze badges

Collectives™ on Stack Overflow

Codeigniter:query results into associative array

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related