I have a SQL query that get all events ordering by Startdate, and i define startday as $today.

but this query didn't take events that start yestarday and that endday is tomorow.

how can i make my query to use BETWEEN ?

my query

SELECT * FROM wp_posts, `wp_mec_dates` AS mecd, wp_icl_translations WHERE wp_posts.ID = mecd.post_id and post_status='publish' AND wp_icl_translations.language_code='$lang' AND dstart>'$startday' and  wp_posts.ID = wp_icl_translations.element_id ORDER BY dstart LIMIT 0,6

startdate is $startday = date("Y-m-d");

EDIT : with documentation & help, i'm here now :

SELECT * FROM wp_posts, wp_mec_dates AS mecd, wp_icl_translations WHERE wp_posts.ID = mecd.post_id and post_status='publish' AND wp_icl_translations.language_code='$lang' AND ($startday BETWEEN dstart AND dend)AND  wp_posts.ID = wp_icl_translations.element_id ORDER BY dstart LIMIT 0,6

but it return 0 results. Any help will be apreciated !

I have a SQL query that get all events ordering by Startdate, and i define startday as $today.

but this query didn't take events that start yestarday and that endday is tomorow.

how can i make my query to use BETWEEN ?

my query

SELECT * FROM wp_posts, `wp_mec_dates` AS mecd, wp_icl_translations WHERE wp_posts.ID = mecd.post_id and post_status='publish' AND wp_icl_translations.language_code='$lang' AND dstart>'$startday' and  wp_posts.ID = wp_icl_translations.element_id ORDER BY dstart LIMIT 0,6

startdate is $startday = date("Y-m-d");

EDIT : with documentation & help, i'm here now :

SELECT * FROM wp_posts, wp_mec_dates AS mecd, wp_icl_translations WHERE wp_posts.ID = mecd.post_id and post_status='publish' AND wp_icl_translations.language_code='$lang' AND ($startday BETWEEN dstart AND dend)AND  wp_posts.ID = wp_icl_translations.element_id ORDER BY dstart LIMIT 0,6

but it return 0 results. Any help will be apreciated !

• sql

2 Answers 2

found the solution <3

Used WHERE '$startday' BETWEEN dstart AND dend

plus : OR dstart >= '$startday'

SELECT * FROM wp_posts, wp_mec_dates AS mecd, wp_icl_translations WHERE wp_posts.ID = mecd.post_id and post_status='publish' AND wp_icl_translations.language_code='$lang' AND ('$startday' BETWEEN dstart AND dend OR dstart >= '$startday') AND  wp_posts.ID = wp_icl_translations.element_id ORDER BY dstart LIMIT 0,6

Try this

WHERE dstart BETWEEN '$startdate' AND '$enddate'