Query Jetpack Wordpress Stats List

Question

I want to show the most viewed post from the week in a widget.

<?php

function filter_where($where = '') {
    //posts in the last 30 days
    $where .= " AND post_date > '" . date('Y-m-d', strtotime('-30 days')) . "'";
    return $where;
}
add_filter('posts_where', 'filter_where');

query_posts('post_type=post&posts_per_page=4&orderby=rand&order=DESC');

while (have_posts()): the_post(); ?>

<li style="
    margin-bottom: 5px;
    background: transparent url(http://i.imgur.com/6ngfnNo.png) repeat scroll center top;
    padding: 15px;
    list-style-type: none;
    width: 500px;
    margin: 10px 0px 0px 10px;
"><a href="<?php the_permalink(); ?>" title="<?php printf(esc_attr('Permalink to %s'), the_title_attribute('echo=0')); ?>" rel="bookmark"><span class="tptn_title" style="
    color: #fff;
    text-transform: uppercase;
    font-family: 'Montserrat-Bold', sans-serif;
"><?php the_title(); ?></span></a></li>

<?php
endwhile;
wp_reset_query();
?>

I'm using this one, because Jetpack doesn't allow me to query the most viewed post.

Answer 1

You can try this method:

<?php 
// creating the post views DB table
add_action('wp_head', 'create_post_views_table');
function create_post_views_table() {
    global $wpdb;
    // our table name
    $table_name = $wpdb->prefix . "post_views";
    // SQL for creating the table
    $sql = "CREATE TABLE IF NOT EXISTS $table_name (
            meta_id int(11) NOT NULL AUTO_INCREMENT,
            post_id int(11) NOT NULL,
            date_viewed datetime NOT NULL,
            PRIMARY KEY (meta_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;";
    // we're using functions from the WP admin
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    // executing the SQL
    dbDelta($sql);
}
?>

<?php 
// Add this function when user sees the post / most likely in single.php or loop-single etc. 

// recording each post view
function update_post_views($postID) {
    global $wpdb;
    // our table name
    $table_name = $wpdb->prefix . "post_views";
    // the current time 
    $date = date('Y-m-d H:i:s');
    // the SQL for inserting the view
    $sql = "INSERT INTO $table_name (post_id,date_viewed) VALUES ($postID, '$date')";
    // executing the SQL
    $wpdb->query($sql);
}
?>

<?php 
// get the most popular posts for the last X days
function get_most_popular_posts($count = 30, $interval = '') {
    global $wpdb;
    $where = '';
    // adding WHERE clause to specify the date interval 
    if ($interval) {
        $where = "
        WHERE date_viewed > ( NOW() - INTERVAL $interval DAY)
        ";
    }
    // building SQL
    $sql = "SELECT post_id, COUNT(post_id) as count
            FROM {$wpdb->prefix}post_views
            {$where}
            GROUP BY post_id
            ORDER BY count DESC
            LIMIT $count
            ";
    // fetching the posts
    $results = $wpdb->get_results($sql);
    return $results;
}
?>