Keine Beschreibung

special5.php 2.7KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687
  1. <?php
  2. // This script fetches how many people have answered a given moment
  3. // and spits out a json with an array of people count, moment ID, and date
  4. require_once 'processes/config.php';
  5. require_once 'processes/dbh.inc.php';
  6. require_once 'processes/checkLogin.php';
  7. $experienceID = mysqli_real_escape_string($connection, trim($_POST['experienceID']));
  8. // Old Query
  9. // (Note: only the tuple (momentID, date) is unique, so same moments yet different dates appear separated from each other)
  10. /*
  11. $sql = "SELECT COUNT(id_student) AS `count`, id_subquestionnair AS momentID, DATE(answered_date) AS `date`
  12. FROM `student_subquestionnair`
  13. WHERE id_subquestionnair IN
  14. (SELECT id_subquestionnair
  15. FROM experience_subquestionnair
  16. WHERE id_experience = '$experienceID')
  17. GROUP BY id_subquestionnair, DATE(answered_date)
  18. ORDER BY DATE(answered_date) ASC";
  19. */
  20. // Fixed Query
  21. // (Note: used date_to_administer instead of date_answered to avoid splitting (momentID, date) tuples)
  22. /*
  23. $sql = "SELECT COUNT(SS.id_student) AS `count`, SS.id_subquestionnair AS momentID, DATE(SQ.date_to_administer) AS `date`
  24. FROM `student_subquestionnair` AS SS RIGHT JOIN `subquestionnair` AS SQ
  25. WHERE SS.id_subquestionnair = SQ.id
  26. AND SS.id_subquestionnair IN
  27. (SELECT id_subquestionnair
  28. FROM experience_subquestionnair
  29. WHERE id_experience = '$experienceID')
  30. GROUP BY SS.id_subquestionnair, DATE(SQ.date_to_administer)
  31. ORDER BY DATE(SQ.date_to_administer) ASC";
  32. */
  33. // Re-fixed Query
  34. // (Note: display 0 for those moments that haven't been answered as of "today")
  35. $sql = "SELECT COUNT(SS.id_student) AS `count`, SQ.id AS momentID, DATE(SQ.date_to_administer) AS `date`, SQ.title AS momentTitle
  36. FROM `student_subquestionnair` AS SS RIGHT JOIN `subquestionnair` AS SQ
  37. ON SS.id_subquestionnair = SQ.id
  38. WHERE SQ.id IN
  39. (SELECT id_subquestionnair
  40. FROM experience_subquestionnair
  41. WHERE id_experience = '$experienceID')
  42. AND DATE(SQ.date_to_administer) <= CURRENT_DATE
  43. GROUP BY SQ.id, DATE(SQ.date_to_administer)
  44. ORDER BY DATE(SQ.date_to_administer) ASC";
  45. $result = mysqli_query($connection, $sql);
  46. // Prepare Result (fetch max score at once)
  47. $maxScore = 0;
  48. while($row = mysqli_fetch_object($result)) {
  49. // Fetch Max Score (for formating chart purposes)
  50. if($maxScore < $row->count) {
  51. $maxScore = $row->count;
  52. }
  53. // Format Date as "<abbreviated month> <day of the month without leading zero>"
  54. $row->date = date('M j', strtotime($row->date));
  55. // Append to array
  56. $data[] = $row;
  57. }
  58. // EXAMPLE:
  59. // $temp = [
  60. // [
  61. // 'count' => 1,
  62. // 'momentID' => 133,
  63. // 'date' => '2020-03-12'
  64. // ]
  65. // ];
  66. $payload = [
  67. 'maxScore' => $maxScore,
  68. 'data' => $data
  69. ];
  70. echo json_encode($payload);