Search options (Page 9 of 16)

<?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);
    
    }

?>