Fovie Waka Fovie Waka - 2 months ago 5
MySQL Question

Conditionally Count a MYSQL Column Rows and Output Data via PHP

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

Answer

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).

Comments