php - 如何比较php中mysql查询的时间？

Question

我有一个 php 脚本。

$id=$_GET['id'];
$endTime=$_GET['endTime'];

    $stmt = $con->prepare("SELECT candidateId from Attendence where candidateId=? and startTime>cast(? as time)");    //This is the problem line
    $stmt->bind_param("ds", $id,$endTime);

 //executing the query 
 $stmt->execute();


 //binding results to the query 
 $stmt->bind_result($var);

 $response = array(); 
 $response['success']=false;

 //traversing through all the result
 if($stmt->fetch())
 {

 $response['success']=true;
 $response['cand']=$var;
 }


 //displaying the result in json format 
 echo json_encode($response);

当我在 url 栏中输入这个时，这工作正常:

http://arnabbanerjee.dx.am/checkAttendenceValidity.php?id=12100116050&endTime='14:30:00'

但是当我将第 3 行更改为

$stmt = $con->prepare("SELECT candidateId from Attendence where candidateId=? and startTime<cast(? as time)");

并输入这个网址:

http://arnabbanerjee.dx.am/checkAttendenceValidity.php?id=12100116050&endTime='18:30:00'

显示{"success":false}

这是出勤表中的条目。

12100116050(candidateId)  15:30:00(startTime)  2019-02-05   OS   present   17:30:00(endTime)

谁能告诉我如何解决这个问题？

Answer 1

去掉 endTime 两边的单引号

http://arnabbanerjee.dx.am/checkAttendenceValidity.php?id=12100116050&endTime=18:30:00

否则查询字符串将展开为:

startTime<cast("'18:30:00'" as time)

转换结果为NULL。