Heartstrings

Thank you sir!

Interesting! So since I don't have a separate attended column..(it's based on whether no_shows & cancellations are both 0), can I just add them up and subtract from total to get attended?

Nevermind I just needed to use ` instead of ' for the column names lol. Thank you so much.

When I ran this query I get: +------------+-----------+-----------+ | weekno | no_shows | cancelled | +------------+-----------+-----------+ | NULL | 0 | 0 |

DB structure: https://imgur.com/a/DdyTqiE

Yes, that's what my DB looks like Hmm..that SELECT statement is giving me null even though I replaced the variables with the correct names.

So I'm calling in data from a DB where each record is associated with a (column) 'start_time', 'no_shows', 'cancelled', and 'attended' I want to go through the results of this SELECT and count the number of no shows, cancellations and attended on a WEEKLY basis (based on the start_time). How can I achieve this? The user will provide a date range and the DB will select the records based on that date range. Now this date range will then have to be split by weeks and then get the count. I'm totally stuck on the counting by weeks part. This is what I have so far: All it does is just organize the data into separate arrays without any counting. Attached is a preview of the output. // Multidimensional Array with [boolean][week #] $no_shows_weekly = [ 'no_show' => [], 'week' => [] ]; $cancelled_weekly = [ 'cancelled' => [], 'week' => [] ]; $attended_weekly = [ 'attended' => [], 'week' => [] ]; foreach($result as $r) { $start_time = new DateTime($r['start_time']); if($r['is_no_show'] == 0 && $r['is_cancelled'] == 0) { array_push($attended_weekly['attended'], 1); array_push($attended_weekly['week'], date_format($start_time, "W")); } else { array_push($attended_weekly['attended'], 0); array_push($attended_weekly['week'], date_format($start_time, "W")); } array_push($no_shows_weekly['no_show'], $r['is_no_show']); array_push($no_shows_weekly['week'], date_format($start_time, "W")); array_push($cancelled_weekly['cancelled'], $r['is_cancelled']); array_push($cancelled_weekly['week'], date_format($start_time, "W")); } echo json_encode(array( 'success'=> 1, 'msg'=> array( 'No Shows' => $no_shows_weekly, 'Cancellations' => $cancelled_weekly, 'Attendend' => $attended_weekly ) )); Any suggestions or help is greatly appreciated!

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}

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!