SQL Help Required

  • indyparker

    (@indyparker)


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

Viewing 1 replies (of 1 total)
  • vtxyzzy

    (@vtxyzzy)

    11 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 />";
}
Viewing 1 replies (of 1 total)
  • The topic ‘SQL Help Required’ is closed to new replies.