andreaem andreaem - 4 months ago 4
SQL Question

MySQL: Select and return articles where date between two weeks

I'm trying to display most recent visited articles in top of my page, but my query fail even if I put in phpmyadmin.

Here is my query, hope someone can find a solution:

SELECT `slug`,`title`,`image` FROM `article` WHERE DATE(`date`) = DATE_ADD(CURDATE(), INTERVAL 16 DAY) ORDER BY `article`.`view_count` DESC LIMIT 0 , 5


I'll select slug, title and image between 2 week ordered by view count, but the query return 0 elements.

Data format in the table is YYYY-MM-DD.

Thanks to all

Answer

You're trying to get a date in the future, and you're trying to find articles on that exact date:

select DATE_ADD(CURDATE(), INTERVAL 16 DAY);
+--------------------------------------+
| DATE_ADD(CURDATE(), INTERVAL 16 DAY) |
+--------------------------------------+
| 2016-08-12                           |
+--------------------------------------+

Use DATE_SUB to look in the past, and >= to get that date and forward:

DATE(`date`) >= DATE_SUB(CURDATE(), INTERVAL 16 DAY)