Splatt to PHPBB - Serious Advice Needed

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!<BR>\n");


    }


}


echo "Categories Transfered!<br>\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!<BR>\n");


    }


echo "Forum = $forum_id - Posts = $forum_posts - Topics = $forum_topics<br>\n";


}


echo "Forums Transfered!<br>\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("\'","&acute;",$topic_title);


    $topic_title = ereg_replace("\"","&quot;",$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!<BR>\n";


    }


}


echo "Topics Transfered!<br>\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("\'","&acute;",$post_text);


    $post_text = ereg_replace("\"","&quot;",$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("\'","&acute;",$post_subject);


    $post_subject = ereg_replace("\"","&quot;",$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!<BR>\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!<BR>\n";


    }


}


echo "Posts Transfered!<br>\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!<BR>\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!<BR>\n";


    }


}


echo "Topics Updated!<br>\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!<BR>\n");


    }


}


echo "Categories Transfered!<br>\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!<BR>\n");


    }


echo "Forum = $forum_id - Posts = $forum_posts - Topics = $forum_topics<br>\n";


}


echo "Forums Transfered!<br>\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("\'","&acute;",$post_text);


    $post_text = ereg_replace("\"","&quot;",$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("\'","&acute;",$post_subject);


    $post_subject = ereg_replace("\"","&quot;",$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!<BR>\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!<BR>\n";


    }


}


echo "Posts Transfered!<br>\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!<BR>\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!<BR>\n";


    }


}


echo "Topics Updated!<br>\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!<BR>\n");


    }


}


echo "Categories Transfered!<br>\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!<BR>\n");


    }


echo "Forum = $forum_id - Posts = $forum_posts - Topics = $forum_topics<br>\n";


}


echo "Forums Transfered!<br>\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("\'","&acute;",$topic_title);


    $topic_title = ereg_replace("\"","&quot;",$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!<BR>\n";


    }


}


echo "Topics Transfered!<br>\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("\'","&acute;",$post_text);


    $post_text = ereg_replace("\"","&quot;",$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("\'","&acute;",$post_subject);


    $post_subject = ereg_replace("\"","&quot;",$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!<BR>\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!<BR>\n";


    }


}


echo "Posts Transfered!<br>\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!<BR>\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!<BR>\n";


    }


}


echo "Topics Updated!<br>\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.

Posted: Fri Jan 21, 2005 7:59 am

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???

Subject Matter Expert Joined: Feb 23, 2004 Posts: 358

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

Posted: Sat Jan 22, 2005 12:43 pm

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