pulling date from mysql returns all days in the future [duplicate]

This question already has answers here: why isn't this pulling data from 7 days back? (3 answers) Closed 9 years ago.

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.