Fovie Waka - 6 months ago 45

MySQL Question

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

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