Need help with A query

[WizyWyg]

I can't for the life of me figure this out.
I have a table that lists events and using the MysQl Date format (ie 0000-00-00 ). This determines the date the event is happening on.

Im guessing its a mix of php and Mysql queries.

Even if today is x day of the week (ie tue,wed, fri) I want to be able to show the events of the Monday (if there are any). How can I construct a query to show events on Monday no matter what day of the week it is?

How can I show the events of the following Monday (, say if today is Wed)?

 

[tlubi]

Hi, Wiz,

how can you show events from Monday?

Use the 'dayofweek()'-function

select bla bla
from table
where dayofweek(event)=2
;

With this you get ALL events that occured on any Monday in the past and will occur in the future (if registered).

To show events from next Monday only you can use the to_day()-function. It translates your starting date 'now()' into an integer. The result-date must be within 7 days starting from now() and it must be the dayofweek()=2, if you like mondays. I don't like Mondays at all, but that's another point which does not depend on mySQL...;-)

select bla bla
from table
where dayofweek(event)=2
and to_days(event)-to_days(now()) between 1 and 7
;
Changing the "2" into "3" or "4" will show you Tuesdays or Wednesdays events. Changing the between-days 1 and 7 int 8 and 14 will show you week after next week and so on (better would be 'between 1+(($num_weeks-1)*7) and 7+(($num_weeks)-1)*7), so you can provide the wished number of weeks in a variable... just play around a little...)

I hope this will answer some of your questions...
Best Regards
Thomas

 

[WizyWyg]

Hi thanks,

Since its for current week, How would i show only for that monday/tues / etc events? Im going to create two displays ( one for current and one the following week). So "past" events do not need to be shown

 

[apatterno]

If you only need it for a certain Monday, then simply calculate the actual date of that certain monday (using whatever programming language you're using. I'm unfamiliar with PHP but I'm sure it could be done.

SELECT ............... WHERE event_date= [an exact date]

 

[WizyWyg]

This will be dynamically produced so I can't use "exact date".

I know it can be done in php, but I dont know why its not showing anything (returning 0 records) when I do happen to have events scheduled

 

[r937]

according to the mysql docs, DAYOFWEEK() returns 1=sunday, 2=monday, ... 7=saturday

so if today is tuesday, DAYOFWEEK returns 3

now, let's subtract that number from today, which would take us back to last saturday, then add 2 to get to monday

if it's friday, DAYOFWEEK is 6, so subtracting 6 takes us back to the previous saturday, then add 2 as before

so the monday date of the current week is given by

FROM_DAYS( TO_DAYS(CURRENT_DATE)
- DAYOFWEEK(CURRENT_DATE) + 2 )

for the following monday, do something similar, eh




rudy

http://r937.com/