Plotting phpBB3 Forum Registrations Using MySQL Magic
Thursday May 19, 2011 · Permalink
Alright, so it’s not technically magic, but it’s a workaround to a problem not easily solved by using phpBB3 directly. Ok, let’s get started.
Before you begin, you’ll need to be able to access your phpBB3 database, either through your host’s control panel or by using an external utility such as Sequel Pro. It doesn’t matter how you get your data, so do whatever you feel most comfortable with.
Exporting your data
Right, so the first step is to export the data from within your phpBB3 database. The data we want is the user registration date which is stored within the ‘phpbb_users’ table. Within that table is the ‘user_regdate’ column which contains the data we need. One of the things you may notice is that the text contained within this column is not stored in the usual MySQL timestamp format. Instead, it has been captured in Unix Time format. To get around this, submit the following query via phpMyAdmin or through your SQL client:
SELECT from_unixtime(user_regdate,'%Y/%m/%d') FROM phpbb_users
This will then convert the date into a more readable format (Year/Month/Day). If you like, you can replace the tokens in the argument above with any valid date/time specifier, as detailed in the MySQL Reference Manual.
With the query result returned, you now need to export your data, preferably in CSV format. Using Sequel Pro you can export your query result via File → Export → Custom Query Result → CSV File:
Plotting your chart
Great, so you now have your data. It’s now a simple matter of importing this data into your spreadsheet application of choice (I’ve used Numbers/iWork). With a little bit of work you can get something like this:
Okay, I admit that time (denoted on the y axis) really ought to be on the x axis seeing that it’s considered to be discrete, however I struggled to get Numbers to inverse the scales. If you know how to correct this, please let me know!