SUM not working in Codeigniter

[Moorcam]

Howdy folks,

Been a while. Hope all is well.

Anyway, I have run into a pickle and can't for the life of me get out of it.

I am trying to calculate the total amount of money in a database table. Table is called tbl_payment. The information is displayed in the admin dashboard.

 

Here is the section from view_Dashboard file:

    <div class="col-md-4 col-sm-6 col-xs-12">
      <div class="info-box">
        <span class="info-box-icon bg-green"><i class="fa fa-map-signs"></i></span>
        <div class="info-box-content">
          <span class="info-box-text">Total Income</span>
          <span class="info-box-number"><?php echo $paid_amount; ?></span>
        </div>
      </div>
    </div>

Here is the Controller file - Dashboard.php

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Dashboard extends CI_Controller 
{
	function __construct() 
	{
        parent::__construct();
        $this->load->model('admin/Model_common');
        $this->load->model('admin/Model_dashboard');
    }
	public function index()
	{
		$data['setting'] = $this->Model_common->get_setting_data();

		$data['total_category'] = $this->Model_dashboard->show_total_category();
		$data['total_news'] = $this->Model_dashboard->show_total_news();
		$data['total_destination'] = $this->Model_dashboard->show_total_destination();
		$data['total_tour'] = $this->Model_dashboard->show_total_tour();
		$data['total_team_member'] = $this->Model_dashboard->show_total_team_member();
		$data['total_client'] = $this->Model_dashboard->show_total_client();
		$data['total_service'] = $this->Model_dashboard->show_total_service();
		$data['total_testimonial'] = $this->Model_dashboard->show_total_testimonial();
		$data['total_traveller'] = $this->Model_dashboard->show_total_traveller();
		$data['total_vehicle'] = $this->Model_dashboard->show_total_vehicle();
      $data['paid_amount'] = $this->Model_dashboard->show_total_paid_amount();
		

		$this->load->view('admin/view_header',$data);
		$this->load->view('admin/view_dashboard',$data);
		$this->load->view('admin/view_footer');
	}
}

And finally, the Model file - Model_dashboard.php

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Model_dashboard extends CI_Model 
{
	public function show_total_category()
	{
		$sql = 'SELECT * from tbl_category';
        $query = $this->db->query($sql);
        return $query->num_rows();
    }
    public function show_total_news()
	{
		$sql = 'SELECT * FROM tbl_news';
        $query = $this->db->query($sql);
        return $query->num_rows();
    }
    public function show_total_destination()
    {
        $sql = 'SELECT * from tbl_destination';
        $query = $this->db->query($sql);
        return $query->num_rows();
    }
    public function show_total_tour()
    {
        $sql = 'SELECT * from tbl_tour';
        $query = $this->db->query($sql);
        return $query->num_rows();
    }
    public function show_total_team_member()
    {
        $sql = 'SELECT * from tbl_team_member';
        $query = $this->db->query($sql);
        return $query->num_rows();
    }
    public function show_total_client()
    {
        $sql = 'SELECT * from tbl_client';
        $query = $this->db->query($sql);
        return $query->num_rows();
    }
    public function show_total_service()
    {
        $sql = 'SELECT * from tbl_service';
        $query = $this->db->query($sql);
        return $query->num_rows();
    }
    public function show_total_testimonial()
    {
        $sql = 'SELECT * from tbl_testimonial';
        $query = $this->db->query($sql);
        return $query->num_rows();
    }
    public function show_total_traveller()
    {
        $sql = 'SELECT * from tbl_traveller';
        $query = $this->db->query($sql);
        return $query->num_rows();
    }
    
    public function show_total_vehicle()
    {
        $sql = 'SELECT * from tbl_vehicle';
        $query = $this->db->query($sql);
        return $query->num_rows();
    }
  
      public function show_total_paid_amount()
    {
$sql = 'SELECT `p_id`, SUM(`paid_amount`) AS `total_paid_amount` FROM `tbl_payment` GROUP BY `p_id`';
        
        $query = $this->db->query($sql);
        return $query->num_rows();
    }

}

I have tried all I can find. I have 1 record in the Database that shows an amount of $360.00. However, the result in Dashboard is just displaying the number 1.

Hope you guys can help.

Cheers,

Dan

[kicken]

26 minutes ago, Moorcam said:

However, the result in Dashboard is just displaying the number 1.

Because you're asking for the number of rows, not the actual data.

 

27 minutes ago, Moorcam said:

return $query->num_rows();

 

You want to fetch the row data and use that.  Some searching suggests that you'd do that using this code:

return $query->row()->total_paid_amount

Also, if you're trying to get a sum of all payments, you probably don't want to be using GROUP BY, and should remove p_id from your select list.

 

[Moorcam]

24 minutes ago, kicken said:

Because you're asking for the number of rows, not the actual data.

 

 

You want to fetch the row data and use that.  Some searching suggests that you'd do that using this code:

return $query->row()->total_paid_amount

Also, if you're trying to get a sum of all payments, you probably don't want to be using GROUP BY, and should remove p_id from your select list.

 

Thank you so much mate. That resolved it.