Conditionally Count a MYSQL Column Rows and Output Data via PHP

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

Please paste code as text, and not as a picture, if its relevant to the answer we cannot copy/paste a picture, and we are unlikely to want to re-write it — RiggsFolly
– RiggsFolly, Commented Sep 30, 2016 at 11:05
You need to alias the columns you are calculating: COUNT(Investigation) AS investigationCount,, etc. — jeroen
– jeroen, Commented Sep 30, 2016 at 11:06
Maybe 'Gender' is different than `Gender`? You are using different quotes up and down, so your aliases are different and there is no index in row array — daniherculano
– daniherculano, Commented Sep 30, 2016 at 11:09
@RiggsFolly the picture was for illustration, nothing more. Code is all there. Thanks for your help. — Fovie Waka
– Fovie Waka, Commented Sep 30, 2016 at 12:21

RiggsFolly · Accepted Answer · 2016-09-30 11:16:35Z

3

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

edited Sep 30, 2016 at 11:16

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

answered Sep 30, 2016 at 11:07

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

RiggsFolly Over a year ago

What about the COUNT() and SUM() and MONTH() columns as well. If you are going to do the job do it all

Shadow Over a year ago

@RiggsFolly Pls check out the last sentence of my answer.

RiggsFolly Over a year ago

For the benefit of a future visitor that just looks at this answer, it may look like that is all that needs to be done, just like it did to me

Fovie Waka Over a year ago

okay thanks a lot for the help...will try that now. Totally appreciated.

Collectives™ on Stack Overflow

Conditionally Count a MYSQL Column Rows and Output Data via PHP

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related