As part of ongoing work I have been doing using WordPress for our Staff Intranet at work I wanted to build a tracking plugin that would be able to log which users in the WordPress multisite network had viewed different pages and posts and when. Storing the data in a custom table seemed the right way to do this and this is how I did it.
The first part of the process was to create the custom table which WordPress will use to store the data in on each page load. This data didn’t seem to fit well into other WordPress tables as it was not meta data about posts (if it is then use wp_postmeta) and it was not a post (if it is then use wp_posts) and therefore I created my own. I accomplished this with the following code:
[wpmark_gist id=4534059]
The first part of this function is to get the table prefix being used by WordPress. This is set when WordPress is first installed and by default is set to ‘wp_‘. However as this can be changed by the user you should never presume it will be ‘wp_‘. Therefore after declaring the global variable $wpdb we can get our table prefix and assign this to a PHP variable to use later.
Next we create the table only if it has not been created before (i.e. if it already exists from a previous activation of this plugin don’t create it again). Inside this table creation we also build the table structure, creating the table columns. All this is stored as a variable to add later.
In order to actually add the table we use the WordPress dbDelta() function. However this is not loaded by default in WordPress and therefore we must manually include the WordPress upgrade.php file in order to make sure this function is available to use. We can then pass our table creation and structure function to the dbDelta() function in order to create the table. Finally we hook our function into WordPress using the register_activation_hook() function hook which only fires on plugin activation.
Now we have our table inserted we need to add data (rows) to it. I was storing all my data into an array. Then I used the $wpdb->insert function in order to add this to the database table. The code used is below:
[wpmark_gist id=4534269 bump=1]
Here you need to be very careful that your data you want to add is correct. I had a massive problem as every time to query ran it would not add a row to the table. No errors were produced either which I found strange. This led me to reach out to the Twitter community for help and I received the following reply by Mario Y. Peshev (many thanks):
@wpmark looks fine, are you sure your date format is fine, doesn't look OK to me?
— Mario Peshev (@no_fear_inc) January 14, 2013
Mario was spot on in that my date was putting the day first, then the month and then the year which was incorrect. The database table was expecting the date to be year, month, day. Once this was fixed it worked perfectly.
Now on every singular page load it writes the post ID being viewed, the users ID and display name, their IP address and the date and time to my custom database table. Next is to query the table in order to display the statistics in the WordPress dashboard and allow the admins to filter these out.
Leave a Reply