I have Payment Table
. This Payment Table has paymentCusId(It is Customer Id)
, paymentCredit
and paymentDebit
rows etc... How can I show, If Customer's Balance is less than 50 euros, I want to show how many customers balance less than 50 euros.
For example, 5 customers balance less than 50.
Can I solve this using sql query or php conditions ?
Code below:
$this->db->select('payment.paymentCusId, sum(paymentCredit) - sum(paymentDebit)');
$this->db->from('payment');
$this->db->where('sum(paymentCredit) - sum(paymentDebit) <', 50);
$query_debit = $this->db->get();
$number = $query_debit->num_rows();
echo $number;
This code not working well. What can I do to show the number of customers less than 50 euros?
The query you are trying to perform has two errors.
1 - In order to you functions that aggregate function you need to group the result using GROUP BY
2 - Aggregation functions on the WHERE clause do not work, you need to put it in the having clause.
For your case the correct query would be:
$this->db->select('payment.paymentCusId, sum(paymentCredit) - sum(paymentDebit)');
$this->db->from('payment');
$this->db->group_by('payment.paymentCusId');
$this->db->having('sum(paymentCredit) - sum(paymentDebit) <', 50);
$query_debit = $this->db->get();
$number = $query_debit->num_rows();
echo $number;
For more information about codeigniter query builder check their documentation
A good way to figure out if your query is correct before trying to get results or num_rows, etc, is to verify it has an error:
$error = $this->db->error();
if($error) {
echo $error['message'];
echo $error['code'];
}