WordPress Popular Posts

Japanese version is available in WordPress Popular Posts(ja)

There are plugins that tells the visitors which posts or pages are most often viewed. Long time ago, every web page had access counter programs and showed the number of visits, which programs satisfied the vanity of the author. The WordPress plugins of this kind is the similar functionality, but they added a new feature which enables the number of veiws or comments in the list format. Visitors of your page can see which posts are frequently viewed and they will have more chance to be read. Surely the using of databases in the web sites made this feature possible. Now we can use the services like Google Analytics or Jetpack plugin provided by Automattic Inc. that is hosting WordPress.com. Google provides the API and Jetpack provides the widget, which enable WordPress to show the page views and others. So we don’t have to use our own database to store them.

We have the plugins like Google Analytics Popular Posts, which uses Google API. But this time we check WordPress Popular Posts(aka WPP). WPP has the advantage of simple and easy use without other web site services.

  1. It is very likely that this plugin is no more maintenanced. We can’t see the Japanese support page and it has not been updated for more than two years.

Some users report that this plugis doesn’t work with Super Cache. This kind of plugins updates the data stored in the database, triggered by the dynamic screen transition. This feature makes it difficult for the plugin to function with the caching plugins. WPP has the two mode of updating database, one is to update the data triggered by the_content() function, which is normal mode, and the other to update with AJAX through $_POST[‘ID’] global variable, which is optional mode. This feature enables WPP to be compatible with caching plugins. But seeing My Thumbnails keep dissapearing after upgrade, there are some problems concerning caching plugins. Furthermore, WPP seems to work with qTranslate.

When you are using SQLite Integration, you can install and activate WPP without problem. But it doesn’t work as you expect. The reason is that WPP uses single quote for the column aliases just like below.

All the aliases of the column are quoted with single quotation. There are no back ticks(*1).

  1. You can use double quotation with MySQL in the ANSI mode and with Oracle. You can use square blacket with MS SQL Server or MS Access.

I made an experiment, using commandline utility sqlite3 (version 3.7.14).

sqlite> SELECT p.ID AS id, p.post_title AS title FROM wp_posts AS p;
id          title
----------  ----------
1           Hello world!
2           Sample Page

sqlite> SELECT p.ID AS 'id', p.post_title AS 'title' FROM wp_posts AS 'p';
id          title
----------  ----------
1           Hello world!
2           Sample Page

The same output. We get the same with double quotation. You can use square blacket, which is compatible with MS SQL Server, and back tick, which is compatible with MySQL. SQL As Understood By SQLite, SQLite Keywords reads: when used with single quote, SQLite interprets it as a string literal and when used in the place where the string literal is not permitted, it interprets as an identifier.

I tried on MySQL, using the commandline utility mysql.

mysql> SELECT p.ID AS id, p.post_title AS title FROM wp_posts AS p;
+----+-----------------------+
| id | title                 |
+----+-----------------------+
|  1 | Hello world!          |
|  2 | Sample Page           |
+----+-----------------------+

mysql> SELECT p.ID AS 'id', p.post_title AS 'title' FROM wp_posts AS 'p';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''p'' at line 1

mysql> SELECT p.ID AS 'id', p.post_title AS 'title' FROM wp_posts AS p;
+----+-----------------------+
| id | title                 |
+----+-----------------------+
|  1 | Hello world!          |
|  2 | Sample Page           |
+----+-----------------------+

We can’t use single quote to the table aliases. The same output when using double quote. You can use back tick to the table aliases.

The results shows that the code of WordPress Popular Post seems to be no problem. It IS no problem, indeed. But when using SQLite Integration, the results are not the same. First try: without quote.

The result is like below, as you expected.

Array
(
    [0] => Array
        (
            [id] => 1
            [title] => Hello world!
        )

    [1] => Array
        (
            [id] => 2
            [title] => Sample Page
        )
)

With quote.

We get the result, empty array.

Array
(
)

This is from the manipulation of the single and double quote of SQLite Integration. SQLite Integration emulates PDO::bindValue, replace the quoted strings with placeholder ":param_number" and stores those strings in an array. After that, it execute PDO::prepare(). When the quoted strings are in the proper place, it returns PDOStatement object. And you can execute PDOStatement::execute() with that array and get the proper results. See more details in PHP Data Objects. I’ll give one example.

In the place :param_0 and :param_1 was a string value like ‘Hello world!’. This feature seems to be implemented originally for that reason below.

  1. It can avoid dangerous input data from the users, manipulating them before executing SQL statement(*1).
  2. PHP can cache the SQL statement and execute faster only with the replacement of the data.
  1. $wpdb::prepare() method is the similar feature. It escapes the data with addslashes() or mysql_real_escape_string()[not recommended in PHP5.5.0] functions.

The bad example has :param_0, :param_1 in the wrong places, which causes PDO::prepare() to throw an exception. SQLite Integration catches this exception and handles it. If you set $wpdb->supress_errors to false and $wpdb->show_errors to true, you can see the error messages, which isn’t enabled by default.

There’s no workaround way of avoiding this. So if you want to use WordPress Popular Posts with SQLite Integration, you have to rewrite it. There’s a patch file that includes minor fix(*1).

  1. I reported to the author Héctor. He will fix it in the next release.

As you see in readme.txt, WPP can collaborate with WP-PostRatings, which can be installed and activated normal way.

Leave a Reply