quiz_record 桌子：我需要計算所有那些具有排marks 不到的marks的Student_Id['4']。此外，如果其他的標記是關(guān)閉相同的的標記Student_Id['4']，然后還指望所有那些time gap (Quiz_End - Quiz_Start)是更比time gap (Quiz_End - Quiz_Start)的Student_Id['4']。預期結(jié)果：2為此，我嘗試了：$time_taken = strtotime($fetch_quiz_record['Quiz_End']) - strtotime($fetch_quiz_record['Quiz_Start']);$count_less_played = $user->runQuery("SELECT COUNT(Id) AS Id FROM quiz_record WHERE Quiz_Id=:quiz_id AND Marks<=:marks AND (Quiz_End - Quiz_Start) >:time_diff");$count_less_played->bindparam(":quiz_id",$fetch_quiz_record['Quiz_Id']);$count_less_played->bindparam(":marks",$fetch_quiz_record['Marks']);$count_less_played->bindparam(":time_diff",$time_taken);$count_less_played->execute();$count_less_played_cnt = $count_less_played->fetch(PDO::FETCH_ASSOC);echo $count_less_played_no= $count_less_played_cnt['Id'];輸出：4
2 回答
繁花如伊
TA貢獻2012條經(jīng)驗 獲得超12個贊
試試這個（評論中的解釋）：
$time_taken = strtotime($fetch_quiz_record['Quiz_End']) - strtotime($fetch_quiz_record['Quiz_Start']);
/////--Counting Rows which have less than marks--/////
$count_less_marks = $user->runQuery("SELECT COUNT(Id) AS Id FROM quiz_record WHERE Quiz_Id=:quiz_id AND Marks<:marks");
$count_less_marks->bindparam(":quiz_id",$fetch_quiz_record['Quiz_Id']);
$count_less_marks->bindparam(":marks",$fetch_quiz_record['Marks']);
$count_less_marks->execute();
$count_less_marks_cnt = $count_less_marks->fetch(PDO::FETCH_ASSOC);
$count_less_marks_no= $count_less_marks_cnt['Id'];
/////--Counting Rows which have equal marks and time difference more--/////
$count_more_time = $user->runQuery("SELECT COUNT(Id) AS Id FROM quiz_record WHERE Quiz_Id=:quiz_id AND Marks=:marks AND TIMESTAMPDIFF(SECOND, Quiz_Start, Quiz_End) > :time_diff");
$count_more_time->bindparam(":quiz_id",$fetch_quiz_record['Quiz_Id']);
$count_more_time->bindparam(":marks",$fetch_quiz_record['Marks']);
$count_more_time->bindparam(":time_diff",$time_taken);
$count_more_time->execute();
$count_more_time_cnt = $count_more_time->fetch(PDO::FETCH_ASSOC);
$count_more_time_no= $count_more_time_cnt['Id'];
/////--Add both counts, to get required results--/////
$count_less_played = $count_less_marks_no + $count_more_time_no;
echo $count_less_played;
絕地無雙
TA貢獻1946條經(jīng)驗 獲得超4個贊
首先，你能指定 $user 和 $fetch_quiz_record 是什么嗎？如果沒有這些信息，我會先選擇參考，在您的情況下是 quiz_record，Student_Id 為 4。
$statement = $conn->prepare("
SELECT
Quiz_Start,
Quiz_End,
Marks
FROM quiz_record
WHERE Student_Id = :student_id
");
// in your case $student_id would be 4
$res = $statement->execute([':student_id' => $student_id]);
$selected_quiz = $res->fetch(PDO::FETCH_ASSOC);
$count_statement = $conn->prepare("
SELECT
COUNT(*) as quiz_count
FROM quiz_record
WHERE
Marks < :mark_limit OR
(Marks = :mark_limit AND TIMESTAMPDIFF(SECOND, Quiz_Start, Quiz_End > :seconds_diff))
");
// depending on the datatype of Quiz_Start and Quiz_End
$quiz_start = \DateTimeImmutable::createFromFormat("Y-m-d H:i:s", $selected_quiz['Quiz_Start']);
$quiz_end = \DateTimeImmutable::createFromFormat("Y-m-d H:i:s", $selected_quiz['Quiz_End']);
$diff_in_seconds = abs($quiz_end->getTimestamp() - $quiz_start->getTimestamp());
$count_res = $count_statement->execute([
':mark_limit' => $selected_quiz['Marks'],
':seconds_diff' => $diff_in_seconds
]);
$fetched = $count_res->fetch(PDO::FETCH_ASSOC);
// $count should be the count
$count = $fetched['quiz_count'];
請注意，此代碼應位于您的數(shù)據(jù)層中。如果提供更多詳細信息，我可以詳細介紹。
- 2 回答
- 0 關(guān)注
- 221 瀏覽
添加回答
舉報
0/150
提交
取消