Hi All
I've been struggling with this query for past 4 days now see If anyone can help me please. A typical day's diary starts from 0800 till 2100, normally you would book an hr but just to test the script I booked 0800 to 2100 so that means all day has been booked NO other booking can be taken for this day if I DO NOT select the varibles 8 & 9 IT WILL book again and again . ????? why why????? How can I avoid that, s_time and e_time are numbers in database, this is the query I'm using
Thanks Zed
PHP Code:
$query = "SELECT b_id, COUNT(*) AS Cnt
2.FROM booking
3.WHERE request_date='$request_date'
4.AND e_time > '$s_time'
5.AND s_time < '$e_time'
6.GROUP BY b_id";
7. $result = mysql_query($query);
8. $row = mysql_fetch_assoc($result);
9. if($row['Cnt'] > 0){
10. error_message('Sorry, this time is already booked. Please choose another');
11. } else {
$query = "SELECT b_id, COUNT(*) AS Cnt FROM booking WHERE request_date='$request_date' AND ( e_time > '$s_time' OR s_time < '$e_time' ) GROUP BY b_id";
It would also be a good idea to check the return value from mysql_query() to make sure the query was parsed and processed OK by MySQL. If not, or if still getting unexpected results, echo $query (or error_log() it) to verify if each variable used in it has the value you think it should.
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation
Actually, upon further review, I think you might want to use some BETWEENs:
PHP Code:
$query = "SELECT b_id, COUNT(*) AS Cnt
FROM booking
WHERE request_date='$request_date' AND
(
'$s_time' BETWEEN s_time AND e_time OR
'$e_time' BETWEEN s_time AND e_time
)
GROUP BY b_id";
Or, if you want to allow the end time of one event to be equal to the start time of the next, you might need to use your < and > in something like:
PHP Code:
$query = "SELECT b_id, COUNT(*) AS Cnt
FROM booking
WHERE request_date='$request_date' AND
(
($s_time >= s_time AND $s_time < e_time) OR
($e_time > s_time AND $e_time <= e_time)
)
GROUP BY b_id";
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation
Small problem after rigorous testing I found there is a small glitch e.g. Let’s say if I book from 10:00 to 13:00 and someone tries to book it from 08:00 to 21:00 (all day) IT WILL BOOK. I have tried to sort it but failing miserably, anyone with any thoughts
Thanks
Zed
Small problem after rigorous testing I found there is a small glitch e.g. Let’s say if I book from 10:00 to 13:00 and someone tries to book it from 08:00 to 21:00 (all day) IT WILL BOOK. I have tried to sort it but failing miserably, anyone with any thoughts
Thanks
Zed
My brain is fried right now, but this seems to make sense to me at the moment. Definitely no guarantees, though.
Code:
...
WHERE
request_date = '$request_date' AND
'$s_time' < e_time AND
'$e_time' > s_time
...
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation
Thanks NogDog for all your help I think I'm making this too complex for myself I might have to revise the whole struchure of design. This is my sixth day at this and not got what I wanted.
Zed
Reply With Quote
Originally Posted by zed420
Bookmarks