How can I select articles that has top views in a week?

Hi there,

I am here again, 'cause I have a question and hope you will answer me. Please don't let this thread with no comments.

Table "news":

-id
-...
-views
-date

$sql = "SELECT `id`, `views` FROM news ORDER BY `views` DESC LIMIT 0, 20";

PHP:

Now my question is how can I select those articles which have top views in a week?
I am not good at programming date (time) codes, so please help me.

 

$sql = "SELECT `id`, `views` FROM news WHERE date >= start_day_of_current_week ORDER BY `views` DESC LIMIT 0, 20";

Code (markup):

replace start_day_of_current_week with the proper date.

I hope this one helps.

 

I would like a code which I dun have to change.

 

Whats the type for the date column?

 

my date column datetime: date(Y-m-d H:i:s)

 

$now = mktime(0,0,0, date('n'), date('j'), date('Y'));
$day = date('w');
$current_week_start = date('Y-m-d H:i:s', $now-$day*86400);
$sql = "SELECT `id`, `views` FROM news WHERE date >=' . $current_week_start . ' ORDER BY `views` DESC LIMIT 0, 20";

Code (markup):

The code above will always display top users in current week.

I hope this helps.

 

$sql = "SELECT `id` FROM news WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= date ORDER BY `views` DESC LIMIT 0, 20";

PHP:

 

It works great!
Thank u all.