I am trying to get cutom post cout by month of current taxonmy term

Question

Hi please help me how can i display custom post count by month like this at current taxonomy page.

**i have been trying to solve this problem aready 2 weeks. **

• December 0

  January 0

  February 0

  March 0 April 0

  May 0

  June 2

  July 8

  August 35

  September 0

  October 0

  November 9

I did everything that i can do. but result 0 or total post count by month

<?php
global $wpdb;
$rel = $wpdb->get_results(
    "SELECT MONTH(post_date) as post_month, COUNT(ID) as post_count " .
    "FROM {$wpdb->posts} AS posts
                LEFT JOIN {$wpdb->term_relationships} AS tax_rel ON (posts.ID = tax_rel.object_id)
                LEFT JOIN {$wpdb->term_taxonomy} AS term_tax ON (tax_rel.term_taxonomy_id = term_tax.term_taxonomy_id)
                LEFT JOIN {$wpdb->terms} AS terms ON (terms.term_id = term_tax.term_id)" .
    "WHERE post_date BETWEEN DATE_SUB(NOW(), INTERVAL 12 MONTH) AND NOW() AND post_type = 'tesekkur' " .
    "AND post_status = 'publish' " . $post_type_query .
       "AND terms.name = 'term_id'
        AND term_tax.taxonomy = 'term_taxonomy'",
    "GROUP BY post_month ORDER BY post_date DESC", OBJECT_K
);

$postCount= 0;
$len = count($looper);

$cur = absint(date('n'));
if($cur > 1)
{
    $looper = array_merge(range($cur+1, 12), range(1, $cur));
}
else
{
    $looper = range(1, 12);
}

$out = '0,';
$postCount= '0';
$len = count($looper);
foreach($looper as $m)
{


    $month = date_i18n('F', mktime(0, 0, 0, $m, 1));

    $out .= sprintf(
       '<li>' .'%s %d'. '</li>',
        $month,
        //'',
        isset($rel[$m]) ? $rel[$m]->post_count : 0
    );
    if ($postCount!= $len-1) {
       $out .= '';
    }

    $postCount++;

}
//$out .= '</ul>';
echo $out; ?>

Answer 1

you can do that with WordPress functions like that :

$post_type = "tesekkur";

$timestamp_start = strtotime("first day of next month -1 year midnight");
$start = date("Y-m-d H:i:s", $timestamp_start);

$posts = get_posts([
    "nopaging" => TRUE,
    "post_type" => $post_type,
    "date_query" => [
        "after" => $start,
    ],
]);


// sort by month

$tab = [];

foreach ($posts as $post) {

    $month = mysql2date("F", $post->post_date);

    if (!isset($tab[$month])) {
        $tab[$month] = 0;
    }

    $tab[$month]++;

}


// display

$timestamp = $timestamp_start;
$now = time();

while ($timestamp < $now) {

    $month = date_i18n("F", $timestamp);

    $count = $tab[$month] ?? 0; // need PHP 7

    echo "$month : $count<br/>";


    // next month

    $timestamp = strtotime("+1 month", $timestamp);

}