Search code examples
mysqlcodeignitercodeigniter-3

Select sum and count of every transaction per day and display it

I am making a monitoring system. My target is to show the sum and count of my transactions per day. I have provided a screenshot of my target. Any help will be appreciated.

enter image description here

View

<tr>
  <th scope="col"><i class="far fa-calendar-alt mr-2"></i>Date</th>
  <th scope="col"><i class="fas fa-user-tie mr-2"></i>Total Transaction</th> 
  <th scope="col"><i class="fas fa-user-tie mr-2"></i>Amount</th> 
   <th scope="col"><i class="fas fa-user-tie mr-2"></i>Action</th> 
      </tr>

Controller

public function testing()
    {
        
        
        $list = $this->repo->function_etc();
        $data = array();
        $no = $_POST['start'];
        foreach ($list as $person) {
            $no++;
            $row = array();
            $row[] = $person->dateUp;
            $row[] = number_format($person->amount);
            $row[] = number_format($person->count);
            $row[] ='<a class="btn btn-sm buttoon" href="https://sample.com/etcetc='.$person->dateUp.'" ><i class="glyphicon glyphicon-pencil"></i>View</a>';
             $data[] = $row;
        }
        
        $output = array(
           "data" => $data,
        );
        
        echo json_encode($output);
    }

Model:

//maybe i'm missing something here?

      private function function_etc()
            {
                
                $this->db->select('*');
                $this->db->from($this->tableDashboard);
                $this->db->where('transReference !=', '');
                $this->db->where('trans_siteName','https://somewhere.com/');
                $this->db->select_sum('tbl_transaction.amount');
                
                $this->db->group_by('tbl_transaction.dateUp');
            $query = $this->db->get();
                return $query->result();
               
            }
Solution

  • Your select section of The query should handle the count and sum.

    private function function_etc()
{
    $this->db->select('*, sum(amount) amt, count(ID) num');
    $this->db->from($this->tableDashboard);
    $this->db->where('transReference !=', '');
    $this->db->where('trans_siteName','https://somewhere.com/');
    $this->db->select_sum('tbl_transaction.amount');
    
    $this->db->group_by('tbl_transaction.date');
    $query = $this->db->get();
    return $query->result();
}