Support » Fixing WordPress » SQL Help Required

  • 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.

Viewing 1 replies (of 1 total)
  • 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, 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'
    ORDER by ASC;
    $rows = $wpdb->get_results($sql);
    foreach ($rows as $row) {
       echo "$row->name ($row->post_count)<br />";
Viewing 1 replies (of 1 total)
  • The topic ‘SQL Help Required’ is closed to new replies.