dates & recurring event intervals

BEFOR

There are events in a repeat region that successfully filter by: week, month,year

How can I account for recurring events by (set to recur by Day Week Month Year in recur_eve) in a query with the table:

'event'

event.datestart - events that recurr start here
event.dateend - ending time for recurring events ( not required to enter this value)
event.recur_eve - entered as "week" "month" "year"

When values are present in the url, url variables are being extracted yyyy-mm-dd as:

$year
$month
$day

For use in the queries.
........................................

Present queries:

When no values are present in url, which the page startst on defualt with, searching on:

WHERE DATE_FORMAT(event_eve.datestart_eve,'%Y') = DATE_FORMAT(CURRENT_DATE(),'%Y')

And likewise for month using '%Y%m'

Week is derived both current and from url date variable with 'W' with other script. Currently there is a week interval that's is the only value using the dateend. //Though when accounting for recurring, will probably be used for all.

When values are present in url, extracting from the url variables yyyy-mm-dd

$year
$month
$day

Which are being matched on.

Possible solution:

It seems to me that possible intervals need to be defined on the page for the recurrances by week, month and year. Also, any recurrances may precede any given datestart_eve value being searched on (by year, month, day) and so searching on this colukmn alone will not do.

Any ideas on this?

Najjar

I was not able to understand your question and what you need!
Could you be more specific in you question please?

BEFOR

Najjar

Thank you for your response. I think this will help clarify the post, it's about as concise as I can make it.

My question: what might a query look like that would account for recurring "events".

a) The query is based on url variables in the form of
page.php?date=yyyy-mm-dd and each of these date components are already available on the page using an explode function, as $year, $month, $day This value is being matched on event.datestart column

For an event to recur, a value is set in the following table as either: " Day" " Week" "Month" or "Year" in recur_eve:

'event'

event.datestart - events that recurr start here
event.dateend - ending time for recurring events ( * not required )
event.recur_eve - contains " Day" " Week" "Month" or "Year" or nothing at all

I've been matching on .datestart column with the extracted varaibles for all events except recurring. This won't work for recurring events, since: events that recur, and fall into the time period of .datestart, can and may have been started prior to COLOR=DarkRed[/COLOR] any date url valiable.

As an example: to find all events that occur today, I search on 2005-11-16 but this will exclude recurring events that were set to occur today, since they had a start date prior to 2005-11-16. So I need to find a way to account for not only those events that have a start date of today in the .datestart column, but those that had a prior start date and recur today.

note: events that recur may or may not have and end date in 'dateend' column. If they do not have an end date, they simply recur to the end of the calendar.

Examples of recurring events set by:

"Year"
.datestart

2004-10-16
recurs on:
2005-11-16/ / today, yet it was started a year earler

"Month"
.datestart

2005-10-16

recurs on:
2005-11-16/ / today, yet it was started a month earler

"Week"
.datestart

2005-11-09

recurs on:
2005-11-16 // today, yet it was started a week earler
2005-11-23

"Day"
To set to recur by Day just means they occur every day. It could have been started any prior day -- it would occur today as long as "dateend" is today or after.

My idea was to find a way to set date intervals on the page that can account for prior start dates (yet that would include the url variable date) for the Year Month and Week possible recurrances. But not sure how to execute this.

Najjar

To get the events that recur on the same date you need to select the mm-dd only in you query
example:

$split_date = split('-', $date);
$month_date = $split_date[1] . '-' . $split_date[2];

Your query will be

Select fields from tablename where SUBSTRING('datestart' FROM 6) = '$month_date' "

To get the values for recurring days, months, etc...

You can group by that field "recur_eve"

Check string functions in http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

I hope this helps