Best DATE based SELECT method in PHP / MYSQL

Question

I have a database table with a DATETIME column called "entry_date", for example ...

ID entry_date 1 2012-10-13 2 2012-10-14 3 2012-10-15 4 2012-10-20 5 2012-10-20 6 2012-10-21 

What is the best way to repeat the DATE-based result in PHP / MYSQL, so I only get results this week, so 2012-10-15> 2012-10-21, but obviously I can’t just change the code because of which days, every day :), since you would do it in php, so tomorrow the condition for this week would be 2012-10-16> 2012-10-22

Answer 1

Use today

 where date(`entry_date`) = curdate() 

This month

 where month(`entry_date`) = month(curdate()) and year(`entry_date`) = year(curdate()) 

this week

 WHERE `entry_date` BETWEEN (CURDATE() - Interval DAYOFWEEK(CURDATE()) day) AND (CURDATE() - Interval DAYOFWEEK(CURDATE()) day + Interval 1 week) 

The above query is helpful for understanding. But this is not very good for performance. See the following. This is much better.

 SET @s:=(CURDATE() - Interval DAYOFWEEK(CURDATE()) day) SET @e:=(CURDATE() - Interval DAYOFWEEK(CURDATE()) day + Interval 1 week) SELECT .... WHERE `entry_date` BETWEEN @s AND @e 

Here, the DAYOFWEEK function returns an integer starting with 1 = Sunday. Thus, your weeks will be considered Saturday as the start date. If you want Monday add + Interval 2 day at intervals.

This can also be simplified as follows. But he does not support a single day, like the first day of the week. Thus, it is not tolerated for the whole culture.

 where yearweek(`entry_date`) = yearweek(curdate())