booking system

date problem

[riginosz]

I am trying to do a booking system concerning my project at the university(using PHP &MySQL)I am hasving the following problem:

This is my DB:-booking 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 grateful for any advice-help
Riginos

[ikke]

Sorry for the empty post I thought I know it but i just realized it was nothing :P

[jatar_k]

alright, I dont know your answer but maybe I will just spit out some random thoughts and see where we get

hmm, well as far as I can tell you would then need comparative selects, it doesn't really hit me as the most practical.

so if I have a range from 1 to 7 and I need to be able to say, I dont have that range but this range is close

now I could try something like a max select maybe

something like select the room with the maximum number of days unbooked between the 1st and the 7th, then present the top 3 rooms from that list.

I could also try a select that gives rooms in a row unbooked between the 1st and the 7th

not sure but maybe that helps get your thinking going in the right direction

[riginosz]

Thanks for your advice,I will try to use these thoughts and see the result..

Reginos

[gettopreacherman]

Instead of querying for the actual table, query for the Date. Return anything that is available, not taken.