SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: booking sytem

  1. #1
    SitePoint Member
    Join Date
    Mar 2005
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy booking sytem

    I am creating a booking system for a hotel and I find it difficult to make the check availability calendar feature work properly.What I succeed so far is the following:

    This is my DB:-
    bookings table

    +---+-------+----+------------+------------+
    |ID _|RoomID|Type|....DateIn....|....DateOut...|
    +---+-------+----+--------+---+------------+
    | 1 -| - 1 -- |- 2 -| 2005-02-01 | 2005-02-04|
    +---+-------+----+------+-----+------------+
    | 2 -| - 2 -- |- 2 -| 2005-02-02 | 2005-02-05|
    +---+-------+----+------+-----+------------+
    | 3 -| - 3 -- |- 2 -| 2005-02-03 | 2005-02-06|
    +---+-------+----+------+-----+------------+
    | 4 -| - 4 -- |- 2 -| 2005-02-06 | 2005-02-07|
    +---+-------+----+------+-----+------------+

    Graphical Representation:
    +----+-----+----+-----+-----+----+----+
    |Days|Mon1|Tue2|Wed3|thu 4|Fri 5|Sat6|
    +----+-----+----+-----+-----+----+----+
    | R. 1| BKD | BKD| BKD | //// | //// | //// |
    +----+-----+----+-----+-----+----+----+
    | R. 2| /////| BKD| BKD | BKD | //// | //// |
    +----+-----+----+-----+-----+----+----+
    | R. 3| /////| //// | BKD | BKD | BKD| //// |
    +----+-----+----+-----+-----+----+----+
    | R. 4| /////| //// | //// | ///// | /// | BKD|
    +----+-----+----+-----+-----+----+----+

    I run my query to check availabilty for dates:
    2005-02-01 to 2005-02-07
    (1st Feb 05 to 7th Feb 05)

    SELECT * FROM bookings WHERE roomtype='2'
    AND datein < '2005-02-07' AND dateout > '2005-02-01'

    i get a message back that no rooms are available to accomdate this booking!
    which is true!

    but i want to be able to let the user know that dates
    2005-02-01 to 2005-01-06 for Room 4 are ok
    and show a graphical rep a bit like this:

    +----+-----+----+-----+-----+----+----+
    |Days|Mon1|Tue2|Wed3|thu 4|Fri 5|Sat6|
    +----+-----+----+-----+-----+----+----+
    | R. 4| /////| //// | //// | ///// | /// | BKD|
    +----+-----+----+-----+-----+----+----+

    so the booker can see where his desired booking is mostly met by room 4!

    I would be very very gretful for any advice.....
    Reginos

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    hmm, i've seen this question before...

    this might help: Available rooms query for room reservation system (site registration may be required, but it's free)

    if you can get to a good library or book store, Joe Celko covers this type of problem in SQL for Smarties
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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