Topic: phorum -> punbb converter!
The code might be a bit bogus, i wrote this in a hurry, the important thing: it worked!
I migrated a forum from phorum!
It worked like a charm, though is not a somplete clean migration ( that would be impossible ) .
Currently i have only a major issue:
something when wrong with the "img" bbcode tag... the viewtopic shows the bbcode source and url insead of the image... strange... can anybody figure out why? on the other hand, the "code" tags work perfectly...
Now, there is a few things to keep in mind:
-I didnt use punbb db layer because i wanted this done quickly, so this will migrate to SQLITE ONLY.
-For those using php 4 or older this won't probably work as you likely don't have suport for sqlite.
-The two first users of your phorum forum will be overwriten by the new admin and the guest acount respectively.
In my case that was no problem as i had registered two accounts from the beginning. A way to solve this is adding user offset.
-No groups are kept, permissions should be set in the new installations
-the display order has to be adjusted manually.
-single quotes will be replaced by double quotes because of sqlite_escape string
I wont make any changes in this code, in case somebody takes the effort...
TODO:
-Add user offset
-Switch from native mysql library to punbb dblayer
-minor code improvements
<?php
/**************************************************************\
* Phorum (mysql) -> Punbb 1.2 (sqlite)
*
* coded by pedrotuga
*
* This script migrates your phorum forum to punbb.
* The destination database is a sqlite database, you will
* no longer need a mysql database or server. The two first
* users in your phorum database will be replaced by the new
* admin and the guest acount respectively.
*
* For further information check punbb support forums at
* http://forums.punbb.org
*
* For further information about SQLite check
* http://sqlite.org
*
* Instructions
* 1.Install punbb 1.2, chose sqlite as your database system
* 2.Delete the test category/forum/post
* 3.Set the variables below
* 4.upload this script and run it
*
* Released under punbb compatible license
*
\***************************************************************/
//original mysql database settings=============================================
$username = "";
$password = "";
$hostname = "";
$database = "";
//sqlite new database file name================================================
$sqlite_filename = "";
//if you use a table preffix on your phorum installation define it here========
$prefix = "";
//set $debug_mode to true to output the queries================================
$debug_mode = false;
//*****************************************************************************
//****** Settings end *******
//*****************************************************************************
//database connections=========================================================
$dbh = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL server");
$selected = mysql_select_db($database)
or die("database error!!! Unable to use specified database");
$db = sqlite_open( $sqlite_filename , 0666, $sqliteerror);
//user migration===============================================================
$sql_get_users ="SELECT user_id, username,`password`,email,posts,date_added FROM ".$prefix."_users WHERE active=1 AND user_id > 2";
$result_users = mysql_query($sql_get_users);
$sql_insert_users = array();
while ($row = mysql_fetch_assoc($result_users)){
$escaped_username = sqlite_escape_string($row["username"]);
$escaped_email = sqlite_escape_string($row["email"]);
$sql_insert_users[]="INSERT INTO users
(id,username,password, email,num_posts,registered)
VALUES
(".$row["user_id"].",'".$escaped_username."','".$row["password"]."','".$escaped_email."',".$row["posts"].",".$row["date_added"].")";
}
foreach ($sql_insert_users as $iterador){
if ($debug_mode){
echo $iterador."<br />";
}
sqlite_query ( $db, $iterador );
}
//categories migration==========================================================
$sql_get_categories = "SELECT forum_id, name FROM ".$prefix."_forums WHERE folder_flag=1";
$result_categories = mysql_query($sql_get_categories);
$sql_insert_categories = array();
while ($row = mysql_fetch_assoc($result_categories)){
$escaped_catname = sqlite_escape_string($row["name"]);
$sql_insert_categories[]="INSERT INTO categories
(id,cat_name)
VALUES
(".$row[forum_id].",'".$escaped_catname."')";
}
foreach ($sql_insert_categories as $iterador){
if ($debug_mode){
echo $iterador."<br />";
}
sqlite_query ( $db, $iterador );
}
//forums migration==========================================================
$sql_get_forums = "SELECT forum_id, name, description, parent_id, message_count, thread_count FROM ".$prefix."_forums WHERE folder_flag=0";
$result_forums = mysql_query($sql_get_forums);
$sql_insert_forums = array();
while ($row = mysql_fetch_assoc($result_forums)){
$escaped_forumname = sqlite_escape_string($row["name"]);
$escaped_forumdescription = sqlite_escape_string($row["description"]);
$sql_insert_forums[]="INSERT INTO forums
(id,forum_name, forum_desc, cat_id, num_posts, num_topics)
VALUES
(".$row[forum_id].",'".$escaped_forumname."','".$escaped_forumdescription."',".$row["parent_id"].",".$row["message_count"].",".$row["thread_count"].")";
}
foreach ($sql_insert_forums as $iterador){
if ($debug_mode){
echo $iterador."<br />";
}
sqlite_query ( $db, $iterador );
}
//topics migration==========================================================
$sql_get_topics = "SELECT message_id,
forum_id,
author,
subject,
datestamp,
viewcount,
thread_count
FROM
".$prefix."_messages
WHERE
parent_id=0";
$result_topics = mysql_query($sql_get_topics);
$sql_insert_topics = array();
while ($row = mysql_fetch_assoc($result_topics)){
$escaped_topicsubject = sqlite_escape_string($row["subject"]);
$escaped_author = sqlite_escape_string($row["author"]);
$sql_insert_topics[]="INSERT INTO topics
(id,forum_id,poster,subject,posted,num_views,num_replies)
VALUES
(".$row[message_id].",".$row["forum_id"].",'".$escaped_author."','".$escaped_topicsubject."',".$row["datestamp"].",".$row["viewcount"].",".$row["thread_count"].")";
}
foreach ($sql_insert_topics as $iterador){
if ($debug_mode){
echo $iterador."<br />";
}
sqlite_query ( $db, $iterador );
}
//topposts migration==========================================================
$sql_get_topposts= "SELECT message_id,
author,
body,
user_id,
datestamp,
modifystamp
FROM
".$prefix."_messages
WHERE
parent_id=0";
$result_topposts = mysql_query($sql_get_topposts);
$sql_insert_topposts = array();
while ($row = mysql_fetch_assoc($result_topposts)){
$escaped_body = sqlite_escape_string($row["body"]);
$escaped_author = sqlite_escape_string($row["author"]);
$sql_insert_topposts[]="INSERT INTO posts
(id,topic_id,poster,message,poster_id,posted,edited,edited_by)
VALUES
(".$row[message_id].",".$row["message_id"].",'".$escaped_author."','".$escaped_body."',".$row["user_id"].",".$row["datestamp"].",".$row["modifystamp"].",'desconhecido')";
}
foreach ($sql_insert_topposts as $iterador){
if ($debug_mode){
echo $iterador."<br />";
}
sqlite_query ( $db, $iterador );
}
//non-topposts migration======================================================
$sql_get_posts= "SELECT message_id,
parent_id,
author,
body,
user_id,
datestamp,
modifystamp
FROM
".$prefix."_messages
WHERE
parent_id<>0";
echo $sql_get_posts;
$result_posts = mysql_query($sql_get_posts);
$sql_insert_posts = array();
while ($row = mysql_fetch_assoc($result_posts)){
$escaped_body = sqlite_escape_string($row["body"]);
$escaped_author = sqlite_escape_string($row["author"]);
$sql_insert_posts[]="INSERT INTO posts
(id,topic_id,poster,message,poster_id,posted,edited,edited_by)
VALUES
(".$row["message_id"].",".$row["parent_id"].",'".$escaped_author."','".$escaped_body."',".$row["user_id"].",".$row["datestamp"].",".$row["modifystamp"].",'desconhecido')";
}
foreach ($sql_insert_posts as $iterador){
if ($debug_mode){
echo $iterador."<br />";
}
sqlite_query ( $db, $iterador );
}
//update the last post of each topic==========================================
$sql_get_alltopics= "SELECT id from topics";
$result_alltopics = sqlite_query($db, $sql_get_alltopics);
while ($row = sqlite_fetch_array($result_alltopics)){
$sql_get_last_values = "SELECT id, poster, posted FROM posts WHERE topic_id=".$row["id"]." ORDER BY posted DESC LIMIT 1";
$result = sqlite_query($db, $sql_get_last_values);
$last_values = sqlite_fetch_array($result);
$escaped_author = sqlite_escape_string($last_values["poster"]);
$sql_update_topics = "UPDATE topics SET last_post=".$last_values["posted"].", last_post_id=".$last_values["id"].", last_poster='$escaped_author' WHERE id=".$row["id"];
sqlite_query($db, $sql_update_topics);
}
//update the last post of each forum==========================================
$sql_get_allforums= "SELECT id from forums";
$result_allforums = sqlite_query($db, $sql_get_allforums);
while ($row = sqlite_fetch_array($result_allforums)){
$sql_get_last_values = "SELECT p.id id, p.poster poster, p.posted posted FROM posts as p, topics as t WHERE p.topic_id=t.id AND t.forum_id=".$row["id"]." ORDER BY p.posted DESC LIMIT 1";
$result = sqlite_query($db, $sql_get_last_values);
$last_values = sqlite_fetch_array($result);
$escaped_author = sqlite_escape_string($last_values["poster"]);
$sql_update_forums = "UPDATE forums SET last_post=".$last_values["posted"].", last_post_id=".$last_values["id"].", last_poster='$escaped_author' WHERE id=".$row["id"];
sqlite_query($db, $sql_update_forums);
}
?>