查询在phpmyadmin中工作，但不在脚本中

Question

I am making a booking system and therefor I need to be able to check to see if timeslots are available before letting a user book (my function also adds 1 timeslot before and after to check for to cover traveling time etc.)

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;

}

outputs

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

Now in bookings There is a slot that is being used for that date on time slot 4. I then try this query in phpmyadmin

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

Which is essentially the same query ( same variables being submitted ) but returns with the correct response of 1. This makes me think there is something wrong with my code that I cannot see.

Answer 1

Here is the problem:

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;

}

This way your query looks like:

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

And of course the answer is zero.

Change your code this way:

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;

}

Or this way:

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;

}