How to GET number of posts for each category with type category_post using Codeigniter (query group_by )?

CodingStudent 0
Viewed 76

hello Stackoverflow team, I'm working on a project using Codeigniter and I want to display the following data by showing the number of data in real time. 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, because the CATEGORY table is used for other tables, so I want to have just categories which relates to POSTS, but when i send the data to the view i get the following error : Severity: Warning

Message: oci_execute(): ORA-00979: not a group by expression GROUP BY. //UPDATE:!! So After editing the Model code as suggestedg, i get the following error code Message: oci_execute(): ORA-00936: missing expression

Maybe I have problem with JOIN and GROUP_BY and also difficulties about how to display data in VIEW here an image on how I want to have the data, any help will be appreciated Thank you for your assistance, my respects here the result i'm woring on

here is my 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();
        }
    }

here is my controller

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

    function index() {
        $this->load->model('My_model'); 
        //config
        $config['base_url'] = base_url('/?c=ui_page'); 
        //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);
    }

here is my view

<table border="1">  
      <tbody>  
         <tr>  
            <td> CATEGORY NAME</td>  
            <td> Total POSTS</td> 
            <td> %</td>  
         </tr>  
         <?php  
         foreach ($countdata as $row1)  
         {  
            ?><tr>  
            <td><?php echo $row1->CATEGORY_NAME;?></td>  
            <td><?php echo $row1->ID;?></td>  
            <td>%</td>  
            </tr>  
         <?php }  
         ?>  
      </tbody>  
   </table>
codeigniter model-view-controller oracle eloquent php
1 Answers

if you not specified columns at GROUP BY clause, you cannot use this columns at SELECT. Because GROUP BY gives array and SELECT expects only one element. But you can use aggregate functions such as COUNT, AVG, MIN, MAX, SUM and so on(List of Aggregate functions).

So try this solution:

class My_model extends CI_Model {

    function get_post_by_category() {
        $postsCount = $this->db->select('count(*) as posts_count')
                        ->from('POST')->get()->row()->posts_count;
        
        $this->db->select(' POST.CATEGORY_ID, CATEGORY.CATEGORY_NAME, COUNT(POST.CATEGORY_ID) as total_posts')
                ->from('POST')
                ->join('CATEGORY', 'POST.CATEGORY_ID= CATEGORY.ID', 'left')
                ->where('CATEGORY.CATEGORY_TYPE', 'TYPE_POST');
        $this->db->group_by('POST.CATEGORY_ID, CATEGORY.CATEGORY_NAME'); 
        $this->db->order_by('POST.CATEGORY_ID', 'ASC');
        $q = $this->db->get();
        return [ 'result' => $q->result(), 'post_count' => $postsCount ];
    }
}

Without changes to your controller, your view must be like:

<table border="1">
    <tbody>
        <tr>
            <td> CATEGORY NAME</td>
            <td> Total POSTS</td>
            <td> %</td>
        </tr>
        <?php
        foreach ($countdata['result'] as $row1) { ?>
            <tr>
                <td><?php echo $row1->CATEGORY_NAME; ?></td>
                <td><?php echo $row1->total_posts; ?></td>
                <td><?php echo  $countdata['post_count'] > 0 ? round(($row1->total_posts / $countdata['post_count']) * 100, 2) : 0; ?>%</td>
            </tr>
        <?php } ?>
    </tbody>
</table>
Orkhan Zeynalli0
Related