SQL Help Required (2 posts)

  1. indyparker
    Posted 2 years ago #

    I am using ELI's Custom SQL Reports Creation plugin.

    I want to produce the following SQL report (within WordPress):

    Count all posts for all my Categories between two dates.

    The output should be like this

    Category Count
    Football 10
    Cricket 5
    Hockey 3

    I have tried a few ideas but still cannot get it to work.

    I can get all posts for one category i.e.

    SELECT DATE_FORMAT(post_date_gmt, '%M %Y') 'Date', post_title 'Post Title' , ID FROM wp_posts
    INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
    INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
    WHERE (wp_term_taxonomy.term_id = 134
       AND wp_term_taxonomy.taxonomy = 'category'
       AND wp_posts.post_type = 'post'
       AND wp_posts.post_status = 'publish')
    ORDER by wp_posts.post_date_gmt DESC;

    Any help will be highly appreciated with this.

    Thanks in advance.

  2. vtxyzzy
    Posted 2 years ago #

    I think this will get you started:

    $start_dt = "2010/07/01";
    $end_dt = "2012/12/31";
    $tax_ids = '32, 67, 81';  // your term_taxonomy_ids
    $sql = "
    SELECT t.name, count(1) as post_count
    FROM $wpdb->posts p
    INNER JOIN $wpdb->term_relationships tr ON (p.ID = tr.object_id)
    INNER JOIN $wpdb->term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
    INNER JOIN $wpdb->terms t ON (tt.term_id = t.term_id)
    WHERE tt.term_taxonomy_id IN ($tax_ids)
       AND tt.taxonomy = 'category'
       AND p.post_type = 'post'
       AND p.post_status = 'publish'
       AND DATE_FORMAT(p.post_date, '%Y/%m/%d') BETWEEN '$start_dt' AND '$end_dt'
    GROUP BY t.name
    ORDER by t.name ASC;
    $rows = $wpdb->get_results($sql);
    foreach ($rows as $row) {
       echo "$row->name ($row->post_count)<br />";

Topic Closed

This topic has been closed to new replies.

About this Topic