php - 查询在 phpmyadmin 中工作但不在脚本中

Question

我正在制作一个预订系统，因此我需要能够在让用户预订之前检查是否有可用的时间段(我的功能还在前后添加了 1 个时间段以检查是否包含旅行时间等)

function timeSlotAvailable($date, $time){

    $timeslots = array($time - 1, $time, $time + 1);
    $slots = join(',',$timeslots); 

    $STH = $this->database->prepare("SELECT COUNT(*) FROM bookings WHERE bookings.date = :bdate AND bookings.slot IN (:ids)");
    $STH->execute(array(":bdate"=>$date, ":ids"=>$slots));
    $data = $STH->fetchColumn();
    return "checking date:".$date." for slots ".$slots." the count is ".$data;

}

输出

checking date:02/15/2014 for slots 3,4,5 the count is 0

现在在 bookings 中，时间段 4 上有一个用于该日期的时间段。然后我在 phpmyadmin 中尝试这个查询

SELECT COUNT(*) FROM bookings WHERE bookings.date = "02/15/2014" AND bookings.slot IN (3,4,5)

本质上是相同的查询(提交相同的变量)但返回正确的响应 1。这让我觉得我的代码有一些我看不到的错误。

Answer 1

问题是:

function timeSlotAvailable($date, $time){

$timeslots = array($time - 1, $time, $time + 1); 
$slots = join(',',$timeslots); //bad

$STH = $this->database->prepare("SELECT COUNT(*) FROM bookings WHERE bookings.date = :bdate AND bookings.slot IN (:ids)");
$STH->execute(array(":bdate"=>$date, ":ids"=>$slots)); //here is the problem
$data = $STH->fetchColumn();
return "checking date:".$date." for slots ".$slots." the count is ".$data;

}

这样你的查询看起来像:

SELECT COUNT(*) 
FROM bookings WHERE bookings.date ='02/15/2014' 
AND bookings.slot IN (3) //Just took one, oops

当然答案是零。

以这种方式更改您的代码:

function timeSlotAvailable($date, $time){

$timeslots = array($time - 1, $time, $time + 1); 

$STH = $this->database->prepare("SELECT COUNT(*) FROM bookings WHERE bookings.date = :bdate AND bookings.slot IN (:ids)");
$STH->execute(array(":bdate"=>$date, ":ids"=>$timeslots)); 
$data = $STH->fetchColumn();
return "checking date:".$date." for slots ".$slots." the count is ".$data;

}

或者这样:

function timeSlotAvailable($date, $time){

$timeslots = array($time - 1, $time, $time + 1); 
$slots = join(',',$timeslots); 

$STH = $this->database->prepare("SELECT COUNT(*) FROM bookings WHERE bookings.date = :bdate AND bookings.slot IN (".$slots.")");
$STH->execute(array(":bdate"=>$date)); 
$data = $STH->fetchColumn();
return "checking date:".$date." for slots ".$slots." the count is ".$data;

}