how to display categories,sub categories and sub sub categories - codeigniter

Question

My categories table design is as follows

I have three levels of category

parent
child
sub child

How to display them in the following manner ?

(SELECT * FROM categories) : i get the following array as below

Array
(
    [0] => stdClass Object
        (
            [category_id] => 1
            [category_slug] => 
            [category_glyphicon] => live glypicon
            [category_name] => Live
            [is_parent] => 0
            [is_child] => 0
            [is_sub_child] => 0
            [rf_flag] => 0
            [status] => 1
            [category_description] => 
            [created_on] => 2015-09-04 10:47:54
            [updated_on] => 2015-09-05 22:48:01
        )

    [1] => stdClass Object
        (
            [category_id] => 2
            [category_slug] => 
            [category_glyphicon] => dddd
            [category_name] => work
            [is_parent] => 0
            [is_child] => 0
            [is_sub_child] => 0
            [rf_flag] => 0
            [status] => 1
            [category_description] => 
            [created_on] => 2015-09-04 11:41:18
            [updated_on] => 2015-09-04 11:11:18
        )

    [2] => stdClass Object
        (
            [category_id] => 3
            [category_slug] => 
            [category_glyphicon] => mmm
            [category_name] => enjoy
            [is_parent] => 0
            [is_child] => 0
            [is_sub_child] => 0
            [rf_flag] => 0
            [status] => 1
            [category_description] => 
            [created_on] => 2015-09-04 11:41:52
            [updated_on] => 2015-09-04 11:12:51
        )

    [3] => stdClass Object
        (
            [category_id] => 4
            [category_slug] => 
            [category_glyphicon] => mmm
            [category_name] => for sale
            [is_parent] => 1
            [is_child] => 0
            [is_sub_child] => 0
            [rf_flag] => 0
            [status] => 1
            [category_description] => 
            [created_on] => 2015-09-04 11:42:59
            [updated_on] => 2015-09-04 11:12:59
        )

    [4] => stdClass Object
        (
            [category_id] => 5
            [category_slug] => 
            [category_glyphicon] => ccccc
            [category_name] => for rent
            [is_parent] => 1
            [is_child] => 0
            [is_sub_child] => 0
            [rf_flag] => 0
            [status] => 1
            [category_description] => 
            [created_on] => 2015-09-05 12:13:48
            [updated_on] => 2015-09-04 11:43:48
        )

    [5] => stdClass Object
        (
            [category_id] => 6
            [category_slug] => 
            [category_glyphicon] => sss
            [category_name] => villas
            [is_parent] => 4
            [is_child] => 1
            [is_sub_child] => 0
            [rf_flag] => 0
            [status] => 1
            [category_description] => 
            [created_on] => 2015-09-05 12:14:57
            [updated_on] => 2015-09-04 11:44:57
        )

)

Basically, you have two ways/two db designs when dealing with hierarchical data: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ And, then you can check some of the solutions for displaying of categories: http://stackoverflow.com/questions/3116330/recursive-categories-with-a-single-query, http://stackoverflow.com/questions/2871861/how-to-build-unlimited-level-of-menu-through-php-and-mysql#3368622 — sinisake, Sep 06 '15 at 09:07
Well... it is easier to me, and i have used it so far, didn't tried second model. Someone with more experience maybe could provide some info about it... Also, i usually set one additional column (position) to apply custom order... — sinisake, Sep 06 '15 at 10:30
in the above article its mentioned (under the Adjacency List Model) "In addition, special care must be taken when deleting nodes because of the potential for orphaning an entire sub-tree in the process" what did u you do in this case (deletion) ?? — Chaitanya K, Sep 06 '15 at 10:57
Well - if (sub)category is removed, all (sub)subcategories/children can be removed, too, or can be left, depending on your needs. E.g. Delete from categories WHERE category_id = 'something' removes just one category, and leave sub-tree (if category is parent)... Now you can update parent_id to something else, or, if you need whole sub-tree removal -> you will apply something like: 'Delete from categories where parent_id = 'something'... all depends on your needs... — sinisake, Sep 06 '15 at 10:57
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/88924/discussion-between-chaitanya-koripella-and-nevermind). — Chaitanya K, Sep 06 '15 at 11:01
One last doubt , According to this example http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ if i want to delete parent - Electronics - and (its whole sub-tree) what would be the query ? ? ? — Chaitanya K, Sep 06 '15 at 11:22
If you want to remove one tree, and leave other, first rather remove tree it self e.g. 'televisions', and then top category (but, in this case, removing of top category could be actually whole table removal:))... Try to build test table - and play a little with it, to see how it works, and then apply queries to your CI admin panel. — sinisake, Sep 06 '15 at 11:48

score 0 · Answer 1 · answered Sep 06 '15 at 06:20

try the following code
I assumed you have mysqli db connection

 //assuming db connction with mysqli

 $res=$db->query("select * from categories");
 if($res){
   echo "<table>";
   echo "<tr>";
     echo "<td>A</td><td>B</td><td>C</td><td>D</td>";
   echo "</tr>";
   $index = 1;
   while( $row = $res->fetch_array()){

       echo "<td>".$index."</td>";        

       if($row['is_parent']>0){
          echo "<td>".$row['category_name']."</td>";        
          echo "<td>0</td>";        
          echo "<td>0</td>";        
       }else if($row['is_child']>0){
          echo "<td>0</td>";      
          echo "<td>".$row['category_name']."</td>";          
          echo "<td>0</td>";        
       }else if($row['is_sub_child']>0){
          echo "<td>0</td>";        
          echo "<td>0</td>";        
          echo "<td>".$row['category_name']."</td>";        
       }  

        $index++;
    }
    echo "</table>";
   }else{
      echo $db->error;
   }

i tried in the way you suggested , but did not get the required output.. i have made some changes in my code..please review and suggest... — Chaitanya K, Sep 06 '15 at 07:45
should i write any join ? instead of (SELECT * FROM categories) ?? — Chaitanya K, Sep 06 '15 at 07:53
how to distinguish parent, child and sub_child, in your array first 3 records are having is_parent=is_child=is_sub_child=0, where these 3 records should be placed — alamnaryab, Sep 06 '15 at 19:44

score 0 · Accepted Answer · answered Sep 07 '15 at 03:08

I got the output

public function get_dt_data()
        {
            error_reporting(1);     
            // data is loading into $list
            $list = $this->category1_model->get_datatables();
            $parenrarray=array();

            foreach($list as $k){
                $parenrarray[$k->category_id]=$k->category_name;
            }

            //echo '<pre>';print_r($list);echo'</pre>';;
            //echo '<pre>';print_r($parenrarray);echo'</pre>';exit();


            $data = array();
            $no = $_POST['start'];
            foreach ($list as $p) {
                $no++;
                $row = array();

                $row[] = "<input type='checkbox'  class='deleteRow' value='".$row['category_id']."'  /> #".$no ;
                $row[] = $p->category_id;

                // DISPLAY TABLE HIERARCHY ----
                $y=($p->is_parent !=0)?$parenrarray[$p->is_parent]:$p->category_name;
                $x=$p->is_child !=0 ?$parenrarray[$p->is_child]:($p->is_parent !=0 ?$p->category_name:'0');
                $z=$p->is_child !=0 ?$p->category_name:'0';

                $row[] = "<a href='".base_url()."admin/category/level1/$p->category_name'>".$y.'</a>';
                $row[] = "<a href='".base_url()."admin/category/level1/$p->category_name'>".$x.'</a>';
                $row[] = "<a href='".base_url()."admin/category/level1/$p->category_name'>".$z.'</a>';

                //  DATA ACTIONS-------
                $row[] = '<a class="btn btn-xs btn-primary" href="javascript:void()" title="Edit" onclick="edit_person('."'".$p->category_id."'".')">
                      <i class="glyphicon glyphicon-pencil"></i> Edit</a>
                      <a class="btn btn-xs btn-danger" href="javascript:void()" title="Hapus" onclick="delete_person('."'".$p->category_id."'".')">
                      <i class="glyphicon glyphicon-trash"></i> Delete</a>';
                $data[] = $row;   

            }

            $output = array(
                            "draw" => $_POST['draw'],
                            "recordsTotal" => $this->categories->count_all(),
                            "recordsFiltered" => $this->categories->count_filtered(),
                            "data" => $data,
                    );
            //output to json format
            echo json_encode($output);
        }

score 0 · Answer 3 · answered Feb 19 '18 at 18:39

multi-level categories feature supposed to follow recursive concept. Here is an example with codeigniter

model:

public function get_categories(){

    $this->db->select('*');
    $this->db->from('categories');
    $this->db->where('parent_id', 0);

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

    $categories = $parent->result();
    $i=0;
    foreach($categories as $p_cat){

        $categories[$i]->sub = $this->sub_categories($p_cat->cat_id);
        $i++;
    }
    return $categories;
}

public function sub_categories($id){

    $this->db->select('*');
    $this->db->from('categories');
    $this->db->where('parent_id', $id);

    $child = $this->db->get();
    $categories = $child->result();
    $i=0;
    foreach($categories as $p_cat){

        $categories[$i]->sub = $this->sub_categories($p_cat->cat_id);
        $i++;
    }
    return $categories;       
}

by calling this (controller)

public function categories(){

    $this->load->model('model_categories');
    $data = $this->model_categories->get_categories();
    print_r($data);

}

Here is an official codeigniter forum thread for https://forum.codeigniter.com/thread-69149.html

how to display categories,sub categories and sub sub categories - codeigniter

3 Answers3