Search code examples
phporacle-databasecodeignitermodel-view-controllereloquent

How to display result of number of posts for each category using Codeigniter in the view?

I'm working on a project using Codeigniter and I want to display the following data by showing result of number of posts for each category. I have a TABLE named POST which contains id, title, content, category_id and a TABLE named CATEGORY which contains id, category_name, category_type. So i would like to display the total of POSTS recorded for each CATEGORY selected in the request, The CATEGORY table is used for other tables, so I want to have just categories which relates to POSTS, the query is good but i don't know how to call the data to my view

SoI have difficulties about how to display data in VIEW, here an image on how I want to display the data, any help will be appreciated Thank you for your assistance, my respects

the result i want

here is my code :

Model:

class My_model extends CI_Model {

    function get_post_by_category() {
        $q = $this->db->select(' POST.CATEGORY_ID, CATEGORY.CATEGORY_NAME, COUNT(POST.CATEGORY_ID) as total_posts,  COUNT(POST.CATEGORY_ID)/COUNT(*) * 100 as percentage')
                      ->from('POST')
                      ->join('CATEGORY', 'POST.CATEGORY_ID= CATEGORY.ID', 'left')
                      ->where('CATEGORY.CATEGORY_TYPE', 'TYPE_POST')
                      ->group_by('POST.CATEGORY_ID, CATEGORY.CATEGORY_NAME') 
                      ->order_by('POST.CATEGORY_ID', 'ASC')
                      ->get();
        return $q->result();
    }
}

Controller:

 public function __construct() {
        parent::__construct();
        $this->load->model('My_model');
    }

    function index() {
        $this->load->model('My_model');
        //load the method of model  
        $data['countdata']=$this->My_model->get_post_by_category(); 
        //return the data in view  
        $this->load->view('data/my_view', $data);
    }

View: //so here where i have the problem

<table border="1">  
      <tbody>  
         <tr>  
            <td> CATEGORY NAME</td>  
            <td> Total POSTS FOR EACH CATEGOTY</td> 
            <td> PERCENTAGE%</td>  
         </tr>  
         <?php  
         foreach ($countdata as $row1)  
         {  
            ?><tr>  
            <td><?php echo $row1->CATEGORY_NAME;?></td>  
            <td><?php echo $row1->total_posts;--WHAT TO DO HERE TO GET NUMBER OF POST FOR EACH CATEGORY--;?></td>  
            <td><?php echo $row1->percentage;<--WHAT TO DO HERE TO GET THE PERCENTAGE OF ALL POST FOR ALL CATEGORIES--%;?></td>  
            </tr>  
         <?php }  
         ?>  
      </tbody>  
   </table>
Solution

  • Following the verification the code is correct it was necessary to write in capital letters so i had just to change this

    $row1->total_posts;

    to

    $row1->TOTAL_POSTS;