Multiple Criteria HTML Table from MYSQL

[JoseN]

Hello Everyone -

 

I am playing around with some MYSQL and PHP project I have. I ran into a complex problem getting a PHP table filled with data from MYSQL. I will try to explain what I am trying to do:

 

I am trying to do something like this but in PHP.

 

 

This is my data from MYSQL database.

 

The Table is called Children

 

 

This is a quick explanation of how each column on the first screenshot should be filled from the database. 

 

 

 

 

 

 

This code is what I have so far... to be honest i am not sure how to get the totals of rest of the columns. Maybe use I can use subqueries or if statements... not sure! Can you please help me out? 

$r = mysqli_query($dbc,"SELECT Classrooms.ClassroomName, COUNT(*) AS TotalChildren
                        FROM Children
                        JOIN Classrooms ON Children.classroomID = Classrooms.classroomID
                        GROUP BY Classrooms.ClassroomName");

 

 

[requinix]

If you need counts according to the classroom and some other columns then your query should group by the classroom and the other columns.

You'll have too many rows, of course. Can't really put it into the table like that.

So do some processing on the results before you try to display them. Use the classroom to build a "row", then look at the other columns to decide what should show up in each of the "columns".

$classrooms = [];
foreach (/* rows from the query */ as $row) {
	list(
		"ClassroomName" => $classroom,
		"childcaretype" => $cc,
		"hstype" => $hs,
		"cpsstatus" => $cps,
		"TotalChildren" => $total
	) = $row;

	if (!isset($classrooms[$classroom])) {
		$classrooms[$classroom] = [
			"KIDS ENROLLED" => 0,
			"CC Only" => 0,
			"EHS/PI" => 0,
			"CC/PI" => 0,
			...
		]
	}

	$classrooms[$classroom]["KIDS ENROLLED"] += $total;

	/* update other columns... */
}

ksort($classrooms); // sort by name

That's the basic idea: total up the values as you go through all the rows. Then your table displays what's in $classrooms.

[Barand]

Alternatively you can do it in the query by defining the condition for each column and setting it 0 or 1 then SUMMING the columns grouped by class

I have only done the first five columns but you should get the idea

SELECT classroomname as Classroom
     , COUNT(*) as Enrolled
     , SUM(cconly) as `CC Only`
     , SUM(ehspi) as `EHS/PI`
     , SUM(ccpi) as `CC/PI`
     , SUM(ehsccpi) as `EHS/CC/PI`
FROM classrooms
        JOIN
     (
		SELECT classroomID
             , CASE WHEN childcaretype IS NOT NULL
                         AND hstype IS NULL
                         AND cpsstatus IS NULL
                    THEN 1
                    ELSE 0
                END as cconly
              , CASE WHEN hstype = 'EHS'
                          AND (cpsstatus = 'PI PEN' OR cpsstatus = 'PI APP')
                     THEN 1 
                     ELSE 0
                 END as ehspi
              , CASE WHEN childcaretype IS NOT NULL
                          AND (cpsstatus = 'PI PEN' OR cpsstatus = 'PI APP')
                     THEN 1
                     ELSE 0
                 END as ccpi
               , CASE WHEN childcaretype IS NOT NULL
                           AND hstype = 'EHS'
                           AND (cpsstatus = 'PI PEN' OR cpsstatus = 'PI APP')
					  THEN 1
                      ELSE 0
                  END as ehsccpi
		 FROM children	
     ) as childs USING (classroomid)
GROUP BY classroomname;

+-----------+----------+---------+--------+-------+-----------+
| classroom | Enrolled | CC Only | EHS/PI | CC/PI | EHS/CC/PI |
+-----------+----------+---------+--------+-------+-----------+
| Room 1    |       13 |       5 |      1 |     3 |         1 |
| Room 2    |       12 |       3 |      1 |     3 |         1 |
| Room 3    |       16 |       2 |      2 |     5 |         2 |
| Room 4    |       18 |       5 |      0 |     4 |         0 |
| Room 5    |       10 |       4 |      0 |     0 |         0 |
| Room 6    |       12 |       2 |      1 |     2 |         1 |
| Room 7    |       12 |       4 |      1 |     2 |         1 |
+-----------+----------+---------+--------+-------+-----------+

It would better if the cpsstatus were two columns

1. PI or PFA
2. AP or PEN

[JoseN]

1 hour ago, requinix said:

If you need counts according to the classroom and some other columns then your query should group by the classroom and the other columns.

You'll have too many rows, of course. Can't really put it into the table like that.

So do some processing on the results before you try to display them. Use the classroom to build a "row", then look at the other columns to decide what should show up in each of the "columns".

$classrooms = [];
foreach (/* rows from the query */ as $row) {
	list(
		"ClassroomName" => $classroom,
		"childcaretype" => $cc,
		"hstype" => $hs,
		"cpsstatus" => $cps,
		"TotalChildren" => $total
	) = $row;

	if (!isset($classrooms[$classroom])) {
		$classrooms[$classroom] = [
			"KIDS ENROLLED" => 0,
			"CC Only" => 0,
			"EHS/PI" => 0,
			"CC/PI" => 0,
			...
		]
	}

	$classrooms[$classroom]["KIDS ENROLLED"] += $total;

	/* update other columns... */
}

ksort($classrooms); // sort by name

That's the basic idea: total up the values as you go through all the rows. Then your table displays what's in $classrooms.

Thanks requinix! I will try out your idea! Thanks for your response.

[JoseN]

1 hour ago, Barand said:

Alternatively you can do it in the query by defining the condition for each column and setting it 0 or 1 then SUMMING the columns grouped by class

I have only done the first five columns but you should get the idea

SELECT classroomname as Classroom
     , COUNT(*) as Enrolled
     , SUM(cconly) as `CC Only`
     , SUM(ehspi) as `EHS/PI`
     , SUM(ccpi) as `CC/PI`
     , SUM(ehsccpi) as `EHS/CC/PI`
FROM classrooms
        JOIN
     (
		SELECT classroomID
             , CASE WHEN childcaretype IS NOT NULL
                         AND hstype IS NULL
                         AND cpsstatus IS NULL
                    THEN 1
                    ELSE 0
                END as cconly
              , CASE WHEN hstype = 'EHS'
                          AND (cpsstatus = 'PI PEN' OR cpsstatus = 'PI APP')
                     THEN 1 
                     ELSE 0
                 END as ehspi
              , CASE WHEN childcaretype IS NOT NULL
                          AND (cpsstatus = 'PI PEN' OR cpsstatus = 'PI APP')
                     THEN 1
                     ELSE 0
                 END as ccpi
               , CASE WHEN childcaretype IS NOT NULL
                           AND hstype = 'EHS'
                           AND (cpsstatus = 'PI PEN' OR cpsstatus = 'PI APP')
					  THEN 1
                      ELSE 0
                  END as ehsccpi
		 FROM children	
     ) as childs USING (classroomid)
GROUP BY classroomname;

+-----------+----------+---------+--------+-------+-----------+
| classroom | Enrolled | CC Only | EHS/PI | CC/PI | EHS/CC/PI |
+-----------+----------+---------+--------+-------+-----------+
| Room 1    |       13 |       5 |      1 |     3 |         1 |
| Room 2    |       12 |       3 |      1 |     3 |         1 |
| Room 3    |       16 |       2 |      2 |     5 |         2 |
| Room 4    |       18 |       5 |      0 |     4 |         0 |
| Room 5    |       10 |       4 |      0 |     0 |         0 |
| Room 6    |       12 |       2 |      1 |     2 |         1 |
| Room 7    |       12 |       4 |      1 |     2 |         1 |
+-----------+----------+---------+--------+-------+-----------+

It would better if the cpsstatus were two columns

1. PI or PFA
2. AP or PEN

Thanks so much barand! So, your idea can be done in one single query? Unfortunately, PI PEN, PI APP, PFA PEN, and PFA APP are options that belong to the cpsstatus. In my idea, PEN means Pending and APP means Approved.