Take the 2-minute tour ×

Currently I have two tables and the following sql statement which correctly retrieves the items in the events table ordered by their dates in the event_dates table:

SELECT * FROM events, event_dates 
WHERE events.id=event_dates.event_id 
     AND events.preview=0 AND event_dates.start_date>=now() 
ORDER BY event_dates.start_date ASC,event_dates.start_time ASC LIMIT 3

Now I want to add an extra AND to make sure only the events on the next weekend are set. The date column is in a standard mysql date format (YYYY-MM-DD). Got stuck on this bit. Cheers.

share|improve this question
Please do not mix PHP and SQL when possible. You will confuse yourself in the first place. Speaking of SQL queries, use SQL only. PHP has very little with query mening, it does only string manipulation. –  Your Common Sense Sep 9 '10 at 14:07
What if you are querying on a Saturday? Sunday? (Specifics, Bob!) –  jensgram Sep 9 '10 at 14:07
@Col. Shrapnel While, in general, I agree with you on this subject, I believe this is a special case where a simple initial PHP calculation (cf. @spudley's answer) will give a much more readable query (which would have to contain quite a bit of logic to do the same). –  jensgram Sep 9 '10 at 14:10

1 Answer 1

Use PHP strtotime() to get the start and end timestamp of the weekend:

$we_start=strtotime('next saturday');
$we_end=strtotime('next monday')-1;

Then do a sql query to search for timestamps BETWEEN them.

select * from mytable where UNIX_TIMESTAMP(mydatefield) BETWEEN $we_start AND $we_end

Hope that helps.

share|improve this answer
+1 Better than what I had in mind :) Although, the order of arguments for BETWEEN matters (dev.mysql.com/doc/refman/5.0/en/…) so you should probably do $real_we_start = min($we_start, $we_end) and $real_we_end = max($we_start, $we_end) before querying the DB. –  jensgram Sep 9 '10 at 14:04
Of course, thats perfect –  kalpaitch Sep 9 '10 at 14:08
Or, you could: $we_start=strtotime('next saturday'); and $we_end=strtotime('next monday', $we_start)-1;. I'll be quiet now :) –  jensgram Sep 10 '10 at 5:33
I's better use jensgram code, since if we are in the middle of sunday,strtotime('next monday')-1 will equal tomorrow and not next week's monday. 2 cent –  Alekc Nov 8 '11 at 14:31

Your Answer


