My post “Counting Post Views in WordPress without PlugIn” shows a way on how to count post views in WordPress. To see them, one have to either login to WordPress and have a look at the posts overview, or connect to the WordPress database and run SQL queries. The posts overview also does not show the total number of views, but only the counter per post.

I wanted to have an easy-to-use way to get the figures, without the need to login anywhere. So I had a look for what is needed to implement a REST API in PHP, and was surprised how easy it is to implement one (at least as long as no authorization or the like is required 😉 ).
As I tend to follow the “single-responsibility ” and “separation of concerns” principle, I identified two APIs functions:

Get the total number of post views
Get a list containing the views per post

Please note that the implementation presented here does not extend the WordPress’ REST API, nor uses it. It uses some WordPress functionality, but is not executed in the context of it. Accordingly, this is not a plugin or the like.

Database Preparation

To avoid direct table access from the code (to me always a good idea, not only from a security perspective), I prepared two database views to return the data.

Read total Number of Post Views

This view returns the sum of views of all posts.

create view vw_get_views_total_count as
select sum(cast(meta_value as signed)) as views_total
from wp_postmeta 
where meta_key = 'post_views_count';

Read List of Posts including View Counter

This view returns a list of all posts having at least one view, including the views per post, sorted by number of views in descending order.

create view vw_get_views_counter as
select pm.meta_id, pm.post_id, pm.meta_value, po.post_title from wp_postmeta as pm
inner join wp_posts as po on pm.post_id = po.ID
where pm.meta_key = 'post_views_count' and meta_value <> '0'
order by cast(pm.meta_value as signed) desc;

Please note that it is important to cast meta_value to a numeric data type in the order by clause. Otherwise the sort order would not be as expected.

Helper Functions

Implementing the API, it turned out very quickly that the API functions always follow the same pattern:

Read data from the database
Convert the database resultset to JSON
Return the JSON data

The only difference is the database query itself.

Accordingly, to avoid code (and error) duplication, I’ve created to helper functions.

Database Helper Function

The file ifb_db_helper.php contains the following function ibf_get_get_db_results to read data from the database.

<?php

require_once('WP RootDir/wp-config.php');

/**
 * Executes a query against the database and returns the result of the query.
 * 
 * In case of an exception, the error information is echoed.
 * 
 * @param string $query_string The query to be executed.
 * 
 * @return array|bool The resultset returned by the database, or false in case an exception or throwable occurred.
 * 
 * @global wpdb $wpdb The object to access the WordPress database.
 * 
 */
function ibf_get_get_db_results(string $query_string): array
{
    global $wpdb;

    try {
        $db_query = $wpdb->prepare($query_string);

        $resultset = $wpdb->get_results($db_query);
    } catch (Exception $e) {
        echo 'Caught exception: ',  $e->getMessage(), "\n";
        return false;
    } catch (\Throwable $throwable) {
        $response['error'] = 'Unable to get data from database';
        $response['errorMessage'] = $throwable->getMessage();
        $response['errorFile'] = $throwable->getFile();
        $response['errorLine'] = $throwable->getLine();
        $response['errorLine'] = $throwable->getLine();
        $json_response = json_encode($response);
        echo $json_response;
        return false;
    }

    return $resultset;
}

You have to replace WP RootDir by the absolute or relative path of your WordPress installation to make this code work. The relative path depends on where you place this file.

Even though I will not pass dynamic SQL or SQL containing user input, I follow WordPress’ guidance that “all data in SQL queries must be SQL-escaped before the SQL query is executed to prevent against SQL injection attacks” (see “WordPress database class wpdb“).

API Helper Function

The function ifb_do_db_api_call, contained in the file ifb_api_helper.php, uses ibf_get_get_db_results to retrieve data from the database, and creates the JSON output in case no error occurred when accessing the database.

<?php

require_once('ifb_db_helper.php');

/**
 * Executes a query againts the database and echos the result of the query as JSON.
 * 
 * In case an error occurred when running the query against the database, nothing is echoed.
 * 
 * @param string $query_string The query to be executed.
 * 
 * @global wpdb $wpdb The object to access the WordPress database.
 * 
 */
function ifb_do_db_api_call(string $query_string)
{
    header("Content-Type:application/json");

    $resultset = ibf_get_get_db_results($query_string);

    if ($resultset === false) {
        return;
    }

    $json_response = json_encode($resultset);

    echo $json_response;
}

This implementation assumes that the files ifb_api_helper.php and ifb_db_helper.php are located in the same folder.

The API Functions

Now that the preparation is complete, we are ready to implement the actual API. Thanks to the helpers, only three lines of code are required per API function.

Get Views Counter per Post

<?php

require_once ('../helper/ifb_api_helper.php');

// Use the helper function to process the request.
ifb_do_db_api_call('select * from vw_get_views_counter;');

Ok, here, there are four lines, because of the comment.

The view vw_get_views_counter is used the retrieve the data from the database.

This implementation assumes that the helper functions are located in a folder named helper, located at the same folder level as the current file. In case you decide to have a different setup, you need to adjust the path in the require_once statement.

Get total Number of Post Views

<?php

require_once ('../helper/ifb_api_helper.php');

// Use the helper function to process the request.
ifb_do_db_api_call('select * from vw_get_views_total_count;');

Here, the view vw_get_views_total_count is used to get the total count from the database.

Like above, the implementation assumes that the helper functions are located in a folder named helper, .located at the same folder level as the current file. In case you decide to have a different setup, you need to adjust the path in the require_once statement.

Where to put the API Files?

The files need to be located somewhere, where it is possible to include the WordPress files too. As an example, the following structure can be created (WordPress folders are omitted):

WordPress_root
|
  my_api_folder
  |
    post_views_counter
      get_post_views_counter.php (contains the code to return the number of views per post)
      get_post_views_total_count.php (contains the code to return the total number of post views)
  |
    helper
      ifb_api_helper.php
      ifb_db_helper.php

Use the API

Given the setup is described as above, the following URIs can be used to call the API.

Get the counters per post: your-wordpress-domain.com/my_api_folder/post_views_counter/get_post_views_counter.php

Get the total number of views: your-domain.com/my_api_folder/post_views_counter/get_post_views_total_count.php

Please note that the setup of this blog is different. So copying these paths and send a request to instance-factory.com will not return the expected data 😉

For my personal use, I have created a small PowerShell script using Invoke-RestMethod to read the data and Out-GridView to present the list returned by get_post_views_counter.php.

Write-Host 'Getting views total count'
$totalcount = Invoke-RestMethod https://my-total-count-uri
Write-Host ($totalcount | Format-Table | Out-String)

Write-Host 'Getting views counter'
$counter = Invoke-RestMethod https://my-views-counter-uri
$counter | Out-GridView

Learnings when using Wordfence

The initial implementation of ibf_get_get_db_results had a $wpdb->close(); as the last statement included. This is due to the development guideline to release external resources as soon as they are not needed any more.

This implementation worked without any issues until I installed the WordPress plugin “Wordfence Security – Firewall & Malware Scan” by Wordfence. After activating it, calling any API function, I received the following error:

Fatal error: Uncaught OutOfBoundsException: ipRange is not a valid property for this block type in wordpress_root\wp-content\plugins\wordfence\models\block\wfBlock.php on line 1148

I did lots of debugging, thought the plugin was not initialized properly, and so forth. In the end it took me about two days to solve the issue. While debugging the plugin I saw that, after my API function completed its task, the plugin was still executing some database queries. To do so, it is also using the globally defined $wpdb object.

To cut a long story short: I’ve learned it is not the best idea to close / free globally resources within custom code in WordPress.

Security Considerations

This solution is not very secure. Whoever knows the path to the API is able to call it. No authentication or authorization is required.

In case you see the counters or other data returned by the API (e.g. database ids) to be critical or confidential, you should not implement this approach.

To at least “hide” your API, I suggest not to publish the path and use a different, not-so-easy-to-guess, folder setup, not the one shown in this post.

Links

Counting Post Views in WordPress without PlugIn
REST API on Wikipedia
Single-responsibility Principle
Separation of Concerns Principle
Documentation of WordPress database class wpdb
WordPress’ REST API
WordPress plugin “Wordfence Security – Firewall & Malware Scan” by Wordfence

Factory Blog

by Instance Factory - a project of the proccelerate GmbH

REST API to retrieve WordPress View Counters