Custom Tables in a WordPress Plugin

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):

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.

Re-Installing Mac OS X Mountain Lion for Resale

I have recently sold my iMac on eBay. I have a MacBook Air and was not fully convinced when I purchased it that it would be good enough for a main machine. How wrong was I, which left me not really needing my iMac hence the reason for the sale. Therefore I needed to wipe it and then re-install the OS ready for sale. Here is how I went about this.

Previous to Mac OS X Lion all Macs shipped with Install discs containing the Operating System and any additional software that was installed on the machine. However starting with Lion and afterwards the Mac App store meant that Mac OS X was all done with digital downloads and therefore Apple stopped providing install discs with Macs. What Apple failed to announce in a prominent place was that all Macs then included a Mac OS X Recovery partition which could be used in order to restore your machine to a vanilla install.

With this in mind I took the steps below in order to wipe my Mac and re-install the OS ready to set the item after a sale.

  1. Backup all data from the Mac. I use Time Machine with a Time Capsule and therefore I just checked to make sure that the latest backup had run correctly (which it had) and therefore I knew all my stuff was safe.
  2. Deactivate the computer in iTunes (should you use this for your music etc. of course). Purchases from iTunes are only allowed to be played back on 5 computers and therefore you should remove this one as you will no longer need it again.
  3. Deactivate any other software that you may have installed that is only authorised for one machine. I was running Adobe CS5 and therefore I needed to deactivate this so that I could then activate this on another machine.
  4. Sign out of iCloud services and then turn them all off.
  5. Next you need to restart your Mac, holding down the Command + R keys which will restart your Mac into Mac OS X Recovery Mode. From the screen that appears you should select Disk Utility.
  6. Here select the main disc in your Mac with all your stuff on it (probably called Macintosh HD). On the right panel click on the Erase tab and then select security options below. I would not choose anything lower than to zero out the data but would recommend higher. The higher you choose the longer the erase will take (it could be anything from a couple of hours to 15 hours depending on the security level you choose and the size on your Mac’s hard disc).
  7. Once the disc has erased close Disk Utility and then click on the “Re-Install Mac OS X” option from the window. Follow the instructions here. One thing to note here is that it will ask you for your Apple ID at this point. I was worried that this would ’embed’ my Apple ID into the install – something I did not want as I was selling the Mac. However this is not the case as your Apple ID is only used to verify that the OS is legal.
  8. Once this is done your Mac will restart and then arrive at a screen for your to start setting up your Mac with a language and user etc. At this point press Command + Q in order to quite the installer as you want the person that has bought the machine to go through this.

Hopefully the purchaser will have no problems with setting the machine up from here as it will be as new for them when they turn it on. The one that I did wonder about was what happens if the disc fails? This would mean there is no recovery partition. Perhaps there is the option to build a bootable recovery thumb drive? More research needed!