Jump to content
JoseN

Multiple Criteria HTML Table from MYSQL

Recommended Posts

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.

screnshot.png.3c8a8a7fd4ed2d05e9541a8b19cb4cfe.png

 

 

This is my data from MYSQL database.

 

The Table is called Children

table.png.698e9ad75b0c7ba8de252573dc02f3cd.png

 

 

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

549144808_screnshotexplanation.thumb.png.6933d64e26cb0e002fd47fe51ea762a1.png

 

 

 

 

 

 

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

 

887901460_screenshot2.png.623fd5039786e0819d831d788ffca202.png

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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. 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.