When I converted HDTVok.com from phpWebsite to WordPress two years ago, I added user forums with the XDForum plug-in. In fact, the wide availability of plug-ins was a major reason why I chose WordPress for the conversion.
Unfortunately, the authors of XDForum announced they were stopping development of the plug-in in February, which effectively meant it was orphaned as WordPress moved forward. I have no problem with that as it’s easy to get over extended and decide you have to drop things that aren’t generating income.
When I did the conversion to WordPress, I could have chosen to use BBPress for the site’s forums, but at that time it wasn’t part of the WordPress family and as well integrated as far as users go. Because I want to keep all my WordPress sites up to date going forward, I converted the XDForum database to BBPress and this post is an explanation of one way that can be accomplished.
These instructions are for advanced users. You should have access to your WordPress database and the ability to run SQL commands on it. You will likely have to edit the files below so if you’re not comfortable with that, look elsewhere.
The files used to handle this conversion are contained in xdforum2bbpress.zip. There are two files:
- xdforum.sql — Contains notes on the basic SQL commands used for the conversion of forums, topics and posts. Also, has the basic SQL commands for adding a collection of tags to the database.
- tagging.php — Once you’ve added the tags, you can run this script once from your server. It will cycle through the tags you added with the first file and add them as tagged with the correct dates.
Using these files, here are steps I went through to convert hdtvok.com’s forums from XDForum to BBPress:
- Backup your WordPress database. We hope nothing goes wrong, but if it does you’ll be glad you did.
- Install BBPress in your WordPress database on your server and familiarize yourself with its settings and database structure. Since XDForum acted as a WordPress plug-in, you’ll want to set up WordPress integration in BBPress so your users transfer seamlessly. The files included with these instructions assume your $table_prefix is “bb_” and “wp_” for BBPress and WordPress respectively.
- Convert xdforums_groups to bb_forums. BBPress doesn’t know about groups so I set them up as forums and then set the XDForums forums as subforums of these. Make a note of the highest xg_id as you’ll need to add that value in the next step and refer to it a few times as convert topics and posts. You’ll also want to delete the default data added with your BBPress installation.
- Convert xdforums_forums to bb_forums. The xf_id plus the highest xg_id in the previous step will be the forum_id for these records and we’ll refer back to that in future steps to keep the topics and posts tied to the forums. In my case, I added 8 to each xdforums_forums.xf_id.
- Convert xdforums_threads to bb_topics. Since all of these are related to the forums in the previous step, we add the highest xg_id value to xt_xf_id so the forum_id matches. You’ll also want to delete the default data added with your BBPress installation.
- Convert xdforum_posts to bb_posts. This is pretty straight forward, but we do discard the xp_subject, which is the title that people give their individual posts. There is no equivalent that I could find in BBPress. You’ll also want to delete the default data added with your BBPress installation.
- Cleanup and update bb_topics. At this point, we have the basic data, but need to grab some additional items, like counts, for updating and also clean up the slugs so we can use pretty permalinks.
- Get the display name for all the topic starters and add it to bb_topics.
- Get the display name for all the topic last posters from bb_posts and add it to bb_topics.
- Create a view named “topic_info” that will contain the topic_id, count of posts, first post time stamp, and last post time stamp. There’s probably an easier way to do this, but I couldn’t figure it. We use this to update several items in bb_topics that we didn’t do while converting the data.
- From the view, update the topic_posts, topic_start_time, and topic_time in bb_topics.
- Drop the “topic_info” view as we won’t need it again.
- Clean up the topic_slug field so that certain characters, such as question marks and slashes are deleted. The sql file contains several update statements that do this, but there could be even more that my content didn’t have. This is especially important if you want to use pretty permalinks.
- Grab the forum_id from bb_topics and add it to bb_posts. This wasn’t done while converting the bb_posts so it needs to be done now to keep everything straight.
- Create and populate the tag tables. This step is somewhat optional, but a nice addition since XDForums didn’t support tagging introduced with WordPress 2.3.
- Add the tags you want to search for to bb_tags. Because we’ll update the count later, we just need the slug and nice name for the tag. The SQL file contains the tags that I added to my database, but obviously you’ll want to modify that to suit your needs.
- Run the tagging.php script on your server. You’ll need to edit the database connection information for your database. This script grabs all the tags in the previous step from the database, inserts the tag_id, user_id, topic_id and date of the last post as the tagged_on value in the bb_tagged table.
- To update the tag count, we create another view named “tag_info” with the tag ID and count so we can update bb_tags with the correct tag count and thus generate the tag cloud/heat map. Again, there’s probably an easier way to do this, but since I was only doing this once I was lazy.
- Drop the “tag_info” view as we done with it.
- Update the counts in BBPress’s admin area under “Manage.”
- Check to make sure everything works as expected. When I first did this, my forum counts were off so I had to make some modifications.
- At this point, you can optionally delete your XDForum tables, but there’s no technical reason to.
- Announce your success to your community.
This worked pretty well for my installation, but I only had a couple of hundred threads, about 600 posts and 50 or so forums. Your mileage may vary.
I looked for something like this, but couldn’t find it. If this saves you some time and you find it useful, consider making a donation to my PayPal account.
August 27, 2008 at 6:45 pm
Howdy there.
Great job sorting this out! I’ve been trying to work through your instructions and got hung up on step 7. I get an SQL syntax error, which I’ll paste below.
Any chance you might know what I’m doing wrong?
Error
SQL query: Documentation
CREATE VIEW topic_info AS SELECT wp_xdforum_posts.xp_xt_id AS topic_ID, COUNT( wp_xdforum_posts.xp_xt_id ) AS my_count, xp_author_ip AS author_ip, FROM_UNIXTIME( MIN( xp_date ) ) AS post_time, FROM_UNIXTIME( MAX( xp_date ) ) AS last_post_time
FROM wp_xdforum_posts
GROUP BY xp_xt_id
ORDER BY xp_xt_id, post_time DESC
MySQL said: Documentation
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘VIEW topic_info AS SELECT wp_xdforum_posts.xp_xt_id AS topic_ID, COUNT(wp_xdforu’ at line 1
August 27, 2008 at 7:30 pm
It could be that your server is using an earlier version of MySQL as views were introduced in version 5.
However, the SQL statement you posted was after a view had been used earlier so probably not. Does just the select statement work for you?
SELECT xp_xt_id AS topic_ID, COUNT(xp_xt_id) AS my_count, xp_author_ip AS author_ip, FROM_UNIXTIME( MIN( xp_date ) ) AS post_time, FROM_UNIXTIME( MAX( xp_date ) ) AS last_post_time
FROM wp_xdforum_posts
GROUP BY xp_xt_id
ORDER BY xp_xt_id, post_time DESC
If that works, then most likely you’re using a version of MySQL that doesn’t support subselects. If that’s the case, you’ll have to figure out how to recreate the view into a temporary table so you can update the counts, which is the whole purpose of the view, which just displays the number of items in the thread, the date the topic was started and the date it was last updated.
August 27, 2008 at 7:41 pm
The Select statement seems to work (in that it doesn’t return an error).
I’m using MySQL 5.0.41
I’m not that schooled at SQL in general – should I be trying to run this from the main SQL tab or from inside one of the tables. (Maybe that doesn’t even matter….)
Thanks for your reply!
August 28, 2008 at 1:55 pm
My bad, I /was/ using a 4.x version of MySQL, though the “MySQL client version 5.0.41” in phpMyAdmin thing was a bit of a throw off for the novice such as myself.