CODEWITHSUNDEEP
phpmysql
PrivateUser
PrivateUser

Reputation: 4524

MySQL where clause to get next week events

My table contains event_start date and event_end date.

I'm confused how to get the next week events. The following variables has the next week start date and end date.

$day = date('w');
$next_week_start = date('Y-m-d', strtotime('+'.(7-$day).' days'));
$next_week_end = date('Y-m-d', strtotime('+'.(7+(6-$day)).' days'));

I would like to get the events that are active on next week.

This is what i have now. Here EVENT_SCHEDULE is the table name and event_date is the event start date. 

$where .= " AND ( ( " . EVENT_SCHEDULE . ".event_date >= '" . $next_week_start . "' AND " . EVENT_SCHEDULE . ".event_date <= '" . $next_week_end . "' ) OR ( " . EVENT_SCHEDULE . ".event_enddate >= '" . $next_week_start . "' AND " . EVENT_SCHEDULE . ".event_enddate <= '" . $next_week_end . "' ) ) ";

Can some one help me with where clause of the query?

Upvotes: 2

Views: 200

Answers (2)

Dobromir Velev
Dobromir Velev

Reputation: 528

Assuming the start and the end variables are correctly calculated you should be able to use

        $where .= "AND (" . 
EVENT_SCHEDULE . ".event_date <= '".$next_week_end."' AND " . 
EVENT_SCHEDULE . ".event_enddate >='" . $next_week_start."') ";

Here is the same code but with the date calculations done on the MySQL server - I am assuming the week is Monday to Sunday

$where .= "AND (" . 
    EVENT_SCHEDULE . ".event_date <= date(Now()+interval 14-date_format(Now(),'%w') day) AND " . 
    EVENT_SCHEDULE . ".event_enddate >= date(Now()+interval 8-date_format(Now(),'%w') day)) ";

Upvotes: 2

bastianowicz
bastianowicz

Reputation: 464

I don't know what datatype your field event_date has, but if it's an Unix-time you should not create a human readable time-string, but an unix timestamp instead. But this depends on your database scheme. you have not provided the actual problem. The where clause you provided looks good to me.

Upvotes: 0

Related Questions