[ExtremeGuy]

I am looking for some help in the formation of a MySQL query.

I have a site that stores reservation dates (ie. arrive: 2004-08-14, depart 2004-08-21). What I am trying to do is create a calendar to show what dates have been reserved already.

Right now, my SQL query statement is (assuming the month that I want is August 2004): SELECT dayofmonth(rv_arrive) as aDate, dayofmonth(rv_depart) as dDate, (TO_DAYS(rv_depart) - TO_DAYS(rv_arrive)) as stay FROM rt_rv WHERE year(rv_arrive)=2004 AND month (rv_arrive)=08

At first this looks fine, however, if I have a reservation that starts in the month of July (07) and ends in August (08), that will not show up because my query statement is just looking for dates between Aug 1 and Aug 31 ONLY. This gets even worse if the arrival day is before August 1 and the departure day is after Aug 31.

So my question is: How do I write a query to capture ALL of the reservations that are to be shown in my month of August? Should I be storing my dates differently?

Thanks!

[infinitylimit]

You will need to look more like this

here is pseudo code for it

Code:

IF THERE IS A depart date between BEGIN OF MONTH to END OF MONTH
  IF YES THEN
             Look for arrival date in database
             IF ARRIVAL DATE is before BEGIN OF MONTH
                   Mark days starting with begin of month
             ELSE
                   Mark days from arrival day to departure date
IF THERE IS A arrival date between BEGIN OF MONTH to END of MONTH
   IF YES THEN
              Look for depart date in database
              IF DEPRATE DATE is after END OF MONNTH 
                   Mark days to END OF MONTH
              ELSE
                    Mark days from arrival day to deprature date

You will want to do a little checking to make sure if you already wrote a reseversation down you don't do it again but that should be all you need. I know it isn't SQL or PHP but this going to be a mixture of the two and you seem to have a decent grasp of SQL and I'm sure you could figure out the rest.