Splatt to PHPBB - Serious Advice Needed

mcoupa · New Member Joined: Dec 02, 2004 Posts: 12

Hi

My Splatt forums are already 33 000 posts and it doesn't look like Splatt will be releasing new versions soon so I decided to move over to PHPBB forum.

I have decided to use the NSN Move script, here is what the code looks like in full view:

Code:

<?php

/********************************************************/
/* Move for PHP-Nuke 6.0.0 Splatt to phpBB port */
/* By: NukeScripts Network (webmaster@nukescripts.net) */
/* http://www.nukescripts.net */
/* Copyright © 2003 by NukeScripts Network */
/********************************************************/
/* While this script contains the NSN copyright it is */
/* released as GPL as it uses some code from the phpBB */
/* port by Tom Nitzschner (tom@toms-home.com) */
/********************************************************/

ob_start("ob_gzhandler");
require_once("config.php");
require_once("includes/sql_layer.php");
global $prefix;
$dbi = sql_connect($dbhost, $dbuname, $dbpass, $dbname);

function make_bbcode_uid() {
// Unique ID for this message..
$uid = md5(mt_rand());
$uid = substr($uid, 0, 10);
return $uid;
}

//Insert categories info
$result1 = sql_query("SELECT cat_id, cat_title, cat_order FROM ".$prefix."_catagories ORDER BY cat_id", $dbi);
while (list($cat_id, $cat_title, $cat_order) = sql_fetch_row($result1, $dbi)) {
if(!sql_query("INSERT INTO ".$prefix."_bbcategories VALUES ($cat_id, '$cat_title', $cat_order)", $dbi)) {
printf(" Error in category transfer! \n");
}
}
echo "Categories Transfered! \n";

//Insert forums info
$result2 = sql_query("SELECT forum_id, forum_name, forum_desc, cat_id FROM ".$prefix."_forums ORDER BY cat_id, forum_id", $dbi);
while (list($forum_id, $forum_name, $forum_desc, $cat_id) = sql_fetch_row($result2, $dbi)) {
$forum_posts = sql_num_rows(sql_query("SELECT * FROM ".$prefix."_posts WHERE forum_id='$forum_id'", $dbi), $dbi);
$forum_topics = sql_num_rows(sql_query("SELECT * FROM ".$prefix."_forumtopics WHERE forum_id='$forum_id'", $dbi), $dbi);
if(!sql_query("INSERT INTO ".$prefix."_bbforums VALUES ($forum_id, $cat_id, '$forum_name', '$forum_desc', 0, 0, $forum_posts, $forum_topics, 0, NULL, 0, 0, 0, 1, 1, 1, 5, 5, 3, 1, 5, 0)", $dbi)) {
printf(" Error in $forum_name transfer! \n");
}
echo "Forum = $forum_id - Posts = $forum_posts - Topics = $forum_topics \n";
}
echo "Forums Transfered! \n";

// Insert topics info
$result3 = sql_query("SELECT topic_id, topic_title, topic_poster, topic_time, topic_views, forum_id, topic_status FROM ".$prefix."_forumtopics", $dbi);
while (list($topic_id, $topic_title, $topic_poster, $topic_time, $topic_views, $forum_id, $topic_status) = sql_fetch_row($result3, $dbi)) {
$topic_title = stripslashes($topic_title);
$topic_title = ereg_replace("\'","´",$topic_title);
$topic_title = ereg_replace("\"",""",$topic_title);
$topic_title = addslashes($topic_title);
$topic_replies = sql_num_rows(sql_query("SELECT * FROM ".$prefix."_posts WHERE topic_id='$topic_id'", $dbi), $dbi);
$topic_time = strtotime($topic_time);
if(!sql_query("INSERT INTO ".$prefix."_bbtopics VALUES ('$topic_id', '$forum_id', '$topic_title', '$topic_poster', '$topic_time', '$topic_views', '$topic_replies', '$topic_status', '', '', '', '', '')", $dbi)) {
echo " Error in $topic_title transfer! \n";
}
}
echo "Topics Transfered! \n";

// Insert posts info
$result5 = sql_query("SELECT post_id, topic_id, forum_id, poster_id, post_text, post_time, poster_ip FROM ".$prefix."_posts", $dbi);
while (list($post_id, $topic_id, $forum_id, $poster_id, $post_text, $post_time, $poster_ip) = sql_fetch_row($result5, $dbi)) {
$ip_sep = explode('.', $poster_ip);
$poster_dip = sprintf('%02x%02x%02x%02x', $ip_sep[0], $ip_sep[1], $ip_sep[2], $ip_sep[3]);
$post_text = stripslashes($post_text);
$post_text = ereg_replace("\'","´",$post_text);
$post_text = ereg_replace("\"",""",$post_text);
$post_text = ereg_replace("\[addsig\]","",$post_text);
$post_text = addslashes($post_text);
$post_time = strtotime($post_time);
$bbcode_uid = md5(mt_rand());
$bbcode_uid = substr($bbcode_uid, 0, 10);
list($post_username) = sql_fetch_row(sql_query("SELECT uname FROM ".$user_prefix."_users WHERE uid='$poster_id'", $dbi), $dbi);
list($post_subject) = sql_fetch_row(sql_query("SELECT topic_title FROM ".$prefix."_bbtopics WHERE topic_id='$topic_id'", $dbi), $dbi);
$post_subject = stripslashes($post_subject);
$post_subject = ereg_replace("\'","´",$post_subject);
$post_subject = ereg_replace("\"",""",$post_subject);
$post_subject = addslashes($post_subject);
if(!sql_query("INSERT INTO ".$prefix."_bbposts VALUES ($post_id, $topic_id, $forum_id, $poster_id, $post_time, '$poster_dip', '$post_username', 1, 0, 1, 1, NULL, 0)", $dbi)) {
echo " Error in $post_id Post transfer! \n";
}
if(!sql_query("INSERT INTO ".$prefix."_bbposts_text VALUES ($post_id, '$bbcode_uid', '$post_subject', '$post_text')", $dbi)) {
echo " Error in $post_id Text transfer! \n";
}
}
echo "Posts Transfered! \n";

// Insert first and last post for each topic
$result6 = sql_query("SELECT topic_id FROM ".$prefix."_bbtopics", $dbi);
while (list($topic_id) = sql_fetch_row($result6, $dbi)) {
list($firstpost) = sql_fetch_row(sql_query("SELECT post_id FROM ".$prefix."_bbposts WHERE topic_id='$topic_id' ORDER BY post_id LIMIT 0,1", $dbi), $dbi);
if(!sql_query("UPDATE ".$prefix."_bbtopics SET topic_first_post_id='$firstpost' WHERE topic_id='$topic_id'", $dbi)) {
echo " Error in $topic_title first post update! \n";
}
list($lastpost) = sql_fetch_row(sql_query("SELECT post_id FROM ".$prefix."_bbposts WHERE topic_id='$topic_id' ORDER BY post_id DESC LIMIT 0,1", $dbi), $dbi);
if(!sql_query("UPDATE ".$prefix."_bbtopics SET topic_last_post_id='$lastpost' WHERE topic_id='$topic_id'", $dbi)) {
echo " Error in $topic_title first post update! \n";
}
}
echo "Topics Updated! \n";

?>

but if I look at my db it doesn't seem to match (because I remember Splatt changing the Table names ) and I don't want to run for fear of loosing data. So let me go through each piece to show you what I mean:

Code:

//Insert categories info
$result1 = sql_query("SELECT cat_id, cat_title, cat_order FROM ".$prefix."_catagories ORDER BY cat_id", $dbi);
while (list($cat_id, $cat_title, $cat_order) = sql_fetch_row($result1, $dbi)) {
if(!sql_query("INSERT INTO ".$prefix."_bbcategories VALUES ($cat_id, '$cat_title', $cat_order)", $dbi)) {
printf(" Error in category transfer! \n");
}
}
echo "Categories Transfered! \n";

First the

Code:

FROM ".$prefix."_catagories ORDER BY cat_id", $dbi);

part in my mind should read

Code:

FROM ".$prefix."_splattforum_catagories ORDER BY cat_id", $dbi);

because that's the name of my table.

next one:

Code:

//Insert forums info
$result2 = sql_query("SELECT forum_id, forum_name, forum_desc, cat_id FROM ".$prefix."_forums ORDER BY cat_id, forum_id", $dbi);
while (list($forum_id, $forum_name, $forum_desc, $cat_id) = sql_fetch_row($result2, $dbi)) {
$forum_posts = sql_num_rows(sql_query("SELECT * FROM ".$prefix."_posts WHERE forum_id='$forum_id'", $dbi), $dbi);
$forum_topics = sql_num_rows(sql_query("SELECT * FROM ".$prefix."_forumtopics WHERE forum_id='$forum_id'", $dbi), $dbi);
if(!sql_query("INSERT INTO ".$prefix."_bbforums VALUES ($forum_id, $cat_id, '$forum_name', '$forum_desc', 0, 0, $forum_posts, $forum_topics, 0, NULL, 0, 0, 0, 1, 1, 1, 5, 5, 3, 1, 5, 0)", $dbi)) {
printf(" Error in $forum_name transfer! \n");
}
echo "Forum = $forum_id - Posts = $forum_posts - Topics = $forum_topics \n";
}
echo "Forums Transfered! \n";

again in this line :

Code:

$result3 = sql_query("SELECT topic_id, topic_title, topic_poster, topic_time, topic_views, forum_id, topic_status FROM ".$prefix."_forumtopics", $dbi);

should actually be:

Code:

$result3 = sql_query("SELECT topic_id, topic_title, topic_poster, topic_time, topic_views, forum_id, topic_status FROM ".$prefix."_splattforum_forumtopics", $dbi);

the rest appears fine but are two additional fields in the splattforum_forumtopics table that are not in this statement which is:
topic_notify and highlight. Not much of a problem for me if I don't carry that over.

next part:

Code:

// Insert posts info
$result5 = sql_query("SELECT post_id, topic_id, forum_id, poster_id, post_text, post_time, poster_ip FROM ".$prefix."_posts", $dbi);
while (list($post_id, $topic_id, $forum_id, $poster_id, $post_text, $post_time, $poster_ip) = sql_fetch_row($result5, $dbi)) {
$ip_sep = explode('.', $poster_ip);
$poster_dip = sprintf('%02x%02x%02x%02x', $ip_sep[0], $ip_sep[1], $ip_sep[2], $ip_sep[3]);
$post_text = stripslashes($post_text);
$post_text = ereg_replace("\'","´",$post_text);
$post_text = ereg_replace("\"",""",$post_text);
$post_text = ereg_replace("\[addsig\]","",$post_text);
$post_text = addslashes($post_text);
$post_time = strtotime($post_time);
$bbcode_uid = md5(mt_rand());
$bbcode_uid = substr($bbcode_uid, 0, 10);
list($post_username) = sql_fetch_row(sql_query("SELECT uname FROM ".$user_prefix."_users WHERE uid='$poster_id'", $dbi), $dbi);
list($post_subject) = sql_fetch_row(sql_query("SELECT topic_title FROM ".$prefix."_bbtopics WHERE topic_id='$topic_id'", $dbi), $dbi);
$post_subject = stripslashes($post_subject);
$post_subject = ereg_replace("\'","´",$post_subject);
$post_subject = ereg_replace("\"",""",$post_subject);
$post_subject = addslashes($post_subject);
if(!sql_query("INSERT INTO ".$prefix."_bbposts VALUES ($post_id, $topic_id, $forum_id, $poster_id, $post_time, '$poster_dip', '$post_username', 1, 0, 1, 1, NULL, 0)", $dbi)) {
echo " Error in $post_id Post transfer! \n";
}
if(!sql_query("INSERT INTO ".$prefix."_bbposts_text VALUES ($post_id, '$bbcode_uid', '$post_subject', '$post_text')", $dbi)) {
echo " Error in $post_id Text transfer! \n";
}
}
echo "Posts Transfered! \n";

again this line:

Code:

$result5 = sql_query("SELECT post_id, topic_id, forum_id, poster_id, post_text, post_time, poster_ip FROM ".$prefix."_posts", $dbi);

should be:

Code:

$result5 = sql_query("SELECT post_id, topic_id, forum_id, poster_id, post_text, post_time, poster_ip FROM ".$prefix."_splattforum_posts", $dbi);

also it doesn't have the image field in this statement but that's fine I don't need to do that.

over here:

Code:

list($post_username) = sql_fetch_row(sql_query("SELECT uname FROM ".$user_prefix."_users WHERE uid='$poster_id'", $dbi), $dbi);

I can't find uname in users table. I can only find username. Should I change that to username?

lastly we have this block:

Code:

// Insert first and last post for each topic
$result6 = sql_query("SELECT topic_id FROM ".$prefix."_bbtopics", $dbi);
while (list($topic_id) = sql_fetch_row($result6, $dbi)) {
list($firstpost) = sql_fetch_row(sql_query("SELECT post_id FROM ".$prefix."_bbposts WHERE topic_id='$topic_id' ORDER BY post_id LIMIT 0,1", $dbi), $dbi);
if(!sql_query("UPDATE ".$prefix."_bbtopics SET topic_first_post_id='$firstpost' WHERE topic_id='$topic_id'", $dbi)) {
echo " Error in $topic_title first post update! \n";
}
list($lastpost) = sql_fetch_row(sql_query("SELECT post_id FROM ".$prefix."_bbposts WHERE topic_id='$topic_id' ORDER BY post_id DESC LIMIT 0,1", $dbi), $dbi);
if(!sql_query("UPDATE ".$prefix."_bbtopics SET topic_last_post_id='$lastpost' WHERE topic_id='$topic_id'", $dbi)) {
echo " Error in $topic_title first post update! \n";
}
}
echo "Topics Updated! \n";

I don't think I have to make a change there.

so when you look at my splattforum db tables/fields they are structured like this:
table: nuke_splattforum_catagories
fields: cat_id, cat_title, cat_order
table: nuke_splattforum_forums
fields: forum_id, forum_name, forum_desc, forum_access, cat_id, forum_type, forum_pass, forum_notify_email, forum_atch, forum_rules, image_in_post
table: nuke_splattforum_forumtopics
fields:topic_id topic_title topic_poster topic_time topic_views forum_id topic_status topic_notify highlight
table: nuke_splattforum_posts
fields: post_id image topic_id forum_id poster_id post_text post_time poster_ip

so based on my observations I would have to change the script to this:

Code:

<?php

/********************************************************/
/* Move for PHP-Nuke 6.0.0 Splatt to phpBB port */
/* By: NukeScripts Network (webmaster@nukescripts.net) */
/* http://www.nukescripts.net */
/* Copyright © 2003 by NukeScripts Network */
/********************************************************/
/* While this script contains the NSN copyright it is */
/* released as GPL as it uses some code from the phpBB */
/* port by Tom Nitzschner (tom@toms-home.com) */
/********************************************************/

ob_start("ob_gzhandler");
require_once("config.php");
require_once("includes/sql_layer.php");
global $prefix;
$dbi = sql_connect($dbhost, $dbuname, $dbpass, $dbname);

function make_bbcode_uid() {
// Unique ID for this message..
$uid = md5(mt_rand());
$uid = substr($uid, 0, 10);
return $uid;
}

//Insert categories info
$result1 = sql_query("SELECT cat_id, cat_title, cat_order FROM ".$prefix."_splattforum_catagories ORDER BY cat_id", $dbi);
while (list($cat_id, $cat_title, $cat_order) = sql_fetch_row($result1, $dbi)) {
if(!sql_query("INSERT INTO ".$prefix."_bbcategories VALUES ($cat_id, '$cat_title', $cat_order)", $dbi)) {
printf(" Error in category transfer! \n");
}
}
echo "Categories Transfered! \n";

//Insert forums info
$result2 = sql_query("SELECT forum_id, forum_name, forum_desc, cat_id FROM ".$prefix."_splattforum_forums ORDER BY cat_id, forum_id", $dbi);
while (list($forum_id, $forum_name, $forum_desc, $cat_id) = sql_fetch_row($result2, $dbi)) {
$forum_posts = sql_num_rows(sql_query("SELECT * FROM ".$prefix."_splattform_posts WHERE forum_id='$forum_id'", $dbi), $dbi);
$forum_topics = sql_num_rows(sql_query("SELECT * FROM ".$prefix."_splattforum_forumtopics WHERE forum_id='$forum_id'", $dbi), $dbi);
if(!sql_query("INSERT INTO ".$prefix."_bbforums VALUES ($forum_id, $cat_id, '$forum_name', '$forum_desc', 0, 0, $forum_posts, $forum_topics, 0, NULL, 0, 0, 0, 1, 1, 1, 5, 5, 3, 1, 5, 0)", $dbi)) {
printf(" Error in $forum_name transfer! \n");
}
echo "Forum = $forum_id - Posts = $forum_posts - Topics = $forum_topics \n";
}
echo "Forums Transfered! \n";

// Insert topics info
$result3 = sql_query("SELECT topic_id, topic_title, topic_poster, topic_time, topic_views, forum_id, topic_status FROM ".$prefix."_splattforum_forumtopics", $dbi);
while (list($topic_id, $topic_title, $topic_poster, $topic_time, $topic_views, $forum_id, $topic_status) = sql_fetch_row($result3, $dbi)) {
$topic_title = stripslashes($topic_title);
$topic_title = ereg_replace("\'","´",$topic_title);
$topic_title = ereg_replace("\"",""",$topic_title);
$topic_title = addslashes($topic_title);
$topic_replies = sql_num_rows(sql_query("SELECT * FROM ".$prefix."_splattforum_posts WHERE topic_id='$topic_id'", $dbi), $dbi);
$topic_time = strtotime($topic_time);
if(!sql_query("INSERT INTO ".$prefix."_bbtopics VALUES ('$topic_id', '$forum_id', '$topic_title', '$topic_poster', '$topic_time', '$topic_views', '$topic_replies', '$topic_status', '', '', '', '', '')", $dbi)) {
echo " Error in $topic_title transfer! \n";
}
}
echo "Topics Transfered! \n";

// Insert posts info
$result5 = sql_query("SELECT post_id, topic_id, forum_id, poster_id, post_text, post_time, poster_ip FROM ".$prefix."_splattforum_posts", $dbi);
while (list($post_id, $topic_id, $forum_id, $poster_id, $post_text, $post_time, $poster_ip) = sql_fetch_row($result5, $dbi)) {
$ip_sep = explode('.', $poster_ip);
$poster_dip = sprintf('%02x%02x%02x%02x', $ip_sep[0], $ip_sep[1], $ip_sep[2], $ip_sep[3]);
$post_text = stripslashes($post_text);
$post_text = ereg_replace("\'","´",$post_text);
$post_text = ereg_replace("\"",""",$post_text);
$post_text = ereg_replace("\[addsig\]","",$post_text);
$post_text = addslashes($post_text);
$post_time = strtotime($post_time);
$bbcode_uid = md5(mt_rand());
$bbcode_uid = substr($bbcode_uid, 0, 10);
list($post_username) = sql_fetch_row(sql_query("SELECT uname FROM ".$user_prefix."_users WHERE uid='$poster_id'", $dbi), $dbi);
list($post_subject) = sql_fetch_row(sql_query("SELECT topic_title FROM ".$prefix."_bbtopics WHERE topic_id='$topic_id'", $dbi), $dbi);
$post_subject = stripslashes($post_subject);
$post_subject = ereg_replace("\'","´",$post_subject);
$post_subject = ereg_replace("\"",""",$post_subject);
$post_subject = addslashes($post_subject);
if(!sql_query("INSERT INTO ".$prefix."_bbposts VALUES ($post_id, $topic_id, $forum_id, $poster_id, $post_time, '$poster_ip', '$post_username', 1, 0, 1, 1, NULL, 0)", $dbi)) {
echo " Error in $post_id Post transfer! \n";
}
if(!sql_query("INSERT INTO ".$prefix."_bbposts_text VALUES ($post_id, '$bbcode_uid', '$post_subject', '$post_text')", $dbi)) {
echo " Error in $post_id Text transfer! \n";
}
}
echo "Posts Transfered! \n";

// Insert first and last post for each topic
$result6 = sql_query("SELECT topic_id FROM ".$prefix."_bbtopics", $dbi);
while (list($topic_id) = sql_fetch_row($result6, $dbi)) {
list($firstpost) = sql_fetch_row(sql_query("SELECT post_id FROM ".$prefix."_bbposts WHERE topic_id='$topic_id' ORDER BY post_id LIMIT 0,1", $dbi), $dbi);
if(!sql_query("UPDATE ".$prefix."_bbtopics SET topic_first_post_id='$firstpost' WHERE topic_id='$topic_id'", $dbi)) {
echo " Error in $topic_title first post update! \n";
}
list($lastpost) = sql_fetch_row(sql_query("SELECT post_id FROM ".$prefix."_bbposts WHERE topic_id='$topic_id' ORDER BY post_id DESC LIMIT 0,1", $dbi), $dbi);
if(!sql_query("UPDATE ".$prefix."_bbtopics SET topic_last_post_id='$lastpost' WHERE topic_id='$topic_id'", $dbi)) {
echo " Error in $topic_title first post update! \n";
}
}
echo "Topics Updated! \n";

?>

I think it might work but I need your advice first:
Will this script merely make a copy of what's in splatt to phpbb or will it remove the posts in splatt?
reason I'm asking is, if I do this and it doesn't work will I be left with a splatt forum with no posts???

can you make any other suggestings?

thanks, I know this is a long post but I want to do this right. thanks in advance for your assistance.

mcoupa · New Member Joined: Dec 02, 2004 Posts: 12

okay for this one

Code:

list($post_username) = sql_fetch_row(sql_query("SELECT uname FROM ".$user_prefix."_users WHERE uid='$poster_id'", $dbi), $dbi);

where i said

Quote:

I can't find uname in users table. I can only find username. Should I change that to username?

I realise now I should change it to username

so I only have one final question:

Will this script merely make a copy of what's in splatt to phpbb or will it remove the posts in splatt?
reason I'm asking is, if I do this and it doesn't work will I be left with a splatt forum with no posts???

PHrEEkie · Subject Matter Expert Joined: Feb 23, 2004 Posts: 250

I didn't spend the time to look through the code for any DROP or EMPTY queries, but it doesn't really matter. You should backup the DB prior to running -any- script over it. If disaster strikes, restore.

PHrEEk

mcoupa · New Member Joined: Dec 02, 2004 Posts: 12

I got it right. I made some changes and had to use the LIMIT as I had too many posts but it worked!