Jump to content

SUM not working in Codeigniter


Moorcam
Go to solution Solved by kicken,

Recommended Posts

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

Link to comment
Share on other sites

  • Solution
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.

 

  • Thanks 1
Link to comment
Share on other sites

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. :)

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.