[FIXED] Nested sub category right under parent category in codeigniter

Issue

I want to to display the list of category in a table and the sub category of each parent category right under.

EDIT

I want it look like this:

Category One

-> Sub category One #1

-> Sub category One #2

Category Two

-> Sub category Two #1

-> Sub category Two #2

Category Three

-> Sub category Three #1

-> Sub category Three #2

But what I got is:

Category One

Category Two

Category Three

-> Sub category One #1

-> Sub category One #2

-> Sub category Two #1 ... etc

Here is the model:

function get_categories() {
    $data = array();
    $this->db->group_by('parent_id,category_id');
    $query = $this->db->get('category');
    if ($query->num_rows() > 0) {
        foreach ($query->result() as $row) {
            $data[] = $row;
        }
    }
    return $data;
}

And in the controller I call this function with $data['categories']=$this->category_model->get_categories(). And I display the data in the view with this code:

<?php foreach ($categories as $cat) : ?>
   <tr>
      <td><?php echo $cat->name ?></td>
      <td><?php echo $cat->description ?></td>
   </tr>
<?php endforeach; ?>

But the problem is, instead of under each their parent category, the sub categories are all showing on the bottom of the list. Please help me to fix this problem. Thanks.

Solution

You need iterate your categories and group them by parent id. As i understand your categories are stored as Adjacency List, so maybe this example will be helpfull:

function get_categories(){
    $this->db->order_by('parent_id');
    return $this->db->get('category')->result_array();
}
$categories = get_categories();
$result = array();
foreach($categories as $cat){
    if($cat['parent_id'] && array_key_exists($cat['parent_id'], $result)){
        $result[$cat['parent_id']]['sub_categories'][] = $cat;
    }
    else{
        $result[$cat['id']] = $cat;
    }
}

Second step need to render html:

<?php foreach ($result as $cat):?>
    <tr>
         <td><?=$cat['name'];?></td>
         <td><?=$cat['description'];?></td>
    </tr>
    <?php if(isset($cat['sub_categories'])):?>
        <?php foreach($cat['sub_categories'] as $sub_category):?>
            <tr>
                <td><?=$sub_category['name'];?></td>
                <td><?=$sub_category['description'];?></td>
           </tr>
        <?php endforeach;?>
    <?php endif;?>
<?php endforeach;?>

Html output is very similar to @scrowler answer.

Answered By - joni jones