How to show the most viewed posts for a certain period?

Question

WordPress

Hello.
The task seems to be simple, but still I don’t know where to look ..

You need to display the top entries by views for the last 7 days, for example, but not the entries that were added to the site in 7 days, but the entries that collected the most views in 7 days.
I tried to collect views in the meta field of the post, but in this case everything is in a bunch for the whole time, how would I change this thing so that you can pull data for a certain period.
Thank you.
UPD. Without plugins of course)

Answer 1

The task seems simple, but still I don’t know where to look

If you really think it's simple, then look, for example, at this, I believe that you will figure it out quickly :)
Create a table with your prefix:

CREATE TABLE {$prefix}post_views (
  post_id bigint(20) NOT NULL,
  entry_day datetime NOT NULL,
  num_views int NOT NULL,
  PRIMARY KEY  (post_id,entry_day),
  KEY idx_pv_ed (entry_day)
);

Take the handler class:

[ Class for most viewed posts ]

class My_Most_Viewed_Posts {
    protected $errors = null;
    protected $notices = null;
    protected $debug = false;

    # This just sets up the errors, notices, and debug variables able
    function __construct($debug = false) {
        $this->clear_all();
        if ( $debug ) {
            $this->debug = true;
        }
    }

    # This lets you add notices about what is going on so you can display them later
    protected function add_notice($msg = '') {
        if ( is_array($msg) || is_object($msg) ) {
            $this->notices[] = print_r($msg, true);
        } else {
            $this->notices[] = $msg;
        }
    }

    # Get an array of the most viewed posts as an array of the form ( post_id => number_of_views )
    # - $oldest_date : should be of the form YYYY-MM-DD
    # - $newest_date : should be of the form YYYY-MM-DD ( if null is provided, it will default to the current day )
    # - $num_to_get  : the number of results to return (e.g. 4 highest by default)
    function get_highest_posts_in_range( $oldest_date, $newest_date = null, $num_to_get = 4 ) {
        # Set up our return value
        $found = array();

        # Debug notice noting what was passed in
        if ( $this->debug ) {
            $this->add_notice( "Starting get_highest_posts_in_range( '{$oldest_date}', '{$newest_date}', {$num_to_get} )" );
        }

        # Do all real processing in a try / catch block to ensure can log problems instead of just throwing errors
        try {
            # Get a handle to the global database connection
            global $wpdb;

            # Verify that the provided $oldest_date is 10 characters long
            if ( 10 !== strlen($oldest_date) ) {
                throw new Exception("Parameter 1: Must be of the form YYYY-MM-DD", 10001);
            } else {
                # Appends time to the oldest date to make any between query include everything on the date
                $oldest_date .= ' 00:00:00';
            }

            # Initialize the $newest_date variable OR validate that any non null value could be in the right format
            if ( is_null( $newest_date ) ) {
                $newest_date = date('Y-m-d') . ' 23:59:59';
            } else if ( 10 !== strlen( $newest_date ) ) {
                # Error
                throw new Exception("Parameter 2: Must be null or in the form YYYY-MM-DD", 10002);
            } else {
                # Appends time to make any between query contain everything entered that day
                $newest_date .= ' 23:59:59';
            }

            # Make sure that the number of records to get is sane
            $num_to_get = (int) $num_to_get;
            if ( $num_to_get <= 0 ) {
                throw new Exception("Parameter 3: Must be a positive integer", 10003);
            }

            # Debug message to note the final values of provided variables after validation
            if ( $this->debug ) {
                $this->add_notice( "After Validation - Oldest: {$oldest_date}, Newest: {$newest_date}, Number to get: {$num_to_get}" );
            }

            # Build our query
            # It will return the post_id and number of views (as views_in_period) for the most viewed items in the given date range
            $query = <<<SQL
SELECT post_id, SUM(num_views) as views_in_period
 FROM {$wpdb->prefix}post_views 
 WHERE entry_day BETWEEN %s AND %s
 GROUP BY post_id
 ORDER BY views_in_period DESC
 LIMIT %d
SQL;

            # Add our variables to the mysql query above safely
            $query = $wpdb->prepare( $query, $oldest_date, $newest_date, $num_to_get );

            # Debug message to note what the final prepared query is
            if ( $this->debug ) {
                $this->add_notice( "Prepared Query:<br />{$query}" );
            }

            # Run the query and get the results
            $results = $wpdb->query( $query );
            if ( false === $results ) {
                $error = $wpdb->last_error;
                throw new Exception("Bad Database query: {$query}, DB Error: {$error}", 10004);
            } else if ( 0 < count($results) ) {
                # There is at least one result. Add a debug message to show what the results are
                $this->add_notice("Results detected:");
                $this->add_notice( $results );

                # Cycle through each result and add it to our return value
                foreach ( $results as $row ) {
                    $found["{$row['post_id']}"] = $row['views'];
                }
            } else if ( $this->debug ) {
                # No results returned, add a notice if in debug mode
                $this->add_notice("Found no results for query");
            }
        } catch ( Exception $e ) {
            # Exception detected, add it to the array of errors
            $this->errors[] = $e;
        }
        return $found;
    }

    # This adds a new row to the post views table OR updates an existing row's num_views by 1
    # The record is automatically added as viewed on the current day
    function add_post_view( $post_id ) {
        # Log how we were called
        if ( $this->debug ) {
            $this->add_notice("Called add_post_view( {$post_id} )");
        }
        # Initialize our return value
        $added = 0;

        try {
            # Get hold of the database
            global $wpdb;

            # Add a new record. If there is a key violation, update the number of views instead.
            # ( the unique key on the table is on the post_id and entry_day )
            $query = <<<SQL
INSERT INTO {$wpdb->prefix}post_views (
    post_id, entry_day, num_views
 ) VALUES (
    %d, UTC_DATE(), 1
 ) ON DUPLICATE KEY UPDATE num_views = num_views + 1
SQL;

            # Add our variables to the query in a safe manner
            $query = $wpdb->prepare( $query, $post_id );

            # Log the query to be ran so we can look at it if needed
            if ( $this->debug ) {
                $this->add_notice("Prepared Query: {$query}");
            }

            # Determine our results
            $result = $wpdb->query( $query );
            if ( false === $result ) {
                # Error
                $error = $wpdb->last_error;
                throw new Exception("Bad Query: {$query}, Database Claims: {$error}", 10001);
            } else if ( 0 === $result ) {
                # Should never happen - would be an error as result is numer affected and there should be at least 1 row affected
                throw new Exception("Bad Query: {$query}, Database claims no records updated!", 10002);
            } else {
                # Note how many changes were made (anything over 1 is fine)
                if ( $this->debug ) {
                    $this->add_notice("Query completed with {$result} results");
                }
                $added = $added + $result;
            }
        } catch ( Exception $e ) {
            # Make note of the exception
            $this->errors[] = "Exception Ahoy!";
            $this->errors[] = $e;
        }
        if ( $this->debug ) {
            $this->add_notice("Leaving add_post_view");
        }
        return $added;
    }

    # Get the list of all errors as an array
    function get_errors() {
        if ( is_null( $this->errors ) ) {
            $errors = array();
        } else {
            $errors = $this->errors;
        }
        return $errors;
    }

    # Get the list of all notices as an array
    function get_notices() {
        if ( is_null( $this->notices ) ) {
            $notices = array();
        } else {
            $notices = $this->notices;
        }
        return $notices;
    }

    # Clear all errors and notices
    # Used on initialization and between calls
    function clear_all() {
        $this->notices = array();
        $this->errors = array();
    }
}

1. And make a plugin based on it;
   
2. Or add it to the end of your functions.php;
   
3. Or add it to the beginning of the template;
   
4. Or even make a library file based on it with a name like most-viewed-posts.php and call it as described in the first comment to this answer.
   

Or use something like Top 10 .