Plotting phpBB3 Forum Registrations Using MySQL Magic

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:

Chart: Origami Forum Registrations

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!

Tags: ,

Origami Forum

Our friendly and active community currently has 7,866 registered users, who have contributed 121,525 posts over 8,708 threads. Join in!

New & Refreshed

This is the 8th incarnation of my personal website. I've had a web presence since developing my very first website in 2000. Learn more about the site on the: