Codeigniter - 显示有条件的数字

Question

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?

Answer 1

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'];
    }