www.webdeveloper.com
Results 1 to 8 of 8

Thread: Select Query

  1. #1
    Join Date
    Oct 2008
    Location
    Leeds, UK
    Posts
    68

    Select Query

    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 { 

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,359
    I think you want one of those ANDs to be an OR.
    PHP Code:
    $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

    eBookworm.us

  3. #3
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,359
    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

    eBookworm.us

  4. #4
    Join Date
    Oct 2008
    Location
    Leeds, UK
    Posts
    68
    Thanks NogDog I'm just having a go at it now I'll let you know in a bit

  5. #5
    Join Date
    Oct 2008
    Location
    Leeds, UK
    Posts
    68

    Thumbs up

    Thank you NogDog. What I can test so far that has worked. Really appreciate your input.
    Thanks
    Zed

  6. #6
    Join Date
    Oct 2008
    Location
    Leeds, UK
    Posts
    68
    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

  7. #7
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,359
    Quote Originally Posted by zed420 View Post
    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

    eBookworm.us

  8. #8
    Join Date
    Oct 2008
    Location
    Leeds, UK
    Posts
    68
    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles