Jump to content

Looping through MySQL table columns


Heartstrings

Recommended Posts

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!

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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 by Heartstrings
Link to comment
Share on other sites

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

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.