I’m trying to build a calendar of reservations and are now stuck in an equation I do not get together.
The person who administers the calendar can create their own periods of the year when visitors have the opportunity to book. And these periods should now be synchronized with the calendar.
The periods are stored in the following columns in the database:
[from_month] - [from_day] - [to_month] - [to_day]
The calendar is built in PHP. And every day should be checked against the database if it is a valid day to book, according to the periods that are saved. So that date is within one period.
That’s where I got stuck now. I thought it would just be to write:
WHERE from_month <= '".$this_monthnr."'
AND from_day <= '".$this_daynr."'
AND to_month >= '".$this_monthnr."'
AND to_day >= '".$this_daynr."'
But it does not work if, for example, the “from date” is 08-01 and the “end date” is 02-01. Then it becomes:
WHERE 08 <= '".$this_monthnr."'
AND 01 <= '".$this_daynr."'
AND 02 >= '".$this_monthnr."'
AND 01 >= '".$this_daynr."'
It becomes crazy as it’s a year-end there between this dates, which makes the ending date value becomes lower than the start date. And the parameter [year] should not be included since these periods should be the same year after year, so it is not necessary.
Would be grateful if some genius wanted to help me. And if there is anything you have questions or do not understand what I mean, just ask.
The goal is that the SQL query should return the row represent the period in which the selected calendar day is within, if it exists. Otherwise, it should not return anything at all.
Why not save them as full DATE types, and then do
WHERE $thisdate BETWEEN from_date AND to_date;
PS: Flagging this for moving to the mySQL forum, so i can stand in the fire of Rudy’s steely gaze once again 
But I can’t save the years values in the database, since the period is the same year after year. Calendar administrator should not need to add new periods each year.
Just because the value is there, doesnt mean you have to use it.
“WHERE '2011-”.$thismonth.“-”.$thisday.“’ BETWEEN from_date AND to_date”;
would work: since you dont care about year, defining all dates to be in year 2011 works.
That said, i’m fairly sure you could store them as integers (MMDD), and the between would still work.
No it doesn’t. If from_date is 08-01, and I save it like 2011-08-01. And to_date is 02-01, and I save it like 2011-02-01. To_date is still lower than from_date.
so instead save it as 2011-08-01 to 2011-12-31 (801 and 1231) and 2011-01-01 (101) to 2011-02-01 (201). Same effect.
You mean I have to set a rule that the period dates has to be within the same year. Not nice solution.
No, Just that your program needs to be able to interpret a request for a period overlaping the end of the year and split the time into two entries. A decent solution.
So, you propose two new columns in periods table. How should the SQL query look like then?
I propose two columns period. But even your old query would work if you split the entries.
No, I don’t want one period (in users eye) to be split up to two periods after saving. It have to still look like the period that was set by the admin.
Right. At this point then I turn you over to the MySQL gods, because obviously my logic doesnt work for you.