Fovie Waka Fovie Waka - 1 year ago 79
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.

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(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):

$male=$row["COUNT(CASE WHEN 'Gender' = 'Male' THEN 1 END)"];
$female=$row["COUNT(CASE WHEN 'Gender' = 'Female' THEN 1 END)"];

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 Source

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}'");


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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download