PHP to convert phpBB links to vB links?

[Marris]

I am working on converting my phpBB forum to vB. I am using the official Impex importer, but one feature it lacks is converting internal phpBB forum links within posts to point to the correct imported vB thread.

I have searched and posted on the official forum, but it's one of those often features that always seems to be "coming soon".

Anyway, I'm open to any suggestions as an effective method to covert the links with a PHP script. I'm also hoping I might hear from someone who has already done this and might be willing to share. There must be a few people out there who have successfully converted phpBB links to vb.


phpBB links are in the following format:

http://www.domain.com/forum/viewtopic.php?t=10820

http://www.domain.com/forum/viewtopic.php?p=72155#72155


the vBulletin equivalents:

http://www.domain.com/forum/showthread.php?t=13511

http://www.domain.com/forum/showthread.php?p=48246#post48246


phpBB table: phpbb_posts

phpBB fields: post_id, topic_id

phpBB table: phpbb_posts_text

phpBB fields: post_id, post_text


vBulletin table: vb3_post

vBulletin fields: postid, threadid, pagetext, importthreadid, importpostid



So, I guess I need to:

search each vb3_post : pagetext for occurances of

http://www.domain.com/forum/viewtopic.php?p=n#n

then return the new $replace=vb3_post : postid where importpostid=n

then write the replacement over the old link

http://www.domain.com/forum/showthread.php?p=$replace#post$replace


and also search each vb3_post : pagetext for occurances of

http://www.domain.com/forum/viewtopic.php?t=n

then return the new $replace=vb3_post : threadid where importthreadid=n

then write the replacement over the old link

http://www.domain.com/forum/showthread.php?t=$replace

then after all links are replaced, overwrite the vb3_post : pagetext entry in the database


Any pointers? I appreciate any help you can offer.

[Marco van Herwaarden]

This can be done, but it is not as easy as it seems.

[Michael Morris]

I'd suggest leaving the db alone and writing a new viewtopic.php file with these contents, turning it into a simple redirecting file.


<?php

if ($_GET['p'])
{
$p = "p=" . intval($_GET['p']);
}
else if ($_GET['t'])
{
$p = "t=" . intval($_GET['t']);
}

header("Location: http://www.yoursite.com/forum/showthread.php?" . $p);
?>

[Marco van Herwaarden]

That will not work since the thread/postid's have changed.

[Marco van Herwaarden]

On second thought, you could use the ol id, to query the ompirt_id column, then redirect to the new id.

[Marris]

Thanks for the suggestions. I have seen the alternate viewtopic.php idea mentioned on the official forums and given the "invalidates your support" official response. Of course, doing a search and replace on the posts probably invalidates support too.

I have had some luck throwing together a script to rewrite my old image gallery links in posts, so I'll try a few tests with something like this and see if I can get anywhere with it.

[Michael Morris]

That will not work since the thread/postid's have changed.

Wasn't aware of that - sorry.

[sabret00the]

Wasn't aware of that - sorry.

yeah if that wasn't the case you could just use the replace function, kinda sucks, kinda really sucks, i'm at a loss as to how to acheive it.

[Marco van Herwaarden]

Maybe something like (using michael's code, no coffee yet, so this is not bugfree, but you'll get the idea:
<?php

if ($_GET['p'])
{
$p = intval($_GET['p']);
$postid = $DB_site->query_first("SELECT postid FROM " . TABLE_PREFIX . "post
WHERE import_id = $p");
header("Location: http://www.yoursite.com/forum/showthread.php?" . p=$postid#$postid);
}
else if ($_GET['t'])
{
$p = "t=" . intval($_GET['t']);
}

?> And the same for threadid, but you'll figure that out.

[Marris]

Thanks again guys. :D Here's the code that is working on my test server:

<?php

$username="";
$password="";
$database="";

if ($_GET['p'])
{
$p = intval($_GET['p']);
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$result=mysql_query("SELECT postid FROM " . vb3_ . "post
WHERE importpostid = $p");
$postid = mysql_result($result,0,"postid");
header("Location: http://www.domain.com/testvb/showthread.php?p=" . $postid . "#post" . $postid);
}
else if ($_GET['t'])
{
$t = intval($_GET['t']);
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$result=mysql_query("SELECT threadid FROM " . vb3_ . "post
WHERE importthreadid = $t");
$threadid = mysql_result($result,0,"threadid");
header("Location: http://www.domain.com/testvb/showthread.php?t=" . $threadid);
}

?>

I'm not sure how secure this is, but I'll try to adjust it to standard vB formatting before putting it on the live server.

Thanks to you guys, I can keep my old links working. This should tide me over until I (or Jelsoft) come up with a solid script to replace the old links in the database.

[Marco van Herwaarden]

Jelsoft will never make it i think, because there are too many (not only phpBB) variations.

PS You should remove connecting to the database, and start with a require_once of global.php. That will setup your connection in a secure way. Also change the database functions to vB onces.

[Marris]

Well, I've got a rough, ROUGH version of a script to rewrite topic links in the database (no post links yet). This is cobbled together from my image link re-writing script which was equally shabby. Obviously, I don't really know what I am doing, but I pick up what I can from Google and work things out with lots of trial and error.

Anyway, this is basically working on my test server database.

It searches for any post with a link pointing to an old forum topic, then counts the number of links within a post. Then it splits the post into segments for each link and fills an array with replacement links, then puts in a placeholder for already replaced links. After processing all the segments, it fills in all the placeholders with replacements and writes the updated post to the database.

<?php
$username="";
$password="";
$database="";


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; // 100 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),$st ep);
$checknum = preg_match ('/[^0-9]/', $checkstring);
}
$endPos = ($begPos+strlen($searchstring)+$step-1);
echo "<br>ending position: $endPos</br>";
// $topicid = substr($pagetext,$begPos+strlen($searchstring),$en dPos-$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),$en dPos-$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";
?>

Here's some sample output:

POSTID:8761, NUMBER OF LINKS:3
ORIGINAL POST:
related threads: story title 1 (http://www.domain.com/forum/viewtopic.php?t=1954) another interesting link (http://www.domain.com/forum/viewtopic.php?t=1930) My last link for today (http://www.domain.com/forum/viewtopic.php?t=1834)

TEXT TO BE REPLACED: forum/viewtopic.php?t=1954
REPLACEMENT TEXT: testvb/showthread.php?t=1911

TEXT TO BE REPLACED: forum/viewtopic.php?t=1930
REPLACEMENT TEXT: testvb/showthread.php?t=1888

TEXT TO BE REPLACED: forum/viewtopic.php?t=1834
REPLACEMENT TEXT: testvb/showthread.php?t=1797

EDITED POST:
related threads: story title 1 (http://www.domain.com/testvb/showthread.php?t=1911) another interesting link (http://www.domain.com/testvb/showthread.php?t=1888) My last link for today (http://www.domain.com/testvb/showthread.php?t=1797)

If you can make any sense of this mess and have any constructive tips, please let me know.

I apologize for the state of the code. :o

[Marco van Herwaarden]

Tips in my previous post are still valid.

[Marris]

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
$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),$st ep);
$checknum = preg_match ('/[^0-9]/', $checkstring);
}
$endPos = ($begPos+strlen($searchstring)+$step-1);
//echo "<br>ending position: $endPos</br>";
// $topicid = substr($pagetext,$begPos+strlen($searchstring),$en dPos-$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),$en dPos-$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
$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),$st ep);
$checknum = preg_match ('/[^0-9]/', $checkstring);
}
$endPos = ($begPos+strlen($searchstring)+$step-1);
//echo "<br>ending position: $endPos</br>";
// $topicid = substr($pagetext,$begPos+strlen($searchstring),$en dPos-$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),$en dPos-$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";
?>

[Marco van Herwaarden]

Replace:
$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");
by:
require_once('./global.php');
replace:
$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);

by
$result = $DB_site->query("SELECT * FROM vb3_post WHERE pagetext LIKE ('%www.domain.com/forum/viewtopic.php?t=%')");

$num=$DB_site->num_rows($result);
etc...