Forum module: Discussion thread for converting PHPBB data

By the way, I just wanted to let you guys know that I did post info to the wiki on how to export a SQL table in the CSV format, make some changes to that data, and then upload it into another database. This is important information for the whole process and assumes a general knowledge of PHPMyAdmin.

I am now working on info on mapping the PHPBB database tables to the EE database tables. Stay tuned!

http://www.eewiki.com/wiki/PHPBB_Import

P.S. Thanks to Lisa for creating the Wiki for everyone and taking the time to show me how to use it.

OH wow, it looks like I have missed a lot. I am going to take a look at all of this and see how it works. THANKS!!!

Yeah, I posted the info for bringing data into a spreadsheet and then exporting it back into the database but I have not finished the mapping of the database yet. The problem is mapping and documentation is a pretty big process (so you can imagine that actually writing a script would be nightmarish) and I just don’t have the time to sit down and do it all at once. So basically I work on it for a while and then forget it about until someone posts here 😊

So I suppose it is time to work on it some more…

Yes i do agree with tuder. Kindly help me… Thanx

I’ve been hoping for a solution from the pMachine-team, but since I’m running out of time I have to dig in for a more dirty solution. So the next couple of weeks I’m trying to find some spare hours to come up with something. I hope that there are a couple of people who are interested to participate.

The problem is probably pretty complex, but looking through the tables from both EE and Phpbb it seems to me, that it should be possible to do. It probably depends on how EE organizes it’s data, but I

I am working on a module to do this. I have a couple of questions.

Are you going to move a PHPBB forum into a fresh EE forum installation? Are there other members in your EE system? How many posts are you dealing with? What time frame do you need this to be done in?

Right now, I am working on a module that can merge a PHPBB forum into a populated EE forum, but this is more difficult than doing the same with a clean EE system.

If you have a few weeks, and you are willing to import into a fresh install of EE, then I could probably do this for you. We could speak further about this offline, and I would probably charge a fee because of the effort involved.

I am working on a module to do this. I have a couple of questions. Are you going to move a PHPBB forum into a fresh EE forum installation? Are there other members in your EE system? How many posts are you dealing with? What time frame do you need this to be done in? Right now, I am working on a module that can merge a PHPBB forum into a populated EE forum, but this is more difficult than doing the same with a clean EE system. If you have a few weeks, and you are willing to import into a fresh install of EE, then I could probably do this for you. We could speak further about this offline, and I would probably charge a fee because of the effort involved.

Let’s kick of with the questions: 1. Yes it’s a phpBB forum into a fresh EE. So therefor there’s only one admin member at the start. 2. There are about 40.000 posts at the moment. 3. I don’t have a final date yet. The website I currently own is completely non-commercial but it takes quite some spare time. The EE website will not only replace the current site, we’ll add a completely new functionality to it. But I’ll have to develop all that stuff in the time I have left in a week. I’m hoping for a release in may, but it could be anything else as well.. 4. Your offer is very interesting, but I won’t be able to “freeze” my current site for a few weeks, plus the fact that all income we generate is from some Google-ads, I’m afraid that hiring people to do this is not really an option. But the offer definitely is interesting to me, so I’ll think about it and when I can’t figure it out myself, I’ll get back to you on this.

My questions feel a bit awkward now, because it’s part of your income. But I still hope on some input from you or others to handle this. So If you don’t want this discussion in this thread, please let me know, I’ll open another one.

<

blockquote>Your offer is very interesting, but I won

I see your point and respect that. It’s a usefull module which is definitely worth a price. But the point I’m making is that I’m not in the right position to ask you to develop this module right now. I just can’t guarantee that I will be able to buy it. So for the moment, i’ll have to see the next couple of months what I can do myself, and hope you’re able to find some time to develop it.

<

blockquote>I see your point and respect that. It

If development of this process is still occuring, I’d be very interested in participating or helping to fund it. Let me know.

TTFN Travis

I don’t mean to be stringing you guys along with this. The idea has constantly been evolving and it became apparent that if I was going to make this a reality for normal people, I would have to build a module. I have been working with the Codeigniter framework to get my PHP skills up to par, and summer vacation is almost here. I will be getting started on the module again after finals week (which is this current week.)

Alright, so the other day I discovered myself in the awkward position of converting a PHPBB forum to EE. After a decent couple days of SQL-wrangling, I thought I’d let you know what I came up with.

A couple warnings, before I start:

1. This is a highly hackish process. The two forums are not exactly compatible, and it would be beyond the degree of a ponderous chore to convert things exactly. Consequently there’s some stuff that’s missing: namely polls, file attachments, private messages and security permissions. There could even be other stuff.

2. This is untested and could possibly damage your data. Back up your ee database before you try this. If you use this and it destroys all your data, sleeps with your girlfriend and steals your car, don’t say I didn’t warn you.

3. This was designed for my specific situation. There are some things I used that you might not want to include. I’ll try to indicate those, but regardless this isn’t necessarily a query you can plug straight into your database. It may require elbow grease.

Now, to get started. I had things set up such that there were two databases, called phpbbtemp and expression. I cleared (after backing up) all the exp_ tables that I added data to. This script wasn’t designed to merge things nicely. That said, I’ve recently hit upon the fact that adding 10000 or some large number to every id field will allow merging. See exp_forums below for an example of this.

INSERT INTO exp_members

    (member_id, 
    group_id, 
    username, screen_name, password, unique_id, 
    email, url, location, occupation, 
    aol_im, yahoo_im, msn_im, icq, 
    join_date, last_visit, total_forum_posts)


    SELECT
    
        user_id, 
        (CASE user_rank
            WHEN 0
                THEN 5
            WHEN 1
                THEN 1
            ELSE 7
        END), 
        username, username, user_password, md5(user_id), 
        user_email, user_website, user_from, user_occ, 
        user_aim, user_yim, user_msnm, user_icq, 
        user_regdate, user_lastvisit, user_posts
        
        
        FROM phpbbtemp.phpbb_users
        WHERE user_id > 0

Two things worthy of note there. Firstly I used a case statement to map forum ranks into group ids on exp engine… this may work out unsuitably for you, in which case you may want to get rid of that bit and just configure the groups using the site. Also, I filled unique_id values with the md5 of the user_id. I don’t know what unique_id is for, but I figured given that the user_ids are already unique, they’d be worth using as seed values.

INSERT INTO exp_forums
    (forum_id, forum_name, forum_order, forum_is_cat)

    SELECT
        cat_id*100, cat_title, cat_order, "y"
    FROM phpbbtemp.phpbb_categories

UPDATE exp_forums

Now, in phpbb categories and forums are in separate tables, but in ee forums they’re the same thing, so this required a bit of working around, here I multiplied the cat_id by 100 to avoid conflicts with other forum ids (adding whatever the highest forum_id was would have worked too, now that I think about it).

INSERT INTO exp_forums
    (forum_id, forum_name, forum_description, forum_parent, forum_order)
    
    SELECT
        forum_id, forum_name, forum_desc, cat_id*100, forum_order, 
    FROM phpbbtemp.phpbb_forums

Here we add the actual forums.

SET forum_permissions = CONCAT("a:7:{s:14:\"can_view_forum\";s:15:\"|2|3|5|7|4|6|1|\";s:15:\"can_view_hidden\"",
";s:3:\"|1|\";s:15:\"can_view_topics\";s:13:\"|3|5|7|4|6|1|\";s:15:\"can_post_topics\";s:9:\"|5|7|6|1|\";s:",
"14:\"can_post_reply\";s:9:\"|5|7|6|1|\";s:16:\"can_upload_files\";s:9:\"|5|7|6|1|\";s:10:\"can_search\";",
"s:13:\"|3|5|7|4|6|1|\";}"), 
forum_topics_perpage = 25, 
forum_posts_perpage = 15, 
forum_hot_topic = 10, 
forum_max_post_chars = 6000

This adds some default permissions as well as setting the topics per page and such… these might be overridden by ee if they’re zero, so maybe you don’t need to do this. I only did it because I was messing around while getting other stuff to work.

INSERT INTO exp_forum_topics 
    (topic_id, forum_id, author_id, last_post_author_id, 
    title, topic_date, thread_views, thread_total, poll)
SELECT 
    topic_id, forum_id, topic_poster, topic_poster, 
    topic_title, topic_time, topic_views, topic_replies, IF(topic_vote = 0, "n", "y")
    FROM phpbbtemp.phpbb_topics

Here we add the topics. There’s a little if statement there to convert the vote format used by phpbb into polls used by ee. That said, it turned out to be too much effort to migrate polls, so you can probably not bother with it if you don’t want to.

INSERT INTO exp_forum_posts 
(post_id, topic_id, forum_id, author_id, ip_address, body, post_date, post_edit_date)

SELECT
    P.post_id, P.topic_id, P.forum_id, P.poster_id, P.poster_ip, T.post_text, P.post_time, P.post_edit_time
        FROM phpbbtemp.phpbb_posts as P, phpbbtemp.phpbb_posts_text as T
        WHERE T.post_id = P.post_id

And here we add the posts. Note that phpbb keeps post text in a different table to the post info, so there’s a little joining where clause thing there.

However, after doing this I had the unwelcome surprise that the forum posts weren’t showing up. By going into the control panel, then to modules, forum, forum management and clicking “re-synchronise” posts, which fixed it. You probably also want to go to the admin page and then utilities to the thing about re-calculating statistical data. EE doesn’t expect you to suddenly change its database, so it’ll report things like member counts and forum posts wrongly until you do it.

Anyhow, I hope some of this helps someone in the same situation. Consider it released into the public domain.

Sam

Thanks for the contribution. This would work as a general guide on how the sql maps from PHPBB to EE, but there is still a sh** ton of work that still needs to be done. This is the very beginning of the process, and the easy part. 😉 There are much more complex problems to solve farther down the process, but certainly not insurmountable. I can’t elaborate on the process right now because I am work (then school, homework, sleep, rinse, repeat.)

I have been to this point but I did not release the SQL statements because they would not be functional. Running this code alone would make a mess of your forums. I would not advise this on a production forum. The people that are interested in this script will probably need a module anyways.