CODEWITHSUNDEEP
mysqlcodeignitercodeigniter-3
Dark
Dark

Reputation: 124

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

Upvotes: 0

Views: 846

Answers (3)

Ezekiel Arin
Ezekiel Arin

Reputation: 135

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

Upvotes: 1

Lenin
Lenin

Reputation: 430

MODEL FUNCTION

function tested() {   
    $this->db->select('Date,sum(Amount) as Total');
    $this->db->from('tbl_transaction');
    //$this->db->where('transReference !=', '');
    //$this->db->where('trans_siteName','https://somewhere.com/');
    
    $this->db->group_by('Date');
    $query = $this->db->get();
    return $query->result_array();
}

CONTROLLER FUNCTION: To process the database response

function tested() {
    $this->load->model('repo');

    $result = $this->repo->tested();
 
    foreach ($result as $k => $v) {
        $Date =  $v['Date'];
        $Total = $v['Total'];

        echo    $Date.  $Total .  "<br>";
    }
}

Upvotes: 1

Siva Koteswara Rao
Siva Koteswara Rao

Reputation: 129

SELECT Date, COUNT(ID) AS  'TranCount', SUM(amount) 
FROM table
WHERE DATE(date )= DATE(now()) 
GROUP BY date

Upvotes: 1

Related Questions