1

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)

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;
}
  • 1
    You’re not preventing any SQL injection in here, because there is no user input in your query used... And you should use ‘$wpdb->posts‘ - there is no point in any concatenation and using prefixes here, there is already value that is calculated and ready to use. – Krzysiek Dróżdż Mar 13 at 4:38
  • Agree with SQL injection part. I just gave an example there. And about $wpdb->posts, I've never known that. Thanks. :) – hamdirizal Mar 13 at 6:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.