在multiselect下拉类别和子类别中打印数组

Question

I have set of records which contains 10 records in the parent table and 500 sub-records in the subtable

         Table details
        id    name    active 
         1      A        1
         2      B        1
         3      C        1
         4      D        1
         5      E        1
        ....

         Table sub_details

         sid    sub_name  parent_id  active 
         1      a1       1          1
         2      a2       1          1
         3      a3       1          1
         4      a4       1          1
         5      a5       1          1
         6      b1       2          1
         7      b2       2          1
         8      c1       3          1
         9      c2       3          1
         10     c3       3          1
         11      d1      4          1
         12      e2      5          1
         13      e3      5          1
         14      e4      5          1
         15      e5      5          1
        ....

I am getting an array in the below format

    [0] => stdClass Object
    (
        [id] => 1
        [sid] => 1
        [sub_name] => a2
        [name] => A
    )

     [1] => stdClass Object
    (
        [id] => 1
        [sid] => 2
        [sub_name] => a2
        [name] => A
    )...

While printing in the front end I need to display in the drop-down category_wise

        <select>
        <optgroup label="A">
            <option value="1">a1<option>
            <option value="2">a2<option>
            <option value="3">a3<option>
            <option value="4">a4<option>
            <option value="5">a5<option>
        </optgroup>
        <optgroup label="B">
            <option value="1">b1<option>
            <option value="2">b2<option>
            </optgroup> and so on...
        </select>

I am feeling difficult... For now, I am retrieving the records separately for each category...

View

 <optgroup label="<?php echo $details[0]->name;?>">
   <?php foreach($details as $post){?>
     <option value="<?php echo $post->sid;?>"><?php echo $post->sub_name;?></option>
    <?php }?>
  </optgroup>

Model

function idetails(){
    $this->db->select("*");
    $this->db->from('details');

    $query = $this->db->get();

    foreach($query->result_array() as $row){
 $categories[] = array("id" => $row['id'], "name" => $row['name']);
 }

 $this->db->select("*");
    $this->db->from('sub_details');

    $subquery = $this->db->get();
     foreach($subquery->result_array() as $row){
  $subcategory[$row['parent_id']][] = array("sid" => $row['sid'], "sname" => 
 $row['sname'], "parent_id" => $row['parent_id']);
 }
 return array('categories' => $categories, 'subcategory' => $subcategory);

}

SO now I need to print it in single query..can anyone help me out??

Answer 1

Try this,

    $this->db->select('details.*')
    ->from('details')
    $query = $this->db->get();
    $res = $query->result_array();
    foreach($res as $key=>$value){
        $query = $this->db->query("select * from sub_details  where  id=".$value['parent_id']." order by sid  desc");
        $res[$key]['key'] = $query->result_array();
    //  echo $this->db->last_query();
    }
    //echo "<pre>";print_R($res);exit;
    return $res;