I don't know how much further I'll go with these. They're still very sloppy, but the output is "good enough" for my needs (and considering my lack of skills). Anyway, I'm posting these in the hope that maybe this will inspire someone to write a proper conversion script someday.
I have made a second script copied from the first that fixes the the "p=" post links. The main difference is an additional check for '#' in the link.
Running on a local test server, I have used these to update my full database (several thousand links updated within a few hundred thousand posts).
imported post link converter (viewtopic.php?=p):
PHP Code:
<?php
$username="";
$password="";
$database="";
// post link convertor for imported phpBB posts in vBulletin
// use at your own risk
// post replace script converted from topic replace
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM vb3_post WHERE pagetext LIKE ('%www.domain.com/forum/viewtopic.php?p=%')";
$result=mysql_query($query) or die (mysql_error ());
$num=mysql_numrows($result);
$searchstring = "www.domain.com/forum/viewtopic.php?p=";
// echo "<b><center>Database Output</center></b><br><br>";
$nonexistcount=0;
$changedcount=0;
$i=0; // 10 should be $num below
while ($i < $num) {
$postid=mysql_result($result,$i,"postid");
$pagetext=mysql_result($result,$i,"pagetext");
$count=substr_count($pagetext,$searchstring);
$replacecount=0;
//echo "<b>ROW:$i, POSTID:$postid, NUMBER OF LINKS:$count<br>ORIGINAL POST:<BR>$pagetext<br>";
$pagetext = addslashes($pagetext);
//echo "<br>SLASHIFIED POST:<br>$pagetext<br>";
while ($count > 0) {
$begPos = strpos($pagetext,$searchstring);
// echo "<br>beginning position: $begPos</br>";
$step = 0;
$checknum = 0;
$dbsize = 10; // number of digits in total number of posts in database + 1
while (($step < $dbsize) && ($checknum !== 1))
{
$step=$step+1;
$checkstring = substr($pagetext,$begPos+strlen($searchstring),$step);
$checknum = preg_match ('/[^0-9]/', $checkstring);
}
$endPos = ($begPos+strlen($searchstring)+$step-1);
//echo "<br>ending position: $endPos</br>";
// $topicid = substr($pagetext,$begPos+strlen($searchstring),$endPos-$begPos-1);
// old below
$psum =($endPos-$begPos);
$psumneg=$psum;
if ($psum < 0){
$ic=0;
$psegs=explode("http:\/\/",$pagetext);
//echo "<br>number of segments:$psegs[$ic]<br>";
$begPos = strpos($psegs[$ic],$searchstring);
//echo "<br>beginning position segment $ic: $begPos</br>";
while (( $begPos < 1 ) and ($ic < 100))
{$ic++;
$begPos = strpos($psegs[$ic],$searchstring);
}
//echo "<br>segment $ic : $psegs[$ic]<br>$endPos : before<br>";
$step = 0;
$checknum = 0;
$dbsize = 10; // number of digits in total number of posts in database + 1
while (($step < $dbsize) && ($checknum !== 1))
{
$step=$step+1;
$checkstring = substr($psegs[$ic],$begPos+strlen($searchstring),$step);
$checknum = preg_match ('/[^0-9]/', $checkstring);
}
$endPos = ($begPos+strlen($searchstring)+$step-1);
//echo "$endPos : after<br>";
$psumneg=($endPos-$begPos);
$tmp = substr($psegs[$ic],$begPos+strlen($searchstring),$endPos-$begPos-strlen($searchstring));
//echo "<br>COUNT:$ic<br>POST ID:$postid<br><br>NEGSUM:$psumneg<br>";
//echo "<Br>START:$begPos<br>END:$endPos<br>new post ID:$tmp<br>";
} else {
$tmp = substr($pagetext,$begPos+strlen($searchstring),$endPos-$begPos-strlen($searchstring));
}
if (ereg("&", $tmp)) {
$tmpsplit=explode("&",$tmp);
$tmp=$tmpsplit[0];
$tmpsid=$tmpsplit[1];
} else {
$tmpsid='';
}
$oldpostid = $tmp;
$sql = "SELECT * FROM vb3_post WHERE importpostid = '$oldpostid'";
if( !($result2 = mysql_query($sql)) )
{
message_die(GENERAL_ERROR, 'Could not query post information', '', __LINE__, __FILE__, $sql);
}
$result2=mysql_query($sql);
@$newpost=mysql_result($result2,0,"postid");
if (empty($newpost))
{
$filename[$replacecount]="post_deleted";
$nonexistcount=$nonexistcount+1;
} else {
$filename[$replacecount] = $newpost;
}
$filename[$replacecount]=("testvb/showthread.php?p=" . $filename[$replacecount] . "#post" . $filename[$replacecount]);
if (!empty($tmpsid)) {
$oldpostid=( "$oldpostid" . "&" . "$tmpsid" );
//echo "$oldpostid<br>";
}
$replace[$replacecount]=("forum/viewtopic.php?p=" . $oldpostid . "#" . $oldpostid);
if (substr_count($pagetext,$replace[$replacecount]) == 0){
$replace[$replacecount]=("forum/viewtopic.php?p=" . $oldpostid);
}
$placeholder[$replacecount]=("place" . $replacecount);
$pagetext = str_replace($replace[$replacecount],$placeholder[$replacecount],$pagetext);
//echo "PASS:$replacecount<BR>Old post ID:$oldpostid<br>TEXT TO BE REPLACED: $replace[$replacecount]<br>";
//echo "REPLACEMENT TEXT: $filename[$replacecount]<br>";
$count=$count-1;
$replacecount=$replacecount+1;
$changedcount=$changedcount+1;
}
$pagetext = str_replace($placeholder,$filename,$pagetext);
mysql_query("UPDATE vb3_post SET pagetext='$pagetext' WHERE postid=$postid") or die (mysql_error ());
//echo "<b>EDITED POST:<br>$pagetext<hr><br>";
$i++;
}
mysql_close();
echo "...DONE<br>";
echo "$nonexistcount missing posts replaced with placeholder link<br>";
echo "$changedcount links updated<br>";
echo "$i rows modified";
?>
imported thread link converter (viewtopic.php?=t):
PHP Code:
<?php
$username="";
$password="";
$database="";
// thread link convertor for imported phpBB posts in vBulletin
// use at your own risk
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM vb3_post WHERE pagetext LIKE ('%www.domain.com/forum/viewtopic.php?t=%')";
$result=mysql_query($query) or die (mysql_error ());
$num=mysql_numrows($result);
$searchstring = "www.domain.com/forum/viewtopic.php?t=";
// echo "<b><center>Database Output</center></b><br><br>";
$nonexistcount=0;
$changedcount=0;
$i=0; // 10 should be $num below
while ($i < $num) {
$postid=mysql_result($result,$i,"postid");
$pagetext=mysql_result($result,$i,"pagetext");
$count=substr_count($pagetext,$searchstring);
$replacecount=0;
//echo "<b>ROW:$i, POSTID:$postid, NUMBER OF LINKS:$count<br>ORIGINAL POST:<BR>$pagetext<br>";
$pagetext = addslashes($pagetext);
//echo "<br>SLASHIFIED POST:<br>$pagetext<br>";
while ($count > 0) {
$begPos = strpos($pagetext,$searchstring);
//echo "<br>beginning position: $begPos</br>";
$step = 0;
$checknum = 0;
$dbsize = 10; // number of digits in total number of posts in database + 1
while (($step < $dbsize) && ($checknum !== 1))
{
$step=$step+1;
$checkstring = substr($pagetext,$begPos+strlen($searchstring),$step);
$checknum = preg_match ('/[^0-9]/', $checkstring);
}
$endPos = ($begPos+strlen($searchstring)+$step-1);
//echo "<br>ending position: $endPos</br>";
// $topicid = substr($pagetext,$begPos+strlen($searchstring),$endPos-$begPos-1);
// old below
$psum =($endPos-$begPos);
$psumneg=$psum;
if ($psum < 0){
$ic=0;
$psegs=explode("http:\/\/",$pagetext);
//echo "<br>number of segments:$psegs[$ic]<br>";
$begPos = strpos($psegs[$ic],$searchstring);
//echo "<br>beginning position segment $ic: $begPos</br>";
while (( $begPos < 1 ) and ($ic < 100))
{$ic++;
$begPos = strpos($psegs[$ic],$searchstring);
}
//echo "<br>segment $ic : $psegs[$ic]<br>$endPos : before<br>";
$step = 0;
$checknum = 0;
$dbsize = 10; // number of digits in total number of posts in database + 1
while (($step < $dbsize) && ($checknum !== 1))
{
$step=$step+1;
$checkstring = substr($psegs[$ic],$begPos+strlen($searchstring),$step);
$checknum = preg_match ('/[^0-9]/', $checkstring);
}
$endPos = ($begPos+strlen($searchstring)+$step-1);
//echo "$endPos : after<br>";
$psumneg=($endPos-$begPos);
$tmp = substr($psegs[$ic],$begPos+strlen($searchstring),$endPos-$begPos-strlen($searchstring));
//echo "<br>COUNT:$ic<br>POST ID:$postid<br><br>NEGSUM:$psumneg<br>";
//echo "<Br>START:$begPos<br>END:$endPos<br>thread ID:$tmp<br>";
} else {
$tmp = substr($pagetext,$begPos+strlen($searchstring),$endPos-$begPos-strlen($searchstring));
}
if (ereg("&", $tmp)) {
$tmpsplit=explode("&",$tmp);
$tmp=$tmpsplit[0];
$tmpsid=$tmpsplit[1];
} else {
$tmpsid='';
}
$oldthreadid = $tmp;
$sql = "SELECT * FROM vb3_post WHERE importthreadid = '$oldthreadid'";
if( !($result2 = mysql_query($sql)) )
{
message_die(GENERAL_ERROR, 'Could not query thread information', '', __LINE__, __FILE__, $sql);
}
$result2=mysql_query($sql);
@$newthread=mysql_result($result2,0,"threadid");
if (empty($newthread))
{
$filename[$replacecount]="thread_deleted";
$nonexistcount=$nonexistcount+1;
} else {
$filename[$replacecount] = $newthread;
}
$filename[$replacecount]=("testvb/showthread.php?t=" . $filename[$replacecount]);
if (!empty($tmpsid)) {
$oldthreadid=( "$oldthreadid" . "&" . "$tmpsid" );
//echo "$oldthreadid<br>";
}
$replace[$replacecount]=("forum/viewtopic.php?t=" . $oldthreadid);
$placeholder[$replacecount]=("place" . $replacecount);
$pagetext = str_replace($replace[$replacecount],$placeholder[$replacecount],$pagetext);
//echo "PASS:$replacecount<BR>Old thread ID:$oldthreadid<br>TEXT TO BE REPLACED: $replace[$replacecount]<br>";
//echo "REPLACEMENT TEXT: $filename[$replacecount]<br>";
$count=$count-1;
$replacecount=$replacecount+1;
$changedcount=$changedcount+1;
}
$pagetext = str_replace($placeholder,$filename,$pagetext);
mysql_query("UPDATE vb3_post SET pagetext='$pagetext' WHERE postid=$postid") or die (mysql_error ());
//echo "<b>EDITED POST:<br>$pagetext<hr><br>";
$i++;
}
mysql_close();
echo "...DONE<br>";
echo "$nonexistcount missing threads replaced with placeholder link<br>";
echo "$changedcount links updated<br>";
echo "$i rows modified";
?>
More Information