CODEWITHSUNDEEP
phpmysqlsqlarraysmultidimensional-array
Carson Lee
Carson Lee

Reputation: 2893

Array Data Calculation

I have met problem dealing with multidimensional array. Below are the code to store all the data query from mysql to array. May I know is there any idea how to calculate how many different module name in the multidimensional array and break the status into 2 column which are late and present? The SQL query get the data from a complex table which store all ModuleName and different status. I need to categories them and show in a table. So basically I need to check how many module in the array and how many late or present for the module. 

while($array = mysql_fetch_array($result)){
        $list[] = array(
                        "ModuleName"=>$array['ModuleName'],
                        "status"    =>$array['Status']
            ); 
    }

Sample of the SQL query

This is the sample of expected result

Sample Expected Result

Upvotes: 0

Views: 94

Answers (1)

gwc
gwc

Reputation: 1293

Updated to group by ModuleName

SELECT ModuleName
      ,COUNT(*) AS module_count
      ,SUM(CASE WHEN Status != 'Present' THEN 1 ELSE 0 END) as late_count
      ,SUM(CASE WHEN Status  = 'Present' THEN 1 ELSE 0 END) as present_count
  FROM ( your original query )
 GROUP BY ModuleName

Just replace "your original query" with the SQL you used to retrieve the data in the array.

Original

SELECT COUNT(*) AS module_count
      ,SUM(CASE WHEN Status != 'present' THEN 1 ELSE 0 END) as late_count
      ,SUM(CASE WHEN Status  = 'present' THEN 1 ELSE 0 END) as present_count
  FROM ( your original query )

Upvotes: 1

Related Questions