MYSQL retrieve results where date = next weekend

Question

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.

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.