WordPress feature hacking

I’m using Koko analytics on my site to generally see if it’s getting traffic (it’s not :D). One thing I wanted to add was a site counter as a throwback to my original website which tracked such things. The data is clearly there since Koko can display the visitors over time:

Okay, so let’s get started. Luckily, after cloning the repo, I found an existing file which generates a shortcode. I started there & copy/paste/changed the filename. I didn’t know how to get the data I wanted, but I just wanted the file to show up. I ended up searching for Shortcode_Most_Viewed_Posts to see how it gets used, and I found the following snippet:

require __DIR__ . '/src/class-shortcode-most-viewed-posts.php';
$shortcode = new Shortcode_Most_Viewed_Posts();
$shortcode->init();

Ah, that explains it. the init() function inside of Shortcode_Most_Viewed_Posts just calls add_shortcode( self::SHORTCODE, array( $this, 'content' ) ); and we can see from the docs that this is indeed the correct entrypoint to wire everything up. I added [koko_analytics_site_counter] to my footer.php, and … nothing. Well, not nothing, the footer now said [koko_analytics_site_counter]. So, what that meant to me was that the shortcode wasn’t getting wired up right. I quickly realized that the content() function wasn’t being called at all. A quick ducky search for wordpress footer shortcode pointed me to this answer:

<span>
  Visitors:
    <?php            
      echo do_shortcode('[koko_analytics_site_counter]');
    ?>
</span>

TL;DR: My shortcode was being viewed as regular text, and not interpreted by wordpress. Adding the do_shortcode block fixed this. So, now I had a barebones shortcode, returning 22 as a hardcoded value. Feel free to check out the whole commit here.

Next up was to get the actual functionality. I started with the simple case of getting all visitors. After some searching around, I found this get_stats() function which somewhat matched the behavior I wanted. For right now, I’m ignoring the dates, and since I just need all of the data, I used the SQL SUM aggregation command. That led me to the following helper function (full commit here)

function get_total_views($days) {
    global $wpdb;
    $sql = "SELECT SUM(visitors) FROM {$wpdb->prefix}koko_analytics_site_stats";
    return $wpdb->get_var( $sql );
}

Refreshing the page, and.. voila! it worked. Finally, I wanted to be able to limit this back to the previous 30 days, if desired. I went back to Shortcode_Most_Viewed_Posts and saw that it used gmdate and some related helpers to compute the WHERE clause.

$start_date = gmdate( 'Y-m-d', strtotime( "-{$args['days']} days" ) );
$end_date   = gmdate( 'Y-m-d', strtotime( 'tomorrow midnight' ) );
$sql        = $wpdb->prepare( "SELECT p.id, SUM(visitors) As visitors, SUM(pageviews) AS pageviews FROM {$wpdb->prefix}koko_analytics_post_stats s JOIN {$wpdb->posts} p ON s.id = p.id WHERE s.date >= %s AND s.date <= %s AND p.post_type = %s AND p.post_status = 'publish' GROUP BY s.id ORDER BY pageviews DESC LIMIT 0, %d", array( $start_date, $end_date, $args['post_type'], $args['number'] ) );
$results    = $wpdb->get_results( $sql );

I tried messing with this a bit, and through some helpful stackoverflow posts, I was able to come up with this solution:

private function get_total_views( $days) {
    global $wpdb;
    if ($days == -1) {
      $sql = "SELECT SUM(visitors) FROM {$wpdb->prefix}koko_analytics_site_stats";
    } else {
      $timezone = get_option( 'timezone_string', 'UTC' );
      $datetime = new \DateTime('now', new \DateTimeZone($timezone));
      $datetime->modify(sprintf( '-%d days', $days));
      $start_date = $datetime->format('Y-m-d');
      $sql = $wpdb->prepare("SELECT SUM(visitors) FROM {$wpdb->prefix}koko_analytics_site_stats s WHERE s.date >= %s", array( $start_date ) );
    }
    return $wpdb->get_var( $sql ) || 0;
  }

Don’t forget to use wpdb->prepare to avoid SQL injection attacks like a n00b!

I tested this on my local site, and *perfecto*. However, when I deployed it to my production server, it just says the # of visitors is 1 🙁

To debug this, I opened a shell in my mariadb docker container and ran

MariaDB [wordpress]> SELECT SUM(visitors) FROM wp_koko_analytics_site_stats;
+---------------+
| SUM(visitors) |
+---------------+
|            20 |
+---------------+
1 row in set (0.001 sec)

Therefore, the data looks right, and the query looks right. Maybe it’s a caching issue (since I originally had days=1). So that wasn’t the issue. After a bunch of printf/style (e.g error_log/ print_r ) debugging, I finally realized what my issue is. return $wpdb->get_var( $sql ) || 0; doesn’t do what you think it should do. It always coerces to a boolean. Instead, you want the ?? (null coalescing operator). With that fixed, things were finally looking up!

Anyways, here’s my current solution (total time: 2 hours)

<?php
/**
 * @package koko-analytics
 * @license GPL-3.0+
 * @author Anil Kulkarni
 */

namespace KokoAnalytics;

 class ShortCode_Site_Counter {
  const SHORTCODE = 'koko_analytics_site_counter';

  public function init() {
    add_shortcode( self::SHORTCODE, array( $this, 'content' ) );
  }

  public function content( $args ) {
    $default_args = array(
      'days' => -1,
    );
    $args = shortcode_atts( $default_args, $args, self::SHORTCODE );
    $count = $this->get_total_views($args['days']);
    $html = sprintf( PHP_EOL . ' <span class="koko-analytics-post-count">%s</span>', $count );
    return $html;
  }

  private function get_total_views( $days) {
    global $wpdb;
    if ($days == -1) {
      $sql = "SELECT SUM(visitors) FROM {$wpdb->prefix}koko_analytics_site_stats";
    } else {
      $timezone = get_option( 'timezone_string', 'UTC' );
      $datetime = new \DateTime('now', new \DateTimeZone($timezone));
      $datetime->modify(sprintf( '-%d days', $days));
      $start_date = $datetime->format('Y-m-d');
      $sql = $wpdb->prepare("SELECT SUM(visitors) FROM {$wpdb->prefix}koko_analytics_site_stats s WHERE s.date >= %s", array( $start_date ) );
    }
    return (int)($wpdb->get_var( $sql ) ?? 0);
  }
}