dongshan7708 2016-09-30 10:58
浏览 66
已采纳

通过PHP有条件地计算MYSQL列行和输出数据

Sorry i am new to this. Just trying to learn. I am trying to conditionally count the number of times a particular condition occurs in SQL, using the case and count functions. This counts the number of males/females stored in eeg table. Here is my SQL query.

SELECT  COUNT(CASE WHEN `Gender` = 'Male' THEN 1 END), 
        COUNT(CASE WHEN `Gender` = 'Female' THEN 1 END) 
FROM `eeg`

This outputs the data when i run the query on the mysql backend (phpmyadmin), but in my php file, I get an "Undefined Index" error for those 2 rows. All othjer rows are perfectly okay. I do not know how to output those particular set of data to a variable.

Here is the SQL query (in full) in the php file:

$result = mysql_query("SELECT MONTH(ScanDate), YEAR(ScanDate), 
                            COUNT(Investigation), 
                            COUNT(CASE WHEN `Gender` = 'Male' THEN 1 END), 
                            COUNT(CASE WHEN `Gender` = 'Female' THEN 1 END), 
                            SUM(InvestigationAmount), SUM(AmountDue)  
                        FROM eeg 
                        WHERE Investigation = '{$investigation}' 
                          AND ScanDate BETWEEN '{$ScanDate1}' 
                          AND '{$ScanDate2}'");

Here is the while loop (in full):

while($row=mysql_fetch_array($result)){ 
    $month_doe=$row['MONTH(ScanDate)']; 
    $year_doe=$row['YEAR(ScanDate)'];
    $si=$row['COUNT(Investigation)'];
    $male=$row["COUNT(CASE WHEN 'Gender' = 'Male' THEN 1 END)"];
    $female=$row["COUNT(CASE WHEN 'Gender' = 'Female' THEN 1 END)"];
    $sum_investigation=number_format($si);
    $sia=$row['SUM(InvestigationAmount)'];
    $sum_investigationamount=number_format($sia);
    $srd=$row['SUM(AmountDue)'];
    $sum_rebatedue=number_format($srd);
}

Thank you for your help. Been literally pulling my hair out, but love to learn and improve. And yes, mysql_query is depreciated :D

screenshots below:

Code screenshot

enter image description here

  • 写回答

1条回答 默认 最新

  • doushan6161 2016-09-30 11:07
    关注

    Use an alias for the expressions and use the alias to access the results of the expressions from php:

    $result = mysql_query("SELECT MONTH(ScanDate) as sdyear,
                                  YEAR(ScanDate) as sdmonth, 
                                COUNT(Investigation) as investigation, 
                                COUNT(CASE WHEN `Gender` = 'Male' THEN 1 END) as MaleCount, 
                                COUNT(CASE WHEN `Gender` = 'Female' THEN 1 END) as FemaleCount, 
                                SUM(InvestigationAmount) as investigationamount, 
                                SUM(AmountDue) as amountdue 
                            FROM eeg 
                            WHERE Investigation = '{$investigation}' 
                              AND ScanDate BETWEEN '{$ScanDate1}' 
                              AND '{$ScanDate2}'");
    
    
    while($row=mysql_fetch_array($result)){ 
        $month_doe=$row['sdmonth']; 
        $year_doe=$row['sdyear'];
        $si=$row['investigation'];
        $male=$row["MaleCount"];
        $female=$row["FemaleCount"];
        $sum_investigation=number_format($si);
        $sia=$row['investigationamount'];
        $sum_investigationamount=number_format($sia);
        $srd=$row['amountdue)'];
        $sum_rebatedue=number_format($srd);
    }
    

    I would use this approach for every field that is an expression (the other sum() fields in the above query).

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题