posts - View the number of entries recorded per day|Programmer puzzle solving

I want to display a chart of the number of articles published from a post_type within a specified time period (e.g. 30 days). Example : 1/1/2019 (1) 1/2/2019 (15) 1/3/2019 (0) 1/4/2019 (6) 1/5/2019 (0) 1/6/2019 (3) 1/7/2019 (7)

Share Improve this question asked Mar 12, 2019 at 21:50 Milad Abbasi 213 bronze badges

Add a comment |

1 Answer 1

Sorted by: Reset to default 1

The easiest way is by using custom MySQL query with WPDB class.

global $wpdb;
$table = $wpdb->prefix . 'posts';
$sql = "SELECT DATE(post_date) AS date, COUNT(ID) AS count 
    FROM {$table} WHERE post_type = 'my_post_type' AND post_status = 'publish' GROUP BY DATE(post_date)";
$rows = $wpdb->get_results($sql);

Or if you want to prevent SQL injection, you can use prepared statement like this:

global $wpdb;
$sql = $wpdb->prepare(
    "SELECT DATE(post_date) AS date, COUNT(ID) AS count 
    FROM %s WHERE post_type = %s AND post_status = 'publish' GROUP BY DATE(post_date)",
    array(
        $wpdb->prefix . 'posts',
        'my_post_type'
    )
);
$rows = $wpdb->get_results($sql);

Then you can iterate the $rows to show the data.

foreach ($rows as $row) {
    echo $row->date . ' -- ' . $row->count;
}

Programmer puzzle solving

posts - View the number of entries recorded per day

1 Answer 1

Articles related to this article

comment list (0)