Random musings about food, tech and life

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

The purpose of this tutorial is to guide existing phpBB users in relocating their default forum installation. It is particularly relevant if you are in the process of moving hosts.

Notify your users and moderators

If possible, provide as much notification to your users and moderators about any expected downtime. Begin by posting an annoucement on your forum, followed by a mass email to your users nearer the time. When you are ready to proceed, de-activate the forum via the ACP. It is crucial that this is done to ensure no further amendments are made to the database.

Back up, back up, back up!

I can not stress this enough; make backups of your data. Then make a backup of your backup, because things can go invariably awry with the ‘active’ copy of your backup.

The easiest way of backing up your folders is via an FTP client, such as SmartFTP or Transmit.

Your database(s) can be backed up using phpMyAdmin (in most instances, this is already installed by your host and can be accessed via your website control panel). You may also wish to consider using a desktop database GUI client such as SQLyog. The free version of this software is sufficient for our purposes.

If you have a relatively small database you can export all the data via phpMyAdmin’s Export feature. For larger forums (database data > 20MB) I would recommend that you split your export into smaller batches of tables. You’ll note that the phpbb_posts, phpbb_posts_text, phpbb_privmsgs_text and phpbb_search_wordmatch tables contain the bulk of your data, and I would suggest you back these up seperately. When exporting your tables, ensure that the option for ‘Extended Inserts’ is disabled.

You can verify the integrity of your SQL dump by opening it in a text editor (such as Wordpad). Scroll to the bottom of the file; the presence of any HTML or javascript would suggest that phpMyAdmin timed out during the export.

Upload your data

Upload all your files to your new host. If you have not yet initiated domain transfer to point to your new hosts servers, connect to your server via the IP address provided by your new host. If necessary, update config.php in your forum/ directory to reflect the new database/server credentials.

Restoring your database
Create a new database via your new hosts control panel. Next, download bigdump.php. BigDump is a lightweight PHP script which staggers the import of your SQL file. Without it, you may experience MySQL timeout errors. Make the necessary amendments to bigdump.php to reflect your new database settings. Upload this, together with your SQL files, to your server. You can then initiate the import of your database by accessing bigdump.php directly from your browser. Continue doing this until all your tables have been restored.

You may also wish to consider using SQLyog’s ‘transfer database’ option to propogate your database data directly from your existing host to your new host, without needing to make any SQL backups. (I must add that this didn’t work for me; possibly because my post tables were particularly large).

Going live

Once the data has been transferred, and you are satisfied, arrange to have your DNS/nameserver information amended to reflect that of your new host.

If the location of your forum has not changed (i.e, you’ve simply moved hosts, and have retained directory structure) you should be able to log in to your forum. Don’t forget to re-activate the forum via the ACP.

If the forum has changed location (i.e, if you renamed the forum directory from phpBB2/ to forum/, or installed at root level) be sure to use the Auto Cookies mod to correct your path data.

Redirect visitors with .htaccess

If you have changed the forum directory, you may still receive requests to the old location of your forum. Rather than serve an ugly 404 error, you can seamlessly re-direct this traffic to the new forum location, whilst retaining topic/posts data in the URL.

Create a new .htaccess file and save it (at root level) with the following command:

RewriteEngine On
RewriteRule ^OLD/(.*)$$1 [R=301,L]

Replace ‘OLD/’ with the old directory name, whilst also replacing the URL to reflect the location of your new phpBB install. The above rewrite command will redirect requests to the new location via a transparent 301 ‘Moved permanently’ header.

That’s all folks. If you need any further assistance please ask at the phpBB support forums.


Origami Forum

The Origami Forum currently has over 7,900 registered users, who have contributed more than 122,000 posts over 8,808 threads. Join Now!

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: