I can't seem to figure out how to only pull reminders for the current week, any advice would be appreciated.
$query = ("SELECT * FROM contacts WHERE reminder >= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) ORDER开发者_开发技巧 BY firstname") or die ('Error: ' .mysql_error());
Very naive approach:
WHERE YEARWEEK(reminder) = YEARWEEK(NOW())
. It works, but it won't use an index on reminder
(I believe there is an index). Also, YEARWEEK
will be executed for each record. The right way is to use query like WHERE reminder >=[start_date] and reminder <= [end_date]
(or reminder BETWEEN [start_date] AND [end_date]
) where [start_date] and [end_date] represent the first and last day of the current week respectively. I think it's easier to calculate these dates on client side, but it's also possible to write a function on mysql that returns desired date.
精彩评论