[WordPress] 投稿月でグループ分けした月別PV集計表を見るためのカスタムプラグインを作った【ChatGPTと】

<?php
/**
 * Plugin Name: 月別集計
 * Plugin URI: chiilabo.com
 * Author: chiilabo
 * Author URI: chiilabo.com
 * Description: 投稿月ごとの月別ページビュー集計表を表示する。
 * Version: 1.0.0
 */

// Exit if accessed directly.
if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

define( 'ACCESSES_TABLE_NAME', $wpdb->prefix . 'cocoon' . '_accesses' );

// Register the admin menu for the plugin
function monthly_pageview_totals_admin_menu() {
    add_menu_page(
        '月別集計',
        '月別集計',
        'edit_posts',
        'monthly-pageview-totals',
        'monthly_pageview_totals_display',
        'dashicons-chart-line',
        20
    );
}
add_action( 'admin_menu', 'monthly_pageview_totals_admin_menu' );

// Callback to display the page content
function monthly_pageview_totals_display() {
    global $wpdb;
    
    // Use the Transients API to cache the page view data for 12 hours

        // Retrieve all the posts and their permalinks
        $posts = get_posts( array(
            'post_type' => 'post',
            'post_status' => 'publish',
            'numberposts' => -1
        ) );
        
        $urls = array();
        $post_ms = array();
        $results = array();
        foreach ( $posts as $post ) {
            $url = parse_url( get_permalink( $post ) );
            $urls[$post->ID] = substr( $url['path'], 1 );
            $post_m = substr($urls[$post->ID], 0,7);
            $post_ms[$post_m]=$post_m;
            $results[$post_m]=array();
        }
        
        
        $table_name = ACCESSES_TABLE_NAME;
        $query = "
            SELECT post_id, SUM(count) as pv, YEAR(date) as year, MONTH(date) as month, date
            FROM $table_name 
            GROUP BY post_id, year, month
            ORDER BY date DESC  
        ";  
        
        // Retrieve the access logs
        $logs = $wpdb->get_results($query);

    $months = array();
    foreach ( $logs as $log ) {
        $post_m = substr($urls[$log->post_id], 0,7);
        $access_m = $log->year.sprintf('%02d', $log->month);
        
        $pv = $results[$post_m][$access_m];
        if (!$pv) {
            $pv =0;
        }
        $results[$post_m][$access_m] = $pv + $log->pv;
        $months[ $access_m ] = $log->year.'年'.$log->month.'月';
    }    
    
    // Initialize the table
    echo '<table class="widefat">';
    echo '<thead><tr>';
    echo '<th width="40%">投稿月</th>';
    
    foreach ( $months as $month ) {
        echo '<th>' . esc_html( $month ) . '</th>';
    }
    echo '</tr></thead>';
    
    // Populate the table
    echo '<tbody>';
    foreach ( $results as $m => $row ) {
        echo '<tr>';
        echo '<td>' . $m  . '</td>';
        foreach ( $months as $key => $month ) {
            $pv = 0;
            foreach ( $row as $mm => $mpv ) {
                if ( $mm == $key ) {
                    $pv = $mpv;
                    break;
                }
            }
            echo '<td align="right">' . ( $pv ? number_format_i18n( $pv ) : '' ) . '</td>';
        }
        echo '</tr>';
    }
    

    echo '</tbody>';
    echo '</table>';
}

【動作無保証】montyly_pageview_totals.zipのダウンロード。

<?php
global $wpdb;

// Get all published posts
$posts = $wpdb->get_results("SELECT ID, post_date, post_title FROM $wpdb->posts WHERE post_status = 'publish'");

// Initialize array to hold monthly pageview totals
$pageviews_by_month = array();

// Loop through each post and calculate its pageview total
foreach ($posts as $post) {
    $post_id = $post->ID;
    $post_date = $post->post_date;
    $post_title = $post->post_title;

    // Get monthly pageview total for this post
    $pageviews = $wpdb->get_var("SELECT SUM(views) FROM $wpdb->post_views WHERE id = $post_id AND type = 'post' AND MONTH(date) = MONTH('$post_date') AND YEAR(date) = YEAR('$post_date')");

    // Add pageview total to monthly total for this month
    $month = date('Y-m', strtotime($post_date));
    if (!isset($pageviews_by_month[$month])) {
        $pageviews_by_month[$month] = array();
    }
    $pageviews_by_month[$month][$post_date] = $pageviews;
}

// Print monthly pageview totals as a table
echo "<table>";
echo "<thead><tr><th>Month</th>";
foreach ($posts as $post) {
    echo "<th>" . $post->post_title . "</th>";
}
echo "</tr></thead>";
echo "<tbody>";
foreach ($pageviews_by_month as $month => $totals) {
    echo "<tr><td>" . $month . "</td>";
    foreach ($posts as $post) {
        $post_date = $post->post_date;
        $pageviews = isset($totals[$post_date]) ? $totals[$post_date] : 0;
        echo "<td>" . $pageviews . "</td>";
    }
    echo "</tr>";
}
echo "</tbody>";
echo "</table>";
?>

このスクリプトでは、まずすべての公開された投稿を取得します。次に、各投稿のページビューの合計を計算し、各月の集計表に追加します。最後に、集計表をHTMLテーブルとして出力します。

このスクリプトを、WordPressプラグインとして実装するためには、必要なWordPress関数やフックを利用して、データベースアクセスや出力方法を調整する必要があります

ChatGPTによるコードの説明

<?php
/**
 * Plugin Name: 投稿別アクセス集計
 * Plugin URI: chiilabo.com
 * Author: chiilabo
 * Author URI: chiilabo.com
 * Description: 投稿ごとのアクセス数を集計して表示するプラグインです。
 */

defined( 'ABSPATH' ) or die( 'No direct script access allowed' );

add_action( 'admin_menu', 'pageview_totals_menu' );

function pageview_totals_menu() {
    add_submenu_page(
        'edit.php',
        '投稿別アクセス集計',
        '投稿別アクセス集計',
        'edit_posts',
        'pageview-totals',
        'pageview_totals_callback'
    );
}

function pageview_totals_callback() {
    global $wpdb;
    $table_name = $wpdb->prefix . 'access_logs';

    $urls = $wpdb->get_results(
        "SELECT DISTINCT post_id, post_name FROM $wpdb->posts ".
        "INNER JOIN $table_name ON $wpdb->posts.ID = $table_name.post_id ".
        "ORDER BY post_date DESC"
    );

    $logs = $wpdb->get_results(
        "SELECT post_id, COUNT(*) as count ".
        "FROM $table_name ".
        "GROUP BY post_id"
    );

    echo '<div class="wrap">';
    echo '<h1>投稿別アクセス集計</h1>';

    if ( empty( $urls ) ) {
        echo '<p>アクセスログがありません。</p>';
        echo '</div>';
        return;
    }

    echo '<table class="wp-list-table widefat fixed striped">';
    echo '<thead><tr>';
    echo '<th style="width: 40%;">URL</th>';
    echo '<th>アクセス数</th>';
    echo '</tr></thead><tbody>';

    foreach ( $urls as $url ) {
        $count = 0;
        foreach ( $logs as $log ) {
            if ( $log->post_id == $url->post_id ) {
                $count = $log->count;
                break;
            }
        }

        if ( $count > 0 ) {
            $url_name = preg_replace( '#^https?://[^/]+#i', '', get_permalink( $url->post_id ) );
            echo '<tr><td>'.$url_name.'</td><td style="text-align: right;">'.number_format( $count ).'</td></tr>';
        }
    }

    echo '</tbody></table>';
    echo '</div>';
}

このプラグインは、管理画面に「投稿別アクセス集計」というメニューを追加し、投稿ごとのアクセス数を集計して表示します。

まず、管理画面にメニューを追加するために、add_actionを使ってadmin_menuフックにpageview_totals_menu関数を登録しています。pageview_totals_menu関数では、add_submenu_pageを使って、WordPressの投稿一覧画面にサブメニューを追加しています。

pageview_totals_callback関数では、アクセスログのテーブル名を取得し、URLとアクセス数の配列を取得します。

Chat GPTによる説明