T.Nosek T.Nosek - 1 month ago 15
MySQL Question

Wordpress PostViews plugin sorted by date

I am currently helping my friend to optimise her website. It is built on a wordpress theme and there is a plugin ViewsPost used to show only the most viewed posts. What I need to do is to rewrite the SQL so it gets only the most viewed posts in last week. In the WHERE condition there is only said to get those posts which date is < than the current date. How to edit it?

<?php
}


function gen21_display_most_viewed_posts($smallthumbs = true) {

global $wpdb;
/* taken from wp-postviews.php by Lester Chan http://lesterchan.net/portfolio/programming/php/ */
$favourites = $wpdb->get_results("SELECT DISTINCT $wpdb->posts.*, (meta_value+0) AS views FROM $wpdb->posts LEFT JOIN $wpdb->postmeta ON $wpdb->postmeta.post_id = $wpdb->posts.ID WHERE post_date < '".current_time('mysql')."' AND post_type = 'post' AND post_status = 'publish' AND meta_key = 'views' AND post_password = '' ORDER BY views DESC LIMIT 4");

if (!$favourites) return false;

foreach ($favourites as $fav) { ?>
<div class="col-lg-3 col-md-4 col-sm-6 col-xs-12 item">
<a class="front-item" href="<?php echo get_permalink($fav); ?>">
<?php

echo get_the_post_thumbnail($fav, 'thumbnail');
/*
if ($smallthumbs) {
echo get_the_post_thumbnail($fav, 'small');
} elseif ( has_post_thumbnail($fav) ) {
if (rand(0,1) == 1) {
echo get_the_post_thumbnail($fav, "medium");
}
else {
echo get_the_post_thumbnail($fav, "small");
}
} */ ?>

Answer Source

Change the post_date condition:

post_date BETWEEN DATE_SUB(NOW(), INTERVAL 1 WEEK) AND NOW() -- or
post_date BETWEEN (DATE_SUB(NOW(), INTERVAL 1 WEEK)) AND DATE(NOW())

This subtracts a week from the current date, and finds posts where post_date is between the date obtained and the current date

Whole query is:

$favourites = $wpdb->get_results("SELECT DISTINCT $wpdb->posts.*, (meta_value+0) AS views FROM $wpdb->posts LEFT JOIN $wpdb->postmeta ON $wpdb->postmeta.post_id = $wpdb->posts.ID WHERE post_date BETWEEN DATE_SUB(NOW(),INTERVAL 1 WEEK) AND NOW() AND post_type = 'post' AND post_status = 'publish' AND meta_key = 'views' AND post_password = '' ORDER BY views DESC LIMIT 4"); // or
$favourites = $wpdb->get_results("SELECT DISTINCT $wpdb->posts.*, (meta_value+0) AS views FROM $wpdb->posts LEFT JOIN $wpdb->postmeta ON $wpdb->postmeta.post_id = $wpdb->posts.ID WHERE post_date BETWEEN (DATE_SUB(NOW(),INTERVAL 1 WEEK)) AND DATE (NOW()) AND post_type = 'post' AND post_status = 'publish' AND meta_key = 'views' AND post_password = '' ORDER BY views DESC LIMIT 4");