[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),$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";
?>

Here's some sample output:

POSTID:8761, NUMBER OF LINKS:3
ORIGINAL POST:
related threads: story title 1 another interesting link My last link for today

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 another interesting link My last link for today

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.