为了账号安全,请及时绑定邮箱和手机立即绑定

如何检测连续几天考试的学生?

如何检测连续几天考试的学生?

PHP
Cats萌萌 2021-09-18 11:04:04
我有一个包含 7 列的 (Examdata) 表:(Class_ID、Subject_ID、Student_ID、Subject_name、exam_days、exam_dates、exam_times)我想检测第二天有考试和另一场考试的“student_id”,例如:| student_id | subject_name | exam_days | exam_date ||------------|--------------|-----------|-----------|| 1          |  math        | Sunday    | 2/4/2019  || 1          | physical     | Monday    | 3/4/2019  |这里的 student_id="1" 连续几天有两次考试,也许 student_id 比我想检测的还要多。注意:如果学生在周四 m 和周日有考试,则不应考虑,因为他们之间是周末。这是我的尝试:<?php$con = mysqli_connect("localhost", "root", "", "Exams");$array1 = array();$n = "SELECT DISTINCT exam_dates FROM Examdata ORDER BY exam_dates";$queryarray1 = mysqli_query($con, $n) or die("Error in query: $queryarray1. ".mysqli_error());while ($row = mysqli_fetch_assoc($queryarray1)) {    $array1[] = $row;}// print_r($array1);/* the output for this :Array ( [0] => Array ( [exam_dates] => 1440-04-02 ) [1] => Array ( [exam_dates] => 1440-04-03 ) [2] => Array ( [exam_dates] => 1440-04-04 ) [3] => Array ( [exam_dates] => 1440-04-05 ) [4] => Array ( [exam_dates] => 1440-04-06 ) [5] => Array ( [exam_dates] => 1440-04-08 ) [6] => Array ( [exam_dates] => 1440-04-09 ) [7] => Array ( [exam_dates] => 1440-04-10 ) [8] => Array ( [exam_dates] => 1440-04-11 ) [9] => Array ( [exam_dates] => 1440-04-12 ) [10] => Array ( [exam_dates] => 1440-04-13 ) [11] => Array ( [exam_dates] => 1440-04-15 ) [12] => Array ( [exam_dates] => 1440-04-16 ) [13] => Array ( [exam_dates] => 1440-04-17 ) [14] => Array ( [exam_dates] => 1440-04-18 ) [15] => Array ( [exam_dates] => 1440-04-20 ) )*/$b = json_encode($array1);$z = sizeof($array1);for ($i = 0; $i < $z; $i++) {    $search = "SELECT *              FROM Examdata e1            (                SELECT *     }}消息错误:查询错误:。您的 SQL 语法有错误;检查与您的 MariaDB 服务器版本相对应的手册,以获取在第 2 行附近使用的正确语法(SELECT * from Examdata e2 where e1.exam_dates==[ && e2.exam_dates='-- 但我不知道如何在查询中传递数组。
查看完整描述

2 回答

?
莫回无

TA贡献1865条经验 获得超7个赞

我会给你一个有效的解决方案,并尝试考虑一个更好/更优化的解决方案。


所以我们需要做的是在student_id上将Table与自己连接起来,然后我们需要使用DATEDIFF函数检查exam1是否在exam2之后1天到来。


这是 SQL 查询:


SELECT examData1.student_id, examData1.subject_name as first_subject_name, examData2.subject_name as next_subject_name , examData1.exam_days as first_exam_day, examData2.exam_days as next_exam_day, examData1.exam_date, examData2.exam_date

FROM Examdata as examData1, Examdata as examData2

WHERE examData1.student_id = examData2.student_id AND DATEDIFF(examData2.exam_date,examData1.exam_date) = 1

检查此链接以测试查询。


注意:您应该检查 SQL 语法导致您收到语法错误的错误。


查看完整回答
反对 回复 2021-09-18
?
潇湘沐

TA贡献1816条经验 获得超6个赞

按日期顺序获取考试日期,然后您可以在PHP中使用这样的东西,或者您的解决方案需要“纯SQL”?


$currentStudent = 0;

$lastCheckedDate = 0;

$listOfStudentsWithConsecutiveDates = [];


while( $row = mysqli_fetch_array( $result )  ) {

    if( $currentStudent == $row['student_id'] ) {

       if( isset($listOfStudentsWithConsecutiveDates[ $row['student_id'] ]) ) continue;

       $datediff = strtotime( $row['exam_date'] ) - $lastCheckedDate;

       if( round($datediff / (60 * 60 * 24)) == 1 ) {

           $listOfStudentsWithConsecutiveDates[ $row['student_id'] ] = $row;

       );

    } else {

       $currentStudent = $row['student_id'];

       $lastCheckedDate = strtotime( $row['exam_date'] );

    }

}

你会得到一个数组


$listOfStudentsWithConsecutiveDates[ student_id ] = first exam which is consecutive to another one for this student



查看完整回答
反对 回复 2021-09-18
  • 2 回答
  • 0 关注
  • 143 浏览

添加回答

举报

0/150
提交
取消
意见反馈 帮助中心 APP下载
官方微信