';
$sql2 = "DELETE FROM nuke_pnforum_posts
WHERE forum_id=4";
mysql_query($sql2,$connection)
or die ("A MySQL error occurred.\n
Query: " . $sql2 . "
\nError: (" . mysql_errno() . ") " . mysql_error());
// -------------------------------------------------------------------
// EE uses different date formats than PN did. EE actually uses two
// different formats, unix timestamp (supposedly in GMT--see below)
// and numeric MySQL timestamp (in server time), the latter updated
// when a post is edited.
// This routine adds two fields to hold the EE time formats to the
// PN posts table to simplify pulling it over later. One anomoly that
// I don't understand requires me to do what appears to be a second
// adjustment for GMT (unix_timestamp is GMT already but I have to
// adjust it myself again...must be something I don't grasp here).
// Change the interval times until you get the correct results.
// -------------------------------------------------------------------
echo 'Creating date fields in required formats
';
$sql = "ALTER TABLE nuke_pnforum_posts ADD COLUMN
(new_date INT(10) NOT NULL DEFAULT 0,
new_edit_date TIMESTAMP)";
mysql_query($sql,$connection)
or die ("Couldn't execute query.");
$sql = "UPDATE nuke_pnforum_posts SET new_date=UNIX_TIMESTAMP(DATE_ADD(post_time, INTERVAL 7 HOUR))";
mysql_query($sql,$connection)
or die ("Couldn't execute query.");
$sql = "UPDATE nuke_pnforum_posts SET new_edit_date=(DATE_SUB(FROM_UNIXTIME(new_date) + 0, INTERVAL 7 HOUR))";
mysql_query($sql,$connection)
or die ("Couldn't execute query.");
// -------------------------------------------------------------------
// It kinda goes without saying that PN and EE use mostly different
// smiley codes. There are also a few little cleanup jobs that we
// must take care of to remove unneeded codes and fix the post quotes
// so that they will at least display reasonably well. This routine
// handles this for the most part, though it does leave a few minor
// display anomolies, but they are not critical and affect few posts.
// I figured we would do this cleanup work in one fell swoop on the
// posts_text table rather than piecemeal in the main conversion section.
// -------------------------------------------------------------------
$sql = "SELECT post_id, post_text
FROM nuke_pnforum_posts_text";
$sql_result = mysql_query($sql,$connection)
or die ("A MySQL error occurred.\n
Query: " . $sql . "
\nError: (" . mysql_errno() . ") " . mysql_error());
while ($row = mysql_fetch_array($sql_result))
{
$post_id = $row["post_id"];
$txt = $row["post_text"];
echo 'Cleaning up post ID: ' . $post_id . '
';
$txt = str_replace (':(', '>:(', $txt);
$txt = str_replace (':-(', '>:(', $txt);
$txt = str_replace (':-?', ':-S', $txt);
$txt = str_replace (':?:', ':question:', $txt);
$txt = str_replace (':?', ':-S', $txt);
$txt = str_replace (':-D', ':cheese:', $txt);
$txt = str_replace (':D', ':cheese:', $txt);
$txt = str_replace (':-O', ':bug:', $txt);
$txt = str_replace ('8-)', ':coolsmile:', $txt);
$txt = str_replace (':oops:', ':red:', $txt);
$txt = str_replace (':-X', '>:(', $txt);
$txt = str_replace (':evil:', ':snake:', $txt);
$txt = str_replace (':|', ':exclaim:', $txt);
$txt = str_replace (';)', ';-)', $txt);
$txt = str_replace (':wink:', ';-)', $txt);
$txt = str_replace (':cry:', ':down:', $txt);
$txt = str_replace ('[addsig]', '', $txt);
$txt = str_replace ('', '', $txt);
$txt = str_replace ('', '', $txt);
$txt = str_replace ('[quote=', '[quote author=', $txt);
$txt = str_replace ('[B]', '[b]', $txt);
$txt = str_replace ('[/B]', '[/b]', $txt);
$txt = str_replace ('[URL', '[url', $txt);
$txt = str_replace ('[/URL]', '[/url]', $txt);
$txt = str_replace ('[QUOTE]', '[quote]', $txt);
$txt = str_replace ('[/QUOTE]', '[/quote]', $txt);
$txt = addslashes($txt);
$sql2 = "UPDATE nuke_pnforum_posts_text
SET post_text='$txt'
WHERE post_id=$post_id";
mysql_query($sql2,$connection)
or die ("A MySQL error occurred.\n
Query: " . $sql2 . "
\nError: (" . mysql_errno() . ") " . mysql_error());
}
mysql_free_result($sql_result);
// -------------------------------------------------------------------
// There is a big difference between the organization of EE and PN
// forums. EE topic text is in the topics table while PN topics text
// is in the posts_text table along with responses. In addition, there
// are some topics records in my PN that do not have any text in the
// posts_text file. That means they really should be removed.
// The most reliable way to get at legitimate topics is to pull the
// earliest post ID for each topic ID listed in the PN posts table
// and work back to the topics table. We also need to link in the
// PN posts_text table of course to get all of the info we need.
//
// Once we have the info we then need to do a little massaging prior to
// inserting it into the exp_forum_topics table since they use some
// different flags. For a few values we will use defaults since they
// are of limited criticality and difficult to match up with anything.
// -------------------------------------------------------------------
// -------------------------------------------------------------------
// Here we build a temporary table and insert the post_id and
// forum_id for the first post of each topic.
// -------------------------------------------------------------------
echo 'Building temporary topics table
';
$sql = "CREATE TEMPORARY TABLE temp_topics (
topic_id INT(10) UNSIGNED NOT NULL,
post_id INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (topic_id),
KEY (post_id))";
mysql_query($sql,$connection)
or die ("A MySQL error occurred.\n
Query: " . $sql . "
\nError: (" . mysql_errno() . ") " . mysql_error());
$sql = "SELECT MIN(post_id), topic_id
FROM nuke_pnforum_posts
GROUP BY topic_id";
$sql_result = mysql_query($sql,$connection)
or die ("A MySQL error occurred.\n
Query: " . $sql . "
\nError: (" . mysql_errno() . ") " . mysql_error());
while ($row = mysql_fetch_array($sql_result))
{
$topic_id = $row["topic_id"];
$post_id = $row["MIN(post_id)"];
$sql2 = "INSERT INTO temp_topics (topic_id, post_id)
VALUES ('$topic_id', '$post_id')";
mysql_query($sql2,$connection)
or die ("A fatal MySQL error occurred.\n
Query: " . $sql2 . "
\nError: (" . mysql_errno() . ") " . mysql_error());
}
mysql_free_result($sql_result);
// -------------------------------------------------------------------
// Now we can build the EE topics table based on the temporary table
// we just created.
// -------------------------------------------------------------------
$sql = "SELECT temp.topic_id, posts.forum_id, posts.poster_id, posts.poster_ip, topics.topic_title, text.post_text,
topics.topic_status, topics.sticky, posts.new_date, posts.new_edit_date, topics.topic_replies, topics.topic_views
FROM temp_topics AS temp, nuke_pnforum_posts AS posts, nuke_pnforum_posts_text AS text, nuke_pnforum_topics AS topics
WHERE temp.post_id = posts.post_id AND temp.post_id = text.post_id AND temp.topic_id = topics.topic_id
ORDER BY topic_id";
$sql_result = mysql_query($sql,$connection)
or die ("A MySQL error occurred.\n
Query: " . $sql . "
\nError: (" . mysql_errno() . ") " . mysql_error());
while ($row = mysql_fetch_array($sql_result))
{
$topic_id = $row["topic_id"];
switch ($row["forum_id"])
{
case '1':
$forum_id = 2;
break;
case '2':
$forum_id = 6;
break;
case '3':
$forum_id = 7;
break;
case '5':
$forum_id = 10;
break;
case '6':
$forum_id = 9;
break;
default:
$forum_id = 0; // We do not want to convert any other forums
break;
}
$pentry_id = 0;
$moved_forum_id = 0;
$author_id = $row["poster_id"];
$ip_address = $row["poster_ip"];
$title = addslashes($row["topic_title"]);
$body = addslashes($row["post_text"]);
if ($row["topic_status"] == 0) {
$status = "o";
} else {
$status = "c";
}
if ($row["sticky"] == 0) {
$sticky = "n";
} else {
$sticky = "y";
}
$poll = "n"; // pnForum did not support polls
$announcement = "n"; // pnForum did not support announcements
$topic_date = $row["new_date"];
$topic_edit_date = $row["new_edit_date"];
$thread_total = ($row["topic_replies"] + 1);
$thread_views = $row["topic_views"];
$notify = "n";
$parse_smileys = "y";
$last_post_date = $topic_date; // We will replace this value later
$last_post_author_id = 1; // We will replace this value later
echo 'Inserting topic ID: ' . $topic_id . '
';
$sql2 = "INSERT INTO exp_forum_topics (topic_id, forum_id, pentry_id, moved_forum_id, author_id, ip_address, title, body,
status, sticky, poll, announcement, topic_date, topic_edit_date, thread_total, thread_views, last_post_date, last_post_author_id, notify, parse_smileys)
VALUES ('$topic_id', '$forum_id', '$pentry_id', '$moved_forum_id', '$author_id', '$ip_address', '$title', '$body', '$status',
'$sticky', '$poll', '$announcement', '$topic_date', '$topic_edit_date', '$thread_total', '$thread_views', '$last_post_date', '$last_post_author_id', '$notify', '$parse_smileys')";
mysql_query($sql2,$connection)
or die ("A fatal MySQL error occurred.\n
Query: " . $sql2 . "
\nError: (" . mysql_errno() . ") " . mysql_error());
}
mysql_free_result($sql_result);
// -------------------------------------------------------------------
// EE needs to know the last poster and last post date for each topic
// The following routine pulls this info from the PN tables
// -------------------------------------------------------------------
$sql = "SELECT ee_topics.topic_id, posts.new_date, posts.poster_id
FROM exp_forum_topics AS ee_topics, nuke_pnforum_topics AS pn_topics, nuke_pnforum_posts AS posts
WHERE ee_topics.topic_id = pn_topics.topic_id AND pn_topics.topic_last_post_id = posts.post_id";
$sql_result = mysql_query($sql,$connection)
or die ("Couldn't execute query.\n
Query: " . $sql . "
\nError: (" . mysql_errno() . ") " . mysql_error());
while ($row = mysql_fetch_array($sql_result))
{
$topic_id = $row["topic_id"];
$last_post_date = $row["new_date"];
$last_post_author_id = $row["poster_id"];
echo 'Updating last post info for topic ID: ' . $topic_id . '
';
$sql2 = "UPDATE exp_forum_topics
SET last_post_date=$last_post_date, last_post_author_id=$last_post_author_id
WHERE topic_id=$topic_id";
mysql_query($sql2,$connection)
or die ("A fatal MySQL error occurred.\n
Query: " . $sql2 . "
\nError: (" . mysql_errno() . ") " . mysql_error());
}
mysql_free_result($sql_result);
// -------------------------------------------------------------------
// Before populating the EE posts table, we need to remove the posts
// from the PN posts table that EE regards as topics. That means
// removing the earliest numbered post for each topic_id.
// -------------------------------------------------------------------
$sql = "SELECT topic_id, MIN(post_id)
FROM nuke_pnforum_posts
GROUP BY topic_id";
$sql_result = mysql_query($sql,$connection)
or die ("A MySQL error occurred.\n
Query: " . $sql . "
\nError: (" . mysql_errno() . ") " . mysql_error());
$num = mysql_num_rows ($sql_result);
echo 'Total number of posts to remove from old table: ' . $num . '
';
while ($row = mysql_fetch_array($sql_result))
{
$post_id = $row["MIN(post_id)"];
echo $post_id . ', ';
$sql2 = "DELETE FROM nuke_pnforum_posts
WHERE post_id=$post_id";
mysql_query($sql2,$connection)
or die ("A MySQL error occurred.\n
Query: " . $sql2 . "
\nError: (" . mysql_errno() . ") " . mysql_error());
}
mysql_free_result($sql_result);
// -------------------------------------------------------------------
// Now we can populate the EE posts table. This is a rather simple
// job now since the groundwork has been laid already.
// -------------------------------------------------------------------
$sql = "SELECT posts.post_id, posts.topic_id, posts.forum_id, posts.poster_id, posts.poster_ip, posts.new_date,
posts.new_edit_date, text.post_text
FROM nuke_pnforum_posts AS posts, nuke_pnforum_posts_text AS text
WHERE posts.post_id = text.post_id
ORDER BY post_id";
$sql_result = mysql_query($sql,$connection)
or die ("A MySQL error occurred.\n
Query: " . $sql . "
\nError: (" . mysql_errno() . ") " . mysql_error());
$num = mysql_num_rows ($sql_result);
echo 'Total number of posts to insert into posts table: ' . $num . '
';
while ($row = mysql_fetch_array($sql_result))
{
$post_id = $row["post_id"];
$topic_id = $row["topic_id"];
switch ($row["forum_id"])
{
case '1':
$forum_id = 2;
break;
case '2':
$forum_id = 6;
break;
case '3':
$forum_id = 7;
break;
case '5':
$forum_id = 10;
break;
case '6':
$forum_id = 9;
break;
default:
$forum_id = 0; // We do not want to convert any other forums
break;
}
$author_id = $row["poster_id"];
$ip_address = $row["poster_ip"];
$body = addslashes($row["post_text"]);
$post_date = $row["new_date"];
$post_edit_date = $row["new_edit_date"];
$notify = 'n';
$parse_smileys = 'y';
echo $post_id . ', ';
$sql2 = "INSERT INTO exp_forum_posts (post_id, topic_id, forum_id, author_id, ip_address, body,
post_date, post_edit_date, notify, parse_smileys)
VALUES ('$post_id', '$topic_id', '$forum_id', '$author_id', '$ip_address', '$body',
'$post_date', '$post_edit_date', '$notify', '$parse_smileys')";
mysql_query($sql2,$connection)
or die ("A fatal MySQL error occurred.\n
Query: " . $sql2 . "
\nError: (" . mysql_errno() . ") " . mysql_error());
}
mysql_free_result($sql_result);
// -------------------------------------------------------------------
// Close connection
// -------------------------------------------------------------------
mysql_close($connection);
// -------------------------------------------------------------------
// A final note: This will NOT work for anyone else without some
// adjustments. It might get you started though.
// -------------------------------------------------------------------
?>