I'm a WordPress (PHP) developer, however I have not done a lot of complex MySQL queries. I am working on an events website and want to create filters: User should be able to filter events by the following criteria: TODAY, TOMORROW, THIS WEEKEND, NEXT 7 DAYS, CHOOSE YOUR DATES.
Meta data used to filter events is below and can be found in the post meta db table in the meta_key column.
start_date
end_date
times
recurring_event
days
sold_out
cancelled
This is how the table looks like:
id post_id meta_key meta_value
1 12 start_date 20140923
2 22 days a:4:{i:0;s:6:"monday";i:1;s:9:"wednesday";i:2;s:6:"friday";i:3;s:8:"saturday"}
3 12 end_date 20141003
4 78 recurring_event 0
5 34 times 24 Hours
6 12 days a:2:{i:0;s:6:"monday";i:1;s:7:"tuesday";}
7 67 start_date 20140906
8 45 end_date 20141108
What MySQL queries can I use to get events for Today, Tomorrow, Weekend and 7 days.
I do not know SQL enough, then, for this case, I would caculate the date with PHP and then make the query with SQL.
These are the pages that helped me for the following :
http://php.net/manual/fr/function.date.php
http://php.net/manual/fr/function.mktime.php
1) For today, no calculation, just get the date of today and make the query :
<?php
$today = date('Y-m-d',mktime());
$result = mysqli_query($connect, 'SELECT * FROM events WHERE start_date = "'.$today.'"');
?>
2) Tomorow, calculate tomorow date :
<?php
$tomorow = date('Y-m-d',mktime(0, 0, 0, date("m") , date("d")+1, date("Y")));
$result = mysqli_query($connect, 'SELECT * FROM events WHERE start_date = "'.$tomorow.'"');
?>
3) 7 days later, calculate 7 days later date :
<?php
$day7 = date('Y-m-d',mktime(0, 0, 0, date("m") , date("d")+7, date("Y")));
$result = mysqli_query($connect, 'SELECT * FROM events WHERE start_date = "'.$day7.'"');
?>
3) Week end, calculte when week end comes : For this one, I cannot write it so quickly, sorry.
Explanations :
date('Y-m-d',mktime()); gives 2014-09-04 in $today.
Because mktime() is empty, so mktime() is based on the server time, no argument.
Y => year like ####
m => month like ##
d => day like ##
date('Y-m-d',mktime(0, 0, 0, date("m") , date("d")+1, date("Y"))); gives 2014-09-05
This time we gave arguments to mktime(), 0 hour, 0 minutes, 0 second, 09, 04+1, 2014.
date("m") = 09
date("d")+1 = 04+1 = 05
date("Y") = 2014
I hope this might help you. I'm sorry, but I don't know how put PHP in Wordpress. Nils.
For the weekend and next 7 days you may have to mix sql and php
Today
SELECT * from tablename where start_date=CURDATE();
Tomorrow
SELECT * from tablename where start_date = CURDATE()+INTERVAL 1 DAY;
For Weekend you have to find the weekend dates first.
using
SELECT DAYOFWEEK
you can find current day . So if you have an array , match with it and add how many days to reach Saturday and sunday. I dont know any other easy way
For next 7 days
SELECT * from tablename where start_date >= CURDATE() and
start_date=< CURDATE()+INTERVAL 7 DAY;
Use the tutorial and try yourself :-)
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_curdate