Где проблема в коде плагина?

Question

[Roman]

Пишу плагин для подсчета количества просмотров (уникальных и обычных) и не понимаю где я ошибся, таблица создается но остается пустой.

Код плагина

<?php
/*
 *Plugin Name: News Views and Visitors Counter
 *Description: This plugin counts the number of unique visitors of each news post.
 *Version: 0.4
 *Author: Strah Roman
 *Author URI: https://vk.com/strah_roman
 *License: GPLv2 or later
 *License URI: https://www.gnu.org/licenses/gpl-2.0.html
*/
register_activation_hook(__FILE__, 'create_table');
add_action('admin_menu', 'nvvc_unique_visitors_counter');
function nvvc_unique_visitors_counter()
{
	add_menu_page(
		"Статистика по новостям",
		"Статистика новости",
		"edit_others_posts",
		"unique-visitors-counter",
		"unique_visitors_counter_page"
	);
}

function nvvc_create_table()
{
// Set up the database table for tracking views and visitors.
	global $wpdb;
	$table_name = $wpdb->prefix . 'news_stats';
	$charset_collate = $wpdb->get_charset_collate();
	$sql = "CREATE TABLE $table_name (
    id BIGINT(20) NOT NULL AUTO_INCREMENT,
    post_id BIGINT(20) NOT NULL,
    views BIGINT(20) DEFAULT 0,
    unqviews BIGINT(20) DEFAULT 0,
	ip VARCHAR(64) NOT NULL,
    date DATETIME NOT NULL,
    PRIMARY KEY  (id)
) $charset_collate;";
	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
	dbDelta($sql);
}

// Define the function that will run when the post is displayed
function nvvc_count_views($id)
{
	global $wpdb;
	$table_name = $wpdb->prefix . 'news_stats';
	$ip = $_SERVER['REMOTE_ADDR'];
	$today = date('Y-m-d');
	$check_ip = $wpdb->prepare(
		"SELECT ip FROM %s WHERE post_id = %d AND ip = %s AND date = %s DESC LIMIT 1",
		$table_name,
		$id,
		$ip,
		$today
	);
    
    // Check if the IP address has already visited the post today
	if (empty($check_ip)) {
        // If not, insert a new row in the database to count the visitor
		$wpdb->query(
			$wpdb->prepare(
				"INSERT INTO %s (post_id, views, unqviews, ip, date) VALUES (%d, %d, %d, %s, %s)",
				$table_name,
				$id,
				1,
				1,
				$ip,
				$today
			)
		);
	} else {
		// Update the view count in the database
		$wpdb->prepare(
			"UPDATE %s SET views = views+1 WHERE post_id = %d AND ip = %s AND date = %s",
			$table_name,
			$id,
			$ip,
			$today
		);
	}
}

function nvvc_unique_visitors_counter_page() 
{
    global $wpdb;
    $table_name = $wpdb->prefix . 'news_stats';
    // Check if the user has permission to access this page
    if (!current_user_can("edit_others_posts")) {
        wp_die("You do not have sufficient permissions to access this page.");
    }
    
    // Check if a query has been submitted
    if (isset($_POST["limit_count"])
        and isset($_POST["date_from"])
        and isset($_POST["date_to"])
        and isset($_POST["limit_count"])) {
        $limit_count = $_POST["limit_count"];
        $date_to = $_POST["date_to"];
        $date_from = $_POST["date_from"];
        if (strtotime($date_from) > strtotime($date_to)) {
            echo '<div class="error"><p> Значение "Дата от" не может быть больше значания "Дата до" </p></div>';
        }
        $sql = $wpdb->prepare(
            "SELECT post_id, SUM(views) AS total_views, SUM(unqviews) AS unique_views 
             FROM $table_name nvvc JOIN $wpdb->posts wpp ON nvvc.post_id = wpp.ID 
             WHERE wpp.post_date BETWEEN %s AND %s
             GROUP BY post_id
             ORDER BY views DESC LIMIT %d",
            $date_from,
            $date_to,
            $limit_count
        );
        $results = $wpdb->get_results($sql);
        // Prepare the table for displaying the results
        echo '<table>';
        echo '<thead><tr><th>Post ID</th><th>Total views</th><th>Unique views</th></tr></thead>';
        echo '<tbody>';
        if ($results) {
            // Iterate over the results and display them in the table
            foreach ($results as $result) {
                echo '<tr>';
                echo '<td>' . $result->post_id . '</td>';
                echo '<td>' . $result->total_views . '</td>';
                echo '<td>' . $result->unique_views . '</td>';
                echo '</tr>';
            }
        } else {
            // Display a message if no results were found
            echo '<tr><td colspan="3">No results found.</td></tr>';
        }
        echo '</tbody>';
        echo '</table>';
    }
?>
<div class="wrap">
        <h1>Посещалка сайта</h1>
        <form method="post">
		    <label for="custom_query">Дата от</label>
			</br>
			<input id="date_from" type="date" name="date_from" value="<?php echo date("Y-m-d",strtotime("-1 day")); ?>">
			</br>
			<label for="custom_query">Дата до</label>
			</br>
			<input id="date_to" type="date" name="date_to" value="<?php echo date(
       "Y-m-d",
       strtotime("-1 day")
   ); ?>">
			</br>
			<label for="custom_query">Лимит вывода</label>
			</br>
			<input id="limit_count" type="number" name="limit_count" value="100" min="1" max="300">			
			</br>
            <input type="submit" value="Enter" class="button button-primary">
        </form>
    </div>
<?php	
}

в шаблоне страницы записи вставляю вот это

Код в шаблоне страницы

if (function_exists('nvvc_count_views'))
{
nvvc_count_views($id);
}

Answer 1

[Алексей Уколов]

Вы в трёх местах из четырёх просто вызываете prepare и думаете, что этот метод выполнит ваш запрос и вернёт результат - это не так.

Comments

[Roman]

Правильно ли я понимаю что запросы все кроме Select нужно обернуть в $wpdb->query() ? А для Select $wpdb->get_results() ?

[Дмитрий]

Там какой то свой prepare походу, sprintf в деле

[Алексей Уколов]

Роман Страх, https://developer.wordpress.org/reference/classes/wpdb/

[alexalexes]

Правильно ли я понимаю что запросы

Все запросы, которые имеют входные или выходные параметры, нужно подготовить, выполнить и если надо произвести выборку результата.
В методах Вордпресса get_results, get_row, get_col совмещено два действия - выполнение (execute) и выборка результата (fetch). Отдельного метода execute для случаев, где просто нужно выполнить в документации не просматривается.
Я понимаю, что любым их этих методов можно пнуть подготовленное выражение и оно выполнится.

[Roman]

Алексей Уколов, я видимо что то упустил , сделал как по ссылке выше , все равно не выходит что-то

код

<?php
/*
 *Plugin Name: News Views and Visitors Counter
 *Plugin URI: https://example.com/
 *Description: This plugin counts the number of unique visitors of each news post.
 *Version: 0.4
 *Author: Strah Roman
 *Author URI: https://vk.com/strah_roman
 *License: GPLv2 or later
 *License URI: https://www.gnu.org/licenses/gpl-2.0.html
*/
register_activation_hook(__FILE__, 'nvvc_create_table');
add_action('admin_menu', 'nvvc_unique_visitors_counter');
function nvvc_unique_visitors_counter()
{
	add_menu_page(
		"Статистика по новостям",
		"Статистика новости",
		"edit_others_posts",
		"nvvc-unique-visitors-counter",
		"nvvc_unique_visitors_counter_page"
	);
}

function nvvc_create_table()
{
// Set up the database table for tracking views and visitors.
	global $wpdb;
	$table_name = $wpdb->prefix . 'news_stats';
	$charset_collate = $wpdb->get_charset_collate();
	$sql = "CREATE TABLE $table_name (
    id BIGINT(20) NOT NULL AUTO_INCREMENT,
    post_id BIGINT(20) NOT NULL,
    views BIGINT(20) DEFAULT 0,
    unqviews BIGINT(20) DEFAULT 0,
	ip VARCHAR(64) NOT NULL,
    date DATETIME NOT NULL,
    PRIMARY KEY  (id)
) $charset_collate;";
	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
	dbDelta($sql);
}

// Define the function that will run when the post is displayed
function nvvc_count_views($id)
{
	global $wpdb;
	$table_name = $wpdb->prefix . 'news_stats';
	$ip = $_SERVER['REMOTE_ADDR'];
	$today = date('Y-m-d H:i:s');
	$check_ip = $wpdb->prepare(
		"SELECT ip FROM %s WHERE post_id = %d AND ip = %s AND date = %s DESC LIMIT 1",
		$table_name,
		$id,
		$ip,
		$today
	);
	$results_check_ip=$wpdb->get_results($check_ip);
    
    // Check if the IP address has already visited the post today
	if (empty($results_check_ip)) {
        // If not, insert a new row in the database to count the visitor
		$wpdb->query(
			$wpdb->prepare(
				"INSERT INTO %s (post_id, views, unqviews, ip, date) VALUES (%d, %d, %d, %s, %s)",
				$table_name,
				$id,
				1,
				1,
				$ip,
				$today
			)
		);
	} else {
		// Update the view count in the database
		$wpdb->query($wpdb->prepare(
			"UPDATE %s SET views = views+1 WHERE post_id = %d AND ip = %s AND date = %s",
			$table_name,
			$id,
			$ip,
			$today
		));
	}
}

function nvvc_unique_visitors_counter_page() 
{
   global $wpdb;
    $table_name = $wpdb->prefix . 'news_stats';
    // Check if the user has permission to access this page
    if (!current_user_can("edit_others_posts")) {
        wp_die("You do not have sufficient permissions to access this page.");
    }
    
    // Check if a query has been submitted
    if (isset($_POST["limit_count"])
        and isset($_POST["date_from"])
        and isset($_POST["date_to"])
        and isset($_POST["limit_count"])) {
        $limit_count = $_POST["limit_count"];
        $date_to = $_POST["date_to"];
        $date_from = $_POST["date_from"];
        if (strtotime($date_from) > strtotime($date_to)) {
            echo '<div class="error"><p> Значение "Дата от" не может быть больше значания "Дата до" </p></div>';
        }
        $sql = $wpdb->prepare(
            "SELECT post_id, SUM(views) AS total_views, SUM(unqviews) AS unique_views 
             FROM $table_name nvvc JOIN $wpdb->posts wpp ON nvvc.post_id = wpp.ID 
             WHERE wpp.post_date BETWEEN %s AND %s
             GROUP BY post_id
             ORDER BY views DESC LIMIT %d",
            $date_from,
            $date_to,
            $limit_count
        );
        $results = $wpdb->get_results($sql);
        // Prepare the table for displaying the results
        echo '<table>';
        echo '<thead><tr><th>Post ID</th><th>Total views</th><th>Unique views</th></tr></thead>';
        echo '<tbody>';
        if ($results) {
            // Iterate over the results and display them in the table
            foreach ($results as $result) {
                echo '<tr>';
                echo '<td>' . $result->post_id . '</td>';
                echo '<td>' . $result->total_views . '</td>';
                echo '<td>' . $result->unique_views . '</td>';
                echo '</tr>';
            }
        } else {
            // Display a message if no results were found
            echo '<tr><td colspan="3">No results found.</td></tr>';
        }
        echo '</tbody>';
        echo '</table>';
    } 
?>
<div class="wrap">
        <h1>Посещалка сайта</h1>
        <form method="post">
		    <label for="custom_query">Дата от</label>
			</br>
			<input id="date_from" type="date" name="date_from" value="<?php echo date("Y-m-d",strtotime("-1 day")); ?>">
			</br>
			<label for="custom_query">Дата до</label>
			</br>
			<input id="date_to" type="date" name="date_to" value="<?php echo date(
       "Y-m-d",
       strtotime("-1 day")
   ); ?>">
			</br>
			<label for="custom_query">Лимит вывода</label>
			</br>
			<input id="limit_count" type="number" name="limit_count" value="100" min="1" max="300">			
			</br>
            <input type="submit" value="Enter" class="button button-primary">
        </form>
    </div>
<?php	
}

PS Вывод в админку sql запроса работает корректно , проблема с добавление записей в таблицу

[Roman]

Исправленный код плагина

<?php
/*
 *Plugin Name: News Views and Visitors Counter
 *Plugin URI: https://example.com/
 *Description: This plugin counts the number of unique visitors of each news post.
 *Version: 0.4
 *Author: Strah Roman
 *Author URI: https://vk.com/strah_roman
 *License: GPLv2 or later
 *License URI: https://www.gnu.org/licenses/gpl-2.0.html
*/
register_activation_hook(__FILE__, 'nvvc_create_table');
add_action('admin_menu', 'nvvc_unique_visitors_counter');
function nvvc_unique_visitors_counter()
{
	add_menu_page(
		"Статистика по новостям",
		"Статистика новости (+уникальные)",
		"edit_others_posts",
		"nvvc-unique-visitors-counter",
		"nvvc_unique_visitors_counter_page"
	);
}

function nvvc_create_table()
{
// Set up the database table for tracking views and visitors.
	global $wpdb;
	$table_name = $wpdb->prefix . 'news_stats';
	$charset_collate = $wpdb->get_charset_collate();
	$sql = "CREATE TABLE $table_name (
    id BIGINT(20) NOT NULL AUTO_INCREMENT,
    post_id BIGINT(20) NOT NULL,
    views BIGINT(20) DEFAULT 0,
    unqviews BIGINT(20) DEFAULT 0,
	ip VARCHAR(64) NOT NULL,
    date DATE NOT NULL,
    PRIMARY KEY  (id)
) $charset_collate;";
	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
	dbDelta($sql);
}

// Define the function that will run when the post is displayed
function nvvc_count_views($id)
{
	global $wpdb;
	$table_name = $wpdb->prefix . 'news_stats';
	$ip = $_SERVER['REMOTE_ADDR'];
	$today = date('Y-m-d');
	$check_ip = $wpdb->get_results($wpdb->prepare(
		"SELECT * FROM $table_name WHERE post_id = %d AND ip = %s AND date = %s",
		$id,
		$ip,
		$today
	));
    // Check if the IP address has already visited the post today
	if (empty($check_ip)) {
        // If not, insert a new row in the database to count the visitor
		$wpdb->query(
			$wpdb->prepare(
				"INSERT INTO $table_name (post_id, views, unqviews, ip, date) VALUES (%d, %d, %d, %s, %s)",
				$id,
				1,
				1,
				$ip,
				$today
			)
		);
	} else {
		// Update the view count in the database
		$wpdb->query($wpdb->prepare(
			"UPDATE $table_name SET views = views + 1 WHERE post_id = %d AND ip = %s AND date = %s",
			$id,
			$ip,
			$today
		));
	}
}

function nvvc_unique_visitors_counter_page() 
{
    global $wpdb;
    $table_name = $wpdb->prefix . 'news_stats';
    // Check if the user has permission to access this page
    if (!current_user_can("edit_others_posts")) {
        wp_die("You do not have sufficient permissions to access this page.");
    }
    
    // Check if a query has been submitted
    if (isset($_POST["limit_count"])
        and isset($_POST["date_from"])
        and isset($_POST["date_to"])
        and isset($_POST["limit_count"])) {
        $limit_count = $_POST["limit_count"];
        $date_to = $_POST["date_to"];
        $date_from = $_POST["date_from"];
        if (strtotime($date_from) > strtotime($date_to)) {
            echo '<div class="error"><p> Значение "Дата от" не может быть больше значания "Дата до" </p></div>';
        }
        $sql = $wpdb->prepare(
            "SELECT post_id, SUM(views) AS total_views, SUM(unqviews) AS unique_views 
             FROM $table_name nvvc JOIN $wpdb->posts wpp ON nvvc.post_id = wpp.ID 
             WHERE wpp.post_date BETWEEN %s AND %s
             GROUP BY post_id
             ORDER BY unique_views DESC LIMIT %d",
            $date_from . " 00:00:00",
            $date_to . " 23:59:59",
            $limit_count
        );
        $results = $wpdb->get_results($sql);
        // Prepare the table for displaying the results
        echo '<table>';
        echo '<thead><tr><th>Post ID</th><th>Title</th><th>URL</th><th>Unique views</th><th>Total views</th></tr></thead>';
        echo '<tbody>';
        if ($results) {
            // Iterate over the results and display them in the table
			$sample_total=0;
			$sample_total_unq=0;
            foreach ($results as $result) {
				$sample_total=$sample_total+$result->total_views;
				$sample_total_unq=$sample_total_unq+$result->unique_views;
                echo '<tr>';
                echo '<td>' . $result->post_id . '</td>';
				echo '<td>' . get_the_title($result->post_id) . '</td>';
				echo '<td>' . get_permalink($result->post_id) . '</td>';
				echo '<td>' . $result->unique_views . '</td>';
                echo '<td>' . $result->total_views . '</td>';
                echo '</tr>';
            }
        } else {
            // Display a message if no results were found
            echo '<tr><td colspan="3">No results found.</td></tr>';
        }
		echo '<div class="updated"><p>Общее кол-во просмотров (по выборке): ' . $sample_total . '</p></div>';
		echo '<div class="updated"><p>Общее кол-во уникальных посетителей (по выборке): ' . $sample_total_unq . '</p></div>';
        echo '</tbody>';
        echo '</table>';
    } 
?>
<div class="wrap">
        <h1>Посещалка сайта</h1>
        <form method="post">
		    <label for="custom_query">Дата от</label>
			</br>
			<input id="date_from" type="date" name="date_from" value="<?php echo date("Y-m-d",strtotime("-1 day")); ?>">
			</br>
			<label for="custom_query">Дата до</label>
			</br>
			<input id="date_to" type="date" name="date_to" value="<?php echo date(
       "Y-m-d",
       strtotime("-1 day")
   ); ?>">
			</br>
			<label for="custom_query">Лимит вывода</label>
			</br>
			<input id="limit_count" type="number" name="limit_count" value="100" min="1" max="300">			
			</br>
            <input type="submit" value="Enter" class="button button-primary">
        </form>
    </div>
<?php	
}