Looping through MySQL table columns

[Heartstrings]

So I have a database that is structured like this: https://imgur.com/a/DdyTqiE
Sample data: https://imgur.com/a/kYwmuO1
For each appointment, a student can have multiple categories, such as 'Academic Probation, Re-Admit' etc...

I would like to loop through this table, and get a count of how many were 'is_no_show' and 'is_cancelled' per (unique) category with respect to 'scheduled_student_services.' 
For example, in the sample data, we see that the first appointment has 4 categories (Academic Probation, Entering Cohort Year 20051, First Generation, and Re-Admit'). Now one of these categories  - Academic Probation matches up with what they were scheduled for - '1st Term Probation Advising'.  And they cancelled this appointment, so we want the count of cancellations under Academic Probation to go up by 1. 

How would I approach this? I know I probably need to do two loops but I'm not sure the PHP syntax for this. Any suggestions or tips would be helpful. Thank you for your time!

[gw1500se]

No need to loop through anything. If you just need a count of various criteria you can do it with a MySQL query using the COUNT parameter. I suggest you ask on the MySQL forum.

[Barand]

A good source for php/MySQL interaction (using PDO is recommended) is https://phpdelusions.net/pdo_examples

This query should get you started

SELECT 
     , scheduled_student_service
     , category
     , COUNT(*) as total_appointments
     , SUM(is_no_show) as no_shows
     , SUM(is_cancelled) as cancellations
FROM thetable
GROUP BY scheduled_student_service, category

If that isn't what you want, repost with more details.

In future, please don't post images of data. They are as much use as chocolate teapots if we need to recreate your data to test with when helping.

[Heartstrings]

10 minutes ago, Barand said:

A good source for php/MySQL interaction (using PDO is recommended) is https://phpdelusions.net/pdo_examples

This query should get you started

SELECT 
     , scheduled_student_service
     , category
     , COUNT(*) as total_appointments
     , SUM(is_no_show) as no_shows
     , SUM(is_cancelled) as cancellations
FROM thetable
GROUP BY scheduled_student_service, category

If that isn't what you want, repost with more details.

In future, please don't post images of data. They are as much use as chocolate teapots if we need to recreate your data to test with when helping.

Sorry, I didn't know I will definitely keep in mind for next time. 

What do you think about this php script to store this result in an array?

$q = "SELECT * FROM thetable GROUP BY appointment_id";

try {
  $stmt = $dbh->prepare($q);
  $stmt->execute();
  $result = $stmt->fetchAll();


  $attendeeCategs = array();
  foreach($result as $r) {
    $attendeeCategs[] = array(
      $r['category'] => array(
         $r['scheduled_student_services'] => array(
           'is_no_show' => $r['is_no_show'],
           'is_cancelled' => $r['is_cancelled']
        )
      )
    );
  }
 echo json_encode(array('success'=> 1, 'msg'=> $attendeeCategs));
} // End of try
catch(PDOException $e) { // catch}

 
 

Edited December 12, 2019 by Heartstrings

[Barand]

6 minutes ago, Heartstrings said:

What do you think about this php script to store this result in an array?

I'm afraid that my answer is "Not a lot".

• Grouping by appointment_id will give you one row in the result set for each appointment. The contents of fields other than the appointment_id will be arbitrary.
• Don't use "select * ", define the columns you need.
• That query doesn't require preparing (no user data values) and executing. You could just use $dbh->query($q).