Code: Select all
INSERT INTO phpbb3_users( user_id, username, username_clean, user_email, user_password, user_regdate, user_lastvisit )
SELECT id, username, LOWER( username ) , email, `password`, UNIX_TIMESTAMP( registerDate ) , UNIX_TIMESTAMP( lastvisitDate )
FROM jos_users
Code: Select all
<?php
/*
Simpleboard 1.0.x to phbBB2 2.0.x converter
Based on the phpbb2tosb.php script by Olle Johansson http://mambo.theyard.org/
Notes from Marcos Osorno http://www.blort.com
* Tested on: phpBB 2.0.10, Mambo 4.5 1, PHP 4.3.9, MySQL 4.0.22
* Tested by executing from within the phpBB directory, elsewhere your mileage may vary
* Will DESTROY all the data in your phpBB db and replace it with SB data! Does not append!
* User features degraded from Olle's version this script doesn't check for
banned users and does little sanity checking on moderators.
-------------Config---------------------*/
//MySQL Database host - usually 'localhost'
$host='localhost';
//MySQL Database user
$user='database-username-goes-here';
//MySQL Database password
$pass='your-database-password-goes-here';
//MySQL Database name
$db='enter-database-name-here';
// Mambo database prefix
$mosConfig_dbprefix = "jos_";
// phpBB2 database prefix
$phpbb_dbprefix = "phpbb_";
// Temp DB prefix used for temporary tables in the script
// set this to a prefix you are NOT using on your mySQL db
$temp_dbprefix = "temp_";
/* ---------------------------------------*/
define("BBCODE_UID_LEN", 10);
$bbcode_on = 'true';
ini_set("magic_quotes_gpc", "1");
set_magic_quotes_runtime ( 0 );
echo "<?xml version=\"1.0\"?>";
echo "<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">";
echo "<html xmlns=\"http://www.w3.org/1999/xhtml\">";
echo "<head>";
echo "<title>Importing data from Simpleboard</title>";
echo "<meta http-equiv=\"Content-Type\" content=\"text/html; iso-8859-1\" />";
echo "</head>";
echo "<body>";
echo "<h1>Importing data from Simpleboard</h1>";
//Connect to and select Database
echo "Connecting to Database...\n";
$lnk = mysql_connect($host, $user, $pass) or die ('Not connected: ' . mysql_error());
mysql_select_db($db, $lnk) or die('Error: '.mysql_error());
echo "OK\n";
// PHPBB Categories > Forums > Topics > Posts > Posts Text
// Part One - Populate Categories
echo "<br />Populating Categories...\n";
// Import the main categories
// Prune those Simpleboard categories which have a parent
// Multiply the ordering by 10 to conform to phpbb2 format
// if fb_categories.parent = 0 then category is a phpbb cateogory
//clear phpbb categories table
$query="TRUNCATE TABLE {$phpbb_dbprefix}categories";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
// if fb_categories.parent = 0 then category is a category
$query="INSERT INTO {$phpbb_dbprefix}categories (cat_id, cat_title, cat_order) SELECT id, name, ordering*10 FROM {$mosConfig_dbprefix}fb_categories WHERE {$mosConfig_dbprefix}fb_categories.parent = '0' ORDER by ordering";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
// clear phpbb forums table
$query="TRUNCATE TABLE {$phpbb_dbprefix}forums";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
// Part Two - Populate Forums
// phpbb2 considers category header messages as forums
// these are indicated in sb by setting fb_messages.id=sb_messages.thread
// the actual messages can be detected by seeing that fb_messages.parent != 0
// create a table for counting the number of posts
//remove temp table
$query = "DROP TABLE IF EXISTS {$temp_dbprefix}minmaxmesg";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
// count the topics
$query = "CREATE TABLE {$temp_dbprefix}topics SELECT catid, COUNT(*) AS topics FROM {$mosConfig_dbprefix}fb_messages WHERE id=thread GROUP BY catid;";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
// count the number of posts
$query = "CREATE TABLE {$temp_dbprefix}posts SELECT catid, COUNT(*) AS posts FROM {$mosConfig_dbprefix}fb_messages GROUP BY catid;";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
// find the last post numericaly
$query = "CREATE TABLE {$temp_dbprefix}last SELECT catid, MAX(id) AS last FROM {$mosConfig_dbprefix}fb_messages GROUP BY catid;";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
// join table one and two
$query = "CREATE TABLE {$temp_dbprefix}join_1 SELECT a.catid, a.topics, b.posts FROM {$temp_dbprefix}topics AS a, {$temp_dbprefix}posts AS b WHERE a.catid=b.catid;";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
// join the previous table and three
$query = "CREATE TABLE {$temp_dbprefix}join_2 SELECT a.catid, a.topics, a.posts, b.last FROM {$temp_dbprefix}join_1 AS a, {$temp_dbprefix}last AS b where a.catid=b.catid;";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
$query="TRUNCATE TABLE {$phpbb_dbprefix}forums";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
$query="INSERT INTO {$phpbb_dbprefix}forums"
."(forum_id, cat_id, forum_name, forum_desc, forum_status,"
." forum_order, forum_posts, forum_topics, forum_last_post_id, prune_next,"
." prune_enable, auth_view, auth_read, auth_post, auth_reply, auth_edit,"
." auth_delete, auth_sticky, auth_announce, auth_vote, auth_pollcreate,"
." auth_attachments)"
." SELECT f.id, f.parent, f.name, f.description, 0,"
." ordering, c.posts, c.topics, c.last,'',"
." 0,0,1,1,1,"
." 1,1,3,3,1,"
." 1,0 FROM {$mosConfig_dbprefix}fb_categories AS f, temp_join_2 AS c WHERE f.parent != 0 AND c.catid = f.id;";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
// drop the temp tables
$query = "DROP TABLE {$temp_dbprefix}topics, {$temp_dbprefix}posts, {$temp_dbprefix}last, {$temp_dbprefix}join_1, {$temp_dbprefix}join_2;";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
// Part Three - Populate Topics
// if fb_messages.parent = 0 then messages is a topic
$query="TRUNCATE TABLE {$phpbb_dbprefix}topics";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
// create a table for finding min and max number of messages and the number of replies
$query = "CREATE TABLE {$temp_dbprefix}minmaxmesg SELECT thread, MIN(id) AS min, MAX(id) as max, COUNT(id) as reply FROM {$mosConfig_dbprefix}fb_messages GROUP BY thread;";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
// Run a query taking simple board messages and finding which are topics by thread=id then merge with count table above
// also subtract one from the reply count because it counts itself under this algorithm
$query = "SELECT m.id, m.catid, m.userid, m.subject, m.time, m.hits, reply - 1 AS reply, locked, 0, '', c.min, c.max, 0 FROM {$mosConfig_dbprefix}fb_messages AS m, {$temp_dbprefix}minmaxmesg AS c WHERE m.thread=m.id AND m.parent=0 AND c.thread=m.thread";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
while ( $row = mysql_fetch_object( $result ) ) {
// remove extra slashes
$subject = stripslashes( $row->subject );
// Some messages still had the single quote in them without a \
// which was causing import problems so we
// removed the single quote and replaced it with HTML for the quote
$subject = preg_replace ('/\'/', '\\\'', $subject);
$query="INSERT INTO {$phpbb_dbprefix}topics"
." (topic_id, forum_id, topic_poster, topic_title, topic_time,"
." topic_views, topic_replies, topic_status, topic_vote, topic_type,"
." topic_first_post_id, topic_last_post_id, topic_moved_id)"
." VALUES ('$row->id', '$row->catid', '$row->userid', '$subject', '$row->time',"
." '$row->hits', '$row->reply', '$row->locked', '0', '',"
." '$row->min', '$row->max', '0')";
$updateresult = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
}
mysql_free_result($result);
// drop the count table
$query = "DROP TABLE {$temp_dbprefix}minmaxmesg";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
echo "OK\n";
// Part Four - Populate Messages Table
echo "<br />Populating Posts...\n";
$query="TRUNCATE TABLE {$phpbb_dbprefix}posts";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
$query="INSERT INTO {$phpbb_dbprefix}posts (post_id, topic_id, forum_id, poster_id, post_time, poster_ip, post_username, enable_bbcode, enable_html, enable_smilies, enable_sig, post_edit_time, post_edit_count) SELECT id, thread, catid, userid, time, '', name, 1, 0, 1, 1, '', 0 FROM {$mosConfig_dbprefix}fb_messages";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
echo "OK\n";
//Populate Messages_Text Table
echo "<br />Populating Messages_Text Table...\n";
$query="TRUNCATE TABLE {$phpbb_dbprefix}posts_text";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
$query="SELECT m.id, '', m.subject, t.message FROM {$mosConfig_dbprefix}fb_messages_text AS t, {$mosConfig_dbprefix}fb_messages AS m WHERE m.id = t.mesid";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
while ( $row = mysql_fetch_object( $result ) ) {
// uses phpbb's bbcode encoder to make a uid for each message
// which is required for the bbcode to work in phpbb
$bbcode_uid = ( $bbcode_on ) ? make_bbcode_uid() : '';
$message = prep ( $row->message);
$subject = stripslashes( $row->subject );
$subject = preg_replace ('/\'/', '\\\'', $subject);
$query = "INSERT INTO {$phpbb_dbprefix}posts_text (post_id, bbcode_uid, post_subject, post_text) VALUES ('$row->id','$bbcode_uid', '$subject', '$message')";
$updateresult = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
}
mysql_free_result($result);
echo "OK\n";
//Populate Users Table
echo "<br />Populating Users Table...\n";
$query="TRUNCATE TABLE {$phpbb_dbprefix}users";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
$query="SELECT id, gid, username, password, lastvisitDate, registerDate, email FROM {$mosConfig_dbprefix}users ORDER by id;";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
while ( $row = mysql_fetch_object( $result ) ) {
$lastvisitDate = strtotime($row->lastvisitDate);
$registerDate = strtotime($row->registerDate);
// I had some users with no registration date, set them to October 1, 2004
if ($registerDate == '0') {
$registerDate = '1096671600';
}
// Set moderators based on Site and Main admin from mambo
if ($row->gid == '24' or $row->gid == '25') {
$moderator = '1';
} else {
$moderator = '0';
}
$query="INSERT INTO {$phpbb_dbprefix}users"
."(user_id, user_active, username, user_password, user_session_time,"
." user_session_page, user_lastvisit, user_regdate, user_level, user_posts,"
." user_timezone, user_style, user_lang, user_dateformat, user_new_privmsg,"
." user_unread_privmsg, user_last_privmsg, user_emailtime, user_viewemail, user_attachsig,"
." user_allowhtml, user_allowbbcode, user_allowsmile, user_allowavatar, user_allow_pm,"
." user_allow_viewonline, user_notify, user_notify_pm, user_popup_pm, user_rank,"
." user_avatar, user_avatar_type, user_email, user_icq, user_website,"
." user_from, user_sig, user_sig_bbcode_uid, user_aim, user_yim,"
." user_msnm, user_occ, user_interests, user_actkey, user_newpasswd)"
." VALUES("
." '$row->id', 1, '$row->username', '$row->password', '$lastvisitDate',"
." 0, '$lastvisitDate', '$registerDate', '$moderator', 0,"
." -6, 1, 'english', 'D M d, Y g:i a', 0,"
." 0, 0, 0, 0, 1,"
." 1, 1, 1, 1, 1,"
." 1, 1, 1, 1, 0,"
." '', '', '$row->email', '', '',"
." '', '', '', '', '',"
." '', '', '', '', '')";
$updateresult = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
}
mysql_free_result($result);
// take the number of posts from SB and put them into phpbb
$query="UPDATE {$phpbb_dbprefix}users, {$mosConfig_dbprefix}fb_users SET user_posts=posts WHERE user_id=userid;";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
// insert signatures
$query="UPDATE {$phpbb_dbprefix}users, {$mosConfig_dbprefix}fb_users SET user_sig=signature WHERE user_id=userid;";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
// clean up bbcode in signatures and ad bbcode_uid
$query="SELECT user_id, user_sig FROM {$phpbb_dbprefix}users ORDER BY user_id;";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
while ( $row = mysql_fetch_object( $result ) ) {
// uses phpbb's bbcode encoder to make a uid for each message
// which is required for the bbcode to work in phpbb
$bbcode_uid = ( $bbcode_on ) ? make_bbcode_uid() : '';
$signature = prep ( $row->user_sig);
$query = "UPDATE {$phpbb_dbprefix}users SET user_sig='$signature', user_sig_bbcode_uid='$bbcode_uid' WHERE $row->user_id=user_id";
$updateresult = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
}
mysql_free_result($result);
// create a group for each user
$query="TRUNCATE TABLE {$phpbb_dbprefix}groups";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
$query="INSERT INTO {$phpbb_dbprefix}groups (group_id, group_type, group_name, group_description, group_moderator, group_single_user) SELECT user_id, '1', '', 'Personal User', '0', '1' FROM {$phpbb_dbprefix}users ORDER BY user_id";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
// have each user join their own group
$query="TRUNCATE TABLE {$phpbb_dbprefix}user_group";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
$query="INSERT INTO {$phpbb_dbprefix}user_group (group_id, user_id, user_pending) SELECT user_id, user_id, '0' FROM {$phpbb_dbprefix}users ORDER BY user_id";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
echo "OK\n";
echo "<br />Complete...\n";
echo "</body>";
echo "</html>";
//--- Rewrite phpBB ip ---//
function phpbb_decode_ip($int_ip)
{
if ($int_ip == '00000000') {
return '0.0.0.0';
} else {
return long2ip("0x{$int_ip}");
}
}
//--- Function to prepare strings for MySQL storage ---/
function prep($s) {
// Insert bbcode_uid necessary for bbcode to be recognized by phpbb2
global $bbcode_uid;
// <strong> </strong>
$s = preg_replace('/\[b\]/', '[b:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[\/b\]/', '[/b:'.$bbcode_uid.']', $s);
// <em> </em>
$s = preg_replace('/\[i\]/', '[i:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[\/i\]/', '[/i:'.$bbcode_uid.']', $s);
// <u> </u>
$s = preg_replace('/\[u\]/', '[u:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[\/u\]/', '[/u:'.$bbcode_uid.']', $s);
// quote
$s = preg_replace('/\[quote\]/', '[quote:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[\/quote\]/', '[/quote:'.$bbcode_uid.']', $s);
// url with "
$s = preg_replace('/\[url=\\\"\;(.*?)\\\"\;\](.*?)/', '[url=\\1]\\2', $s);
// image
$s = preg_replace('/\[img(.*?)\](.*?)\[\/img(.*?)\]/si', '[img:'.$bbcode_uid.']'.'\\2[/img:'.$bbcode_uid.']', $s);
// color
$s = preg_replace('/\[color=(.*?)\]/', '[color=\\1:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[\/color\]/', '[/color:'.$bbcode_uid.']', $s);
// size
// sb uses size 1 to 5
// phpbb2 uses size 1 to 29
// will use 1 = 7, 2=9, 3=12, 4=18, 5=24
$s = preg_replace('/\[size=1\]/', '[size=7:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[size=2\]/', '[size=9:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[size=3\]/', '[size=12:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[size=4\]/', '[size=18:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[size=5\]/', '[size=24:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[\/size\]/', '[/size:'.$bbcode_uid.']', $s);
// code
$s = preg_replace('/\[code\]/','[code:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[\/code\]/', '[/code:'.$bbcode_uid.']', $s);
// lists
$s = preg_replace('/\[ul\]/', '[list'.$bbcode_uid.']', $s);
$s = preg_replace('/\[ol\]/', '[list=1:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[li\]/', '[*:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[\/li\]/', '', $s);
$s = preg_replace('/\[\/ul\]/', '[list:u:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[\/ol\]/', '[list:o:'.$bbcode_uid.']', $s);
return $s;
}
// function taken from phpbb2 functions file
function make_bbcode_uid()
{
// Unique ID for this message..
$uid = md5(mt_rand());
$uid = substr($uid, 0, BBCODE_UID_LEN);
return $uid;
}
?>
Code: Select all
<?php
/*
Fireboard 1.0.x to phbBB2 2.0.x converter
Based on the phpbb2tosb.php script by Olle Johansson http://mambo.theyard.org/ and from Marcos Osorno http://www.blort.com
Based upon updated script from http://www.phpbb.com/community/viewtopic.php?f=10&t=371271&p=2026820
* Tested on: phpBB 2.0.22, Joomla 1.0.13 w/ Fireboard 1.0.3, PHP 5.2.3, MySQL 5.0.45
* Tested by executing from within the phpBB directory
* Will DESTROY all the data in your phpBB db and replace it with SB data! Does not append!
-------------Config---------------------*/
//MySQL Database host - usually 'localhost'
$host='localhost';
//MySQL Database user
$user='database-username-goes-here';
//MySQL Database password
$pass='your-database-password-goes-here';
//MySQL Database name
$db='enter-database-name-here';
// Mambo database prefix
$mosConfig_dbprefix = "jos_";
// phpBB2 database prefix
$phpbb_dbprefix = "phpbb_";
// Temp DB prefix used for temporary tables in the script
// set this to a prefix you are NOT using on your mySQL db
$temp_dbprefix = "temp_";
/* ---------------------------------------*/
define("BBCODE_UID_LEN", 10);
$bbcode_on = 'true';
ini_set("magic_quotes_gpc", "1");
set_magic_quotes_runtime ( 0 );
echo "<?xml version=\"1.0\"?>";
echo "<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">";
echo "<html xmlns=\"http://www.w3.org/1999/xhtml\">";
echo "<head>";
echo "<title>Importing data from Fireboard</title>";
echo "<meta http-equiv=\"Content-Type\" content=\"text/html; iso-8859-1\" />";
echo "</head>";
echo "<body>";
echo "<h1>Importing data from Fireboard</h1>"; flush();
//Connect to and select Database
echo "Connecting to Database...\n"; flush();
$lnk = mysql_connect($host, $user, $pass) or die ('Not connected: ' . mysql_error());
mysql_select_db($db, $lnk) or die('Error: '.mysql_error());
echo "OK\n"; flush();
// PHPBB Categories > Forums > Topics > Posts > Posts Text
// Part One - Populate Categories
echo "<br />Populating Categories...\n"; flush();
// Import the main categories
// Prune those Fireboard categories which have a parent
// Multiply the ordering by 10 to conform to phpbb2 format
// if fb_categories.parent = 0 then category is a phpbb cateogory
//clear phpbb categories table
$query = "TRUNCATE TABLE {$phpbb_dbprefix}categories";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
// if fb_categories.parent = 0 then category is a category
$query = "INSERT INTO {$phpbb_dbprefix}categories (cat_id, cat_title, cat_order)
SELECT id, name, ordering*10
FROM {$mosConfig_dbprefix}fb_categories
WHERE {$mosConfig_dbprefix}fb_categories.parent = '0'
ORDER by ordering";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
//Count Rows Inserted
$query = "SELECT COUNT(*)
FROM {$phpbb_dbprefix}categories;";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
list($count) = mysql_fetch_row($result);
echo "<br />Inserted $count Rows... OK"; flush();
// clear phpbb forums table
$query = "TRUNCATE TABLE {$phpbb_dbprefix}forums";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
// Part Two - Populate Forums
// phpbb2 considers category header messages as forums
// these are indicated in sb by setting fb_messages.id=sb_messages.thread
// the actual messages can be detected by seeing that fb_messages.parent != 0
echo "<br />Populating Forums...\n"; flush();
$query = "INSERT INTO {$phpbb_dbprefix}forums (forum_id, cat_id, forum_name, forum_desc, forum_status, forum_order, forum_posts, forum_topics, forum_last_post_id, prune_next, prune_enable, auth_view, auth_read, auth_post, auth_reply, auth_edit,auth_delete, auth_sticky, auth_announce, auth_vote, auth_pollcreate, auth_attachments)
SELECT id, parent, name, description, 0, ordering, c.posts, b.topics, d.last, NULL, 0, 0, 1, 1, 1, 1, 1, 3, 3, 1, 1, 0
FROM {$mosConfig_dbprefix}fb_categories
LEFT JOIN (SELECT catid, COUNT(*) AS topics FROM {$mosConfig_dbprefix}fb_messages WHERE id=thread GROUP BY catid) b
ON {$mosConfig_dbprefix}fb_categories.id=b.catid
LEFT JOIN (SELECT catid, COUNT(*) AS posts FROM {$mosConfig_dbprefix}fb_messages GROUP BY catid) c
ON {$mosConfig_dbprefix}fb_categories.id=c.catid
LEFT JOIN (SELECT catid, MAX(id) AS last FROM {$mosConfig_dbprefix}fb_messages GROUP BY catid) d
ON {$mosConfig_dbprefix}fb_categories.id=d.catid
WHERE parent != 0
GROUP BY id;";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
//Count Rows Inserted
$query = "SELECT COUNT(*)
FROM {$phpbb_dbprefix}forums;";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
list($count) = mysql_fetch_row($result);
echo "<br />Inserted $count Rows... OK"; flush();
// Part Three - Populate Topics
// if fb_messages.parent = 0 then messages is a topic
echo "<br />Populating Topics...\n"; flush();
$query = "TRUNCATE TABLE {$phpbb_dbprefix}topics";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
// Some messages still had the single quote in them without a \
// which was causing import problems so we
// removed the single quote and replaced it with HTML for the quote
// Run a query taking simple board messages and finding which are topics by thread=id then merge with count table above
// also subtract one from the reply count because it counts itself under this algorithm
$query = "SELECT m.id, m.catid, m.userid, m.subject, m.time, m.hits, reply - 1 AS reply, locked, '0', '0', c.min, c.max, '0'
FROM {$mosConfig_dbprefix}fb_messages AS m
JOIN (SELECT thread, MIN(id) AS min, MAX(id) as max, COUNT(id) as reply
FROM {$mosConfig_dbprefix}fb_messages GROUP BY thread) c
WHERE m.thread=m.id AND m.parent=0 AND c.thread=m.thread";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
while ( $row = mysql_fetch_object( $result ) ) {
$subject = stripslashes( $row->subject );
$subject = preg_replace ('/\'/', '\\\'', $subject);
$query = "INSERT INTO {$phpbb_dbprefix}topics(topic_id, forum_id, topic_poster, topic_title, topic_time,topic_views, topic_replies, topic_status, topic_vote, topic_type,topic_first_post_id, topic_last_post_id, topic_moved_id)VALUES ('$row->id', '$row->catid', '$row->userid', '$subject', '$row->time','$row->hits', '$row->reply', '$row->locked', '0', '','$row->min', '$row->max', '0')";
$updateresult = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
}
mysql_free_result($result);
//Count Rows Inserted
$query = "SELECT COUNT(*)
FROM {$phpbb_dbprefix}topics;";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
list($count) = mysql_fetch_row($result);
echo "<br />Inserted $count Rows... OK\n"; flush();
// Part Four - Populate Messages Table
echo "<br />Populating Posts...\n"; flush();
$query = "TRUNCATE TABLE {$phpbb_dbprefix}posts";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
$query = "INSERT INTO {$phpbb_dbprefix}posts (post_id, topic_id, forum_id, poster_id, post_time, poster_ip, post_username, enable_bbcode, enable_html, enable_smilies, enable_sig, post_edit_time, post_edit_count)
SELECT id, thread, catid, userid, time, HEX(INET_ATON(ip)), name, 1, 0, topic_emoticon, 1, NULL, 0 FROM {$mosConfig_dbprefix}fb_messages";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
$query = "SELECT COUNT(*) FROM {$phpbb_dbprefix}posts;";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
list($count) = mysql_fetch_row($result);
echo "<br />Inserted $count Rows... OK"; flush();
//Populate Messages_Text Table
echo "<br />Populating Post Text & Converting Fireboard Syntax to phhBB Syntax... (This may take a while)\n"; flush();
$query = "TRUNCATE TABLE {$phpbb_dbprefix}posts_text";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
$query = "SELECT m.id, m.subject, t.message
FROM {$mosConfig_dbprefix}fb_messages_text AS t, {$mosConfig_dbprefix}fb_messages AS m WHERE m.id = t.mesid";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
// uses phpbb's bbcode encoder to make a uid for each message
// which is required for the bbcode to work in phpbb
while ( $row = mysql_fetch_object( $result ) ) {
$bbcode_uid = ( $bbcode_on ) ? make_bbcode_uid() : '';
$message = prep ( $row->message);
$message = fixquotes ($message);
$subject = stripslashes( $row->subject );
$subject = preg_replace ('/\'/', '\\\'', $subject);
$query = "INSERT INTO {$phpbb_dbprefix}posts_text (post_id, bbcode_uid, post_subject, post_text)
VALUES ('$row->id','$bbcode_uid', '$subject', '$message')";
$updateresult = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
}
mysql_free_result($result);
//Count Rows Inserted
$query = "SELECT COUNT(*) FROM {$phpbb_dbprefix}posts_text;";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
list($count) = mysql_fetch_row($result);
echo "<br />Inserted $count Rows... OK\n"; flush();
//Populate Users Table , Note user_sig_bbcode_uid is a radom, md5 checksum
echo "<br />Populating Users Table From Joomla...\n";
$query = "TRUNCATE TABLE {$phpbb_dbprefix}users";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
$query = "INSERT INTO {$phpbb_dbprefix}users (user_id, user_active, username, user_password, user_session_time, user_lastvisit, user_regdate, user_level, user_dateformat, user_email)
SELECT id,
CASE block
WHEN 0 THEN 1
ELSE 0 END,
username,
password,
UNIX_TIMESTAMP(lastvisitDate),
UNIX_TIMESTAMP(lastvisitDate),
UNIX_TIMESTAMP(registerDate),
CASE gid
WHEN 25 THEN 1
WHEN 24 THEN 1
ELSE 0 END,
'd M Y H:i',
email
FROM {$mosConfig_dbprefix}users ORDER by id;";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
$query = "SELECT COUNT(*) FROM {$phpbb_dbprefix}users;";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
list($count) = mysql_fetch_row($result);
echo "<br />Inserted $count Users... OK\n"; flush();
//Update Info From Fireboard
echo "<br />Updating Users Table From Fireboard...\n";
$query = "UPDATE {$phpbb_dbprefix}users, {$mosConfig_dbprefix}fb_users
SET user_posts=posts, user_avatar=avatar, user_icq=ICQ, user_website=websitename, user_from=location, user_sig=signature, user_aim=AIM, user_yim=YIM, user_msnm=MSN, user_interests=personalText
WHERE user_id=userid;";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
// uses phpbb's bbcode encoder to make a uid for each message
// which is required for the bbcode to work in phpbb
// clean up bbcode in signatures and ad bbcode_uid
$query = "SELECT user_id, user_sig
FROM {$phpbb_dbprefix}users
ORDER BY user_id;";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
while ( $row = mysql_fetch_object( $result ) ) {
$bbcode_uid = ( $bbcode_on ) ? make_bbcode_uid() : '';
$signature = prep ( $row->user_sig);
$signature = preg_replace ('/\'/', '\\\'', $signature);
$query = "UPDATE {$phpbb_dbprefix}users
SET user_sig='$signature', user_sig_bbcode_uid='$bbcode_uid'
WHERE user_id=$row->user_id";
$updateresult = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
}
mysql_free_result($result);
$query = "SELECT COUNT(*) FROM {$phpbb_dbprefix}users;";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
list($count) = mysql_fetch_row($result);
//Clean up avatar, only for off site avatar, Onsite Avatars will be deleted
echo "<br />Updated $count Users... OK\n"; flush();
$query = "UPDATE {$phpbb_dbprefix}users
SET user_avatar_type = 3
WHERE user_avatar LIKE 'http://%';";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
$query = "UPDATE {$phpbb_dbprefix}users
SET user_avatar_type = 0, user_avatar = NULL
WHERE user_avatar NOT LIKE 'http://%';";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
// create a group for each user
echo "<br />Updating Users Groups...\n"; flush();
$query = "TRUNCATE TABLE {$phpbb_dbprefix}groups";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
$query = "INSERT INTO {$phpbb_dbprefix}groups (group_id, group_type, group_name, group_description, group_moderator, group_single_user)
SELECT user_id, '1', '', 'Personal User', '0', '1'
FROM {$phpbb_dbprefix}users ORDER BY user_id";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
// have each user join their own group
$query = "TRUNCATE TABLE {$phpbb_dbprefix}user_group";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
$query = "INSERT INTO {$phpbb_dbprefix}user_group (group_id, user_id, user_pending)
SELECT user_id, user_id, '0'
FROM {$phpbb_dbprefix}users
ORDER BY user_id";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
echo "OK\n"; flush();
//insert ranks
echo "<br />Inserting User Ranks...\n"; flush();
$query = "TRUNCATE TABLE {$phpbb_dbprefix}ranks";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
$query = "INSERT INTO {$phpbb_dbprefix}ranks (rank_id, rank_title, rank_min, rank_special, rank_image) SELECT rank_id, rank_title, rank_min, rank_special, rank_image
FROM {$mosConfig_dbprefix}fb_ranks
ORDER BY rank_id";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
echo "OK\n"; flush();
echo "<br />Complete...\n"; flush();
echo "</body>";
echo "</html>"; flush();
//--- Function to prepare strings for MySQL storage ---/
function prep($s) {
// Insert bbcode_uid necessary for bbcode to be recognized by phpbb2
global $bbcode_uid;
// <strong> </strong>
$s = preg_replace('/\[b\]/', '[b:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[\/b\]/', '[/b:'.$bbcode_uid.']', $s);
// <em> </em>
$s = preg_replace('/\[i\]/', '[i:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[\/i\]/', '[/i:'.$bbcode_uid.']', $s);
// <u> </u>
$s = preg_replace('/\[u\]/', '[u:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[\/u\]/', '[/u:'.$bbcode_uid.']', $s);
// quote
$s = preg_replace('/\[quote\]/', '[quote:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[\/quote\]/', '[/quote:'.$bbcode_uid.']', $s);
// url with "
$s = preg_replace('/\[url=\\\"\;(.*?)\\\"\;\](.*?)/', '[url=\\1]\\2', $s);
// image
$s = preg_replace('/\[img(.*?)\](.*?)\[\/img(.*?)\]/si', '[img:'.$bbcode_uid.']'.'\\2[/img:'.$bbcode_uid.']', $s);
// color
$s = preg_replace('/\[color=(.*?)\]/', '[color=\\1:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[\/color\]/', '[/color:'.$bbcode_uid.']', $s);
// size fb uses size 1 to 5, phpbb2 uses size 1 to 29; will use 1 = 7, 2=9, 3=12, 4=18, 5=24
$s = preg_replace('/\[size=1\]/', '[size=7:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[size=2\]/', '[size=9:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[size=3\]/', '[size=12:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[size=4\]/', '[size=18:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[size=5\]/', '[size=24:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[\/size\]/', '[/size:'.$bbcode_uid.']', $s);
// code
$s = preg_replace('/\[code(.*?)\]/','[code:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[\/code(.*?)\]/', '[/code:'.$bbcode_uid.']', $s);
// lists
$s = preg_replace('/\[ul\]/', '[list:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[\/ul\]/', '[/list:u:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[ol\]/', '[list=1:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[\/ol\]/', '[/list:o:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[li\]/', '[*:'.$bbcode_uid.']', $s);
$s = preg_replace('/\[\/li\]/', '', $s);
return $s;
}
// fixed the quotes.
function fixquotes($q) {
global $bbcode_uid;
$q = preg_replace('/\[b:'.$bbcode_uid.'\](.*?) wrote:\[\/b:'.$bbcode_uid.'\]\r\n\[quote:'.$bbcode_uid.'\]/', '[quote:'.$bbcode_uid.'="\\1"]', $q );
return $q;
}
// function taken from phpbb2 functions file Unique ID for this message..
function make_bbcode_uid()
{
$uid = md5(mt_rand());
$uid = substr($uid, 0, BBCODE_UID_LEN);
return $uid;
}
//last minute accounts to be added note phpbb2admin password is "admn" Besure to remove once you have tested the site.
$query = "INSERT INTO {$phpbb_dbprefix}users (user_id, user_active, username, user_password, user_session_time, user_session_page, user_lastvisit, user_regdate, user_level, user_posts, user_timezone, user_style, user_lang, user_dateformat, user_new_privmsg, user_unread_privmsg, user_last_privmsg, user_login_tries, user_last_login_try, user_emailtime, user_viewemail, user_attachsig, user_allowhtml, user_allowbbcode, user_allowsmile, user_allowavatar, user_allow_pm, user_allow_viewonline, user_notify, user_notify_pm, user_popup_pm, user_rank, user_avatar, user_avatar_type, user_email, user_icq, user_website, user_from, user_sig, user_sig_bbcode_uid, user_aim, user_yim, user_msnm, user_occ, user_interests, user_actkey, user_newpasswd) VALUES
(-1, 0, 'Anonymous', '', 0, 0, 0, 0, 0, 0, 0.00, NULL, '', '', 0, 0, 0, 0, 0, NULL, 0, 0, 1, 1, 1, 1, 0, 1, 0, 1, 0, NULL, '', 0, '', '', '', '', '', NULL, '', '', '', '', '', '', ''),
(2, 1, 'phpbb2admin', '21232f297a57a5a743894a0e4a801fc3', 1198282415, 0, 1198282415, 0, 1, 1, 0.00, 1, 'english', 'd M Y h:i a', 0, 0, 0, 0, 0, NULL, 1, 0, 0, 1, 1, 1, 1, 1, 0, 1, 1, 1, '', 0, '[email protected]', '', '', '', '', NULL, '', '', '', '', '', '', '');";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
$query = "INSERT INTO {$phpbb_dbprefix}user_group (group_id, user_id, user_pending) VALUES
(1, -1, 0),
(2, 2, 0);";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
?>
Code: Select all
// Part Two - Populate Forums
// phpbb2 considers category header messages as forums
// these are indicated in sb by setting fb_messages.id=sb_messages.thread
// the actual messages can be detected by seeing that fb_messages.parent != 0
echo "<br />Populating Forums...\n"; flush();
$query = "INSERT INTO {$phpbb_dbprefix}forums (forum_id, cat_id, forum_name, forum_desc, forum_status, forum_order, forum_posts, forum_topics, forum_last_post_id, prune_next, prune_enable, auth_view, auth_read, auth_post, auth_reply, auth_edit,auth_delete, auth_sticky, auth_announce, auth_vote, auth_pollcreate, auth_attachments)
SELECT id, parent, name, description, 0, ordering, c.posts, b.topics, d.last, NULL, 0, 0, 1, 1, 1, 1, 1, 3, 3, 1, 1, 0
FROM {$mosConfig_dbprefix}fb_categories
LEFT JOIN (SELECT catid, COUNT(*) AS topics FROM {$mosConfig_dbprefix}fb_messages WHERE id=thread GROUP BY catid) b
ON {$mosConfig_dbprefix}fb_categories.id=b.catid
LEFT JOIN (SELECT catid, COUNT(*) AS posts FROM {$mosConfig_dbprefix}fb_messages GROUP BY catid) c
ON {$mosConfig_dbprefix}fb_categories.id=c.catid
LEFT JOIN (SELECT catid, MAX(id) AS last FROM {$mosConfig_dbprefix}fb_messages GROUP BY catid) d
ON {$mosConfig_dbprefix}fb_categories.id=d.catid
WHERE parent != 0
GROUP BY id;";
$result = mysql_query($query) or die("<br />Invalid query:<br />$query<br />" . mysql_error());
Code: Select all
SELECT id, parent, name, description, 0, ordering, c.posts, b.topics, d.last, NULL, 0, 0, 1, 1, 1, 1, 1, 3, 3, 1, 1, 0
FROM jos_fb_categories
LEFT JOIN (SELECT catid, COUNT(*) AS topics
FROM jos_fb_messages WHERE id=thread GROUP BY catid) b ON jos_fb_categories.id=b.catid
LEFT JOIN (SELECT catid, COUNT(*) AS posts
FROM jos_fb_messages GROUP BY catid) c ON jos_fb_categories.id=c.catid
LEFT JOIN (SELECT catid, MAX(id) AS last FROM jos_fb_messages GROUP BY catid) d ON jos_fb_categories.id=d.catid WHERE parent != 0 GROUP BY id;
Do you have any ideas to solve this problem ?Connecting to Database... OK
Populating Categories...
Inserted 6 Rows... OK
Populating Forums...
Invalid query:
INSERT INTO phpbb_forums (forum_id, cat_id, forum_name, forum_desc, forum_status, forum_order, forum_posts, forum_topics, forum_last_post_id, prune_next, prune_enable, auth_view, auth_read, auth_post, auth_reply, auth_edit,auth_delete, auth_sticky, auth_announce, auth_vote, auth_pollcreate, auth_attachments) SELECT id, parent, name, description, 0, ordering, c.posts, b.topics, d.last, NULL, 0, 0, 1, 1, 1, 1, 1, 3, 3, 1, 1, 0 FROM psy3_fb_categories LEFT JOIN (SELECT catid, COUNT(*) AS topics FROM psy3_fb_messages WHERE id=thread GROUP BY catid) b ON psy3_fb_categories.id=b.catid LEFT JOIN (SELECT catid, COUNT(*) AS posts FROM psy3_fb_messages GROUP BY catid) c ON psy3_fb_categories.id=c.catid LEFT JOIN (SELECT catid, MAX(id) AS last FROM psy3_fb_messages GROUP BY catid) d ON psy3_fb_categories.id=d.catid WHERE parent != 0 GROUP BY id;
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 'SELECT catid, COUNT(*) AS topics FROM psy3_fb_messages WHERE id
Code: Select all
SELECT id, parent, name, description, 0, ordering, c.posts, b.topics, d.last, NULL, 0, 0, 1, 1, 1, 1, 1, 3, 3, 1, 1, 0
FROM jos_fb_categories
LEFT JOIN (SELECT catid, COUNT(*) AS topics
FROM jos_fb_messages WHERE id=thread GROUP BY catid) b ON jos_fb_categories.id=b.catid
LEFT JOIN (SELECT catid, COUNT(*) AS posts
FROM jos_fb_messages GROUP BY catid) c ON jos_fb_categories.id=c.catid
LEFT JOIN (SELECT catid, MAX(id) AS last FROM jos_fb_messages GROUP BY catid) d ON jos_fb_categories.id=d.catid WHERE parent != 0 GROUP BY id;