duanjia3187 2018-09-26 21:17
浏览 86
已采纳

Codeigniter - 显示有条件的数字

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?

  • 写回答

1条回答 默认 最新

  • doushi3202 2018-09-27 00:53
    关注

    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'];
        }
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 乌班图ip地址配置及远程SSH
  • ¥15 怎么让点阵屏显示静态爱心,用keiluVision5写出让点阵屏显示静态爱心的代码,越快越好
  • ¥15 PSPICE制作一个加法器
  • ¥15 javaweb项目无法正常跳转
  • ¥15 VMBox虚拟机无法访问
  • ¥15 skd显示找不到头文件
  • ¥15 机器视觉中图片中长度与真实长度的关系
  • ¥15 fastreport table 怎么只让每页的最下面和最顶部有横线
  • ¥15 java 的protected权限 ,问题在注释里
  • ¥15 这个是哪里有问题啊?