Advertisement:

Author Topic: Search and replace script for database file  (Read 8874 times)

Offline davo88

  • Jr. Member
  • **
  • Posts: 140
Search and replace script for database file
« on: July 15, 2005, 07:34:37 PM »
I'm an absolute PHP noob - at the "hello world" stage of learning PHP. I'm about to convert a phpBB forum to SMF using [Unknown]'s converter. This conversion also involves a domain name change.

[Unknown]'s converter does a beautiful job of converting the boards, members, posts, avatars etc, but after conversion, the internal links are still in phpBB format and point to the old domain. There's many different types of these links and over 1000 to convert.

So I've been trying various text editors (using search and replace) on a text file dump of the converted database to replace each type of internal link. It works, but is extremely tedious. And everytime I want to do another trial run, I have to do it all again.

I'd like to write a small PHP script listing all the replacements so that it can be tested on trial installations and when everything is correct, I can convert the real phpBB board in the shortest possible time.

I have written a small PHP script using the str_replace function which replaces strings OK, but I'm using the file_get_contents to open the file which seems to be wrong as the database ends up as one long string.

Can anyone point me in the right direction with a small sample script that will open a text file dump of a database, search and replace a string, then write it back to the disk?

Then I could add all my string replacement data and hopefully have a repeatable solution.

many thanks, Dave

Offline [Unknown]

  • SMF Friend
  • SMF Master
  • *
  • Posts: 36,102
  • Gender: Male
Re: Search and replace script for database file
« Reply #1 on: July 16, 2005, 01:51:39 AM »
They all have about this format, right?

http://www.example.com/phpBB/viewtopic.php?t=12345

Can you give me the other formats they might use?

-[Unknown]

Offline davo88

  • Jr. Member
  • **
  • Posts: 140
Re: Search and replace script for database file
« Reply #2 on: July 16, 2005, 04:16:06 AM »
Here's a list of the links I'm attempting to convert. Some are standard phpBB links, others are custom to my forum.


INTERNAL MENU LINKS

Index - http://www.example.com/phpbb/index.php
FAQ - http://www.example.com/phpbb/faq.php
Search - http://www.example.com/phpbb/search.php
Members - http://www.example.com/phpbb/memberlist.php
Groups - http://www.example.com/phpbb/groupcp.php
Profile - http://www.example.com/phpbb/profile.php?mode=editprofile
PM - http://www.example.com/phpbb/privmsg.php?folder=inbox
Register - http://www.example.com/phpbb/profile.php?mode=register
Log in - http://www.example.com/phpbb/login.php

plus I have a couple of custom menu links

User Guide - http://www.example.com/user_guide/index.htm 
QuickStart Guide - http://www.example.com/user_guide/01_quickstart/quickstart_guide.htm

 

INTERNAL FORUM LINKS

http://www.example.com/phpbb/viewforum.php?f=1

I have 1-37 forums.


INTERNAL TOPIC LINKS

http://www.example.com/phpbb/viewtopic.php?t=1234


INTERNAL POST LINKS

http://www.example.com/phpbb/viewtopic.php?p=1234#1234


INTERNAL FILE LINKS (custom)

These are links to directories storing images, html, and pdf files

http://www.example.com/phpbb-pics/user/2005/filename.jpg

http://www.example.com/phpbb-html/subdir/2005/filename.htm

http://www.example.com/phpbb-pdf/subdir/2005/filename.pdf



LINK TO TEST FORUM (custom)

http://www.example.com/phpbb-test/index.php


A few other phpBB links that may be used in other forums - although I haven't found any in my forum.

PRIVATE MESSAGING

http://www.example.com/phpbb/privmsg.php?folder=inbox
http://www.example.com/phpbb/privmsg.php?folder=sentbox
http://www.example.com/phpbb/privmsg.php?folder=outbox
http://www.example.com/phpbb/privmsg.php?folder=savebox
http://www.example.com/phpbb/privmsg.php?mode=post

POST NEW TOPIC (to forum 1)

http://www.example.com/phpbb/posting.php?mode=newtopic&f=1

POST REPLY

http://www.example.com/phpbb/posting.php?mode=reply&t=1234

As mentioned above, the domain name is changing on the new installation too - so example.com will be replaced by new_example.com.
« Last Edit: July 16, 2005, 04:20:54 AM by davo88 »

Offline [Unknown]

  • SMF Friend
  • SMF Master
  • *
  • Posts: 36,102
  • Gender: Male
Re: Search and replace script for database file
« Reply #3 on: July 21, 2005, 06:49:39 AM »
Something like this?  Doesn't do all of them...

Code: [Select]
<?php

require_once(&#39;SSI.php&#39;);

$_REQUEST[&#39;start&#39;] = (int) @$_REQUEST[&#39;start&#39;];

$from_url = &#39;http://www.example.com/phpbb&#39;;

$replace = array(
&#39;~&#39; . preg_quote($from_url . &#39;/index.php&#39;, &#39;~&#39;) . &#39;~&#39; => $scripturl,
&#39;~&#39; . preg_quote($from_url . &#39;/faq.php&#39;, &#39;~&#39;) . &#39;~&#39; => $scripturl . &#39;?action=help&#39;,
&#39;~&#39; . preg_quote($from_url . &#39;/search.php&#39;, &#39;~&#39;) . &#39;~&#39; => $scripturl . &#39;?action=search&#39;,
&#39;~&#39; . preg_quote($from_url . &#39;/memberlist.php&#39;, &#39;~&#39;) . &#39;~&#39; => $scripturl . &#39;?action=mlist&#39;,
&#39;~&#39; . preg_quote($from_url . &#39;/profile.php?mode=editprofile&#39;, &#39;~&#39;) . &#39;~&#39; => $scripturl . &#39;?action=profile&#39;,
&#39;~&#39; . preg_quote($from_url . &#39;/privmsg.php?folder=inbox&#39;, &#39;~&#39;) . &#39;~&#39; => $scripturl . &#39;?action=pm&#39;,
&#39;~&#39; . preg_quote($from_url . &#39;/privmsg.php?folder=outbox&#39;, &#39;~&#39;) . &#39;~&#39; => $scripturl . &#39;?action=pm;f=outbox&#39;,
&#39;~&#39; . preg_quote($from_url . &#39;/privmsg.php?folder=sentbox&#39;, &#39;~&#39;) . &#39;~&#39; => $scripturl . &#39;?action=pm;f=outbox&#39;,
&#39;~&#39; . preg_quote($from_url . &#39;/privmsg.php?folder=savebox&#39;, &#39;~&#39;) . &#39;~&#39; => $scripturl . &#39;?action=pm&#39;,
&#39;~&#39; . preg_quote($from_url . &#39;/privmsg.php?mode=post&#39;, &#39;~&#39;) . &#39;~&#39; => $scripturl . &#39;?action=pm;sa=send&#39;,
&#39;~&#39; . preg_quote($from_url . &#39;/profile.php?mode=register&#39;, &#39;~&#39;) . &#39;~&#39; => $scripturl . &#39;?action=register&#39;,
&#39;~&#39; . preg_quote($from_url . &#39;/login.php&#39;, &#39;~&#39;) . &#39;~&#39; => $scripturl . &#39;?action=login&#39;,
&#39;~&#39; . preg_quote($from_url . &#39;/viewforum.php?f=&#39;, &#39;~&#39;) . &#39;([\d]+)&amp;st=([\d]+)~&#39; => $scripturl . &#39;?board=$1.$2&#39;,
&#39;~&#39; . preg_quote($from_url . &#39;/viewforum.php?f=&#39;, &#39;~&#39;) . &#39;([\d]+)~&#39; => $scripturl . &#39;?board=$1.0&#39;,
&#39;~&#39; . preg_quote($from_url . &#39;/viewtopic.php?t=&#39;, &#39;~&#39;) . &#39;([\d]+)~&#39; => $scripturl . &#39;?topic=$1.0&#39;,
&#39;~&#39; . preg_quote($from_url . &#39;/viewtopic.php?t=&#39;, &#39;~&#39;) . &#39;([\d]+)&amp;st=([\d]+)~&#39; => $scripturl . &#39;?topic=$1.$2&#39;,
&#39;~&#39; . preg_quote($from_url . &#39;/posting.php?mode=newtopic&amp;f=&#39;, &#39;~&#39;) . &#39;([\d]+)~&#39; => $scripturl . &#39;?action=post;board=$1.0&#39;,
&#39;~&#39; . preg_quote($from_url . &#39;/posting.php?mode=reply&amp;t=&#39;, &#39;~&#39;) . &#39;([\d]+)~&#39; => $scripturl . &#39;?action=post;topic=$1.0&#39;,
);

/*
http://www.example.com/phpbb/viewtopic.php?p=1234#1234
http://www.example.com/user_guide/index.htm
http://www.example.com/user_guide/01_quickstart/quickstart_guide.htm
http://www.example.com/phpbb-pics/user/2005/filename.jpg
http://www.example.com/phpbb-html/subdir/2005/filename.htm
http://www.example.com/phpbb-pdf/subdir/2005/filename.pdf
http://www.example.com/phpbb-test/index.php
*/

$from array_keys($replace);
$to array_values($replace);

while (
true)
{
$request db_query("
SELECT ID_MSG, body
FROM 
{$db_prefix}messages
LIMIT 
$_REQUEST[start], 20"__FILE____LINE__);

if (mysql_num_rows($request) == 0)
break;

while ($row mysql_fetch_assoc($request))
db_query("
UPDATE 
{$db_prefix}messages
SET body = &#39;" 
addslashes(preg_replace($from$to$row[&#39;body&#39;])) . "&#39;
WHERE ID_MSG $row[ID_MSG]
LIMIT 1", __FILE__, __LINE__);
mysql_free_result(
$request);

$_REQUEST[&#39;start&#39;] += 20;
}

?>

-[Unknown]
« Last Edit: July 21, 2005, 06:51:14 AM by [Unknown] »

Offline davo88

  • Jr. Member
  • **
  • Posts: 140
Re: Search and replace script for database file
« Reply #4 on: July 21, 2005, 07:20:56 AM »
Wow! ... thanks [Unknown]. Wasn't expecting anything as sophisticated as this! It's a long way from "hello world"  ;D, but I can (just) recognise enough to give it a fly. Will let you know how it goes.

Davo

Offline davo88

  • Jr. Member
  • **
  • Posts: 140
Re: Search and replace script for database file
« Reply #5 on: July 22, 2005, 08:44:56 AM »
Verrry slick, it didn't say a word - just quietly and quickly did the job.

Here's what it converted - (not converted in orange)...

INTERNAL MENU LINKS

Index - http://www.new_example.com/smf/index.php
FAQ - http://www.new_example.com/smf/index.php?action=help
Search - http://www.new_example.com/smf/index.php?action=search
Members - http://www.new_example.com/smf/index.php?action=mlist
Groups - http://www.example.com/phpbb/groupcp.php
Profile - http://www.new_example.com/smf/index.php?action=profile
PM - http://www.new_example.com/smf/index.php?action=pm
Register - http://www.new_example.com/smf/index.php?action=register
Log in - http://www.new_example.com/smf/index.php?action=login

INTERNAL FORUM LINKS

http://www.new_example.com/smf/index.php?board=1.0

INTERNAL TOPIC LINKS

http://www.new_example.com/smf/index.php?topic=123.0

INTERNAL POST LINKS

http://www.example.com/phpbb/viewtopic.php?p=123#123

PRIVATE MESSAGING

http://www.new_example.com/smf/index.php?action=pm
http://www.new_example.com/smf/index.php?action=pm;f=outbox
http://www.new_example.com/smf/index.php?action=pm;f=outbox
http://www.new_example.com/smf/index.php?action=pm
http://www.new_example.com/smf/index.php?action=pm;sa=send

POST NEW TOPIC (to forum 1)

http://www.new_example.com/smf/index.php?action=post;board=1.0

POST REPLY

http://www.new_example.com/smf/index.php?action=post;topic=123.0


The Groups link wasn't in the list so that's understandable. But the internal post link - http://www.example.com/phpbb/viewtopic.php?p=123#123 - looks like it should have been converted?

Offline [Unknown]

  • SMF Friend
  • SMF Master
  • *
  • Posts: 36,102
  • Gender: Male
Re: Search and replace script for database file
« Reply #6 on: July 22, 2005, 03:57:24 PM »
Internal post links are much more difficult.  SMF doesn't support just linking to a post - you have to link to a topic, and from there to a post.

That is, what was in phpBB:
http://www.example.com/phpbb/viewtopic.php?p=123#123

Might be in SMF:
http://www.example.com/phpbb/index.php?t=45.msg123#msg123

-[Unknown]

Offline davo88

  • Jr. Member
  • **
  • Posts: 140
Re: Search and replace script for database file
« Reply #7 on: July 22, 2005, 05:02:19 PM »
Oh, I didn't realize that. Just tried an internal post link in both phpBB and SMF and I see what you mean. I guess we add that one to the "manual edit" category.

Thank you very much for your work on this [Unknown], it's a far better solution than I could ever have achieved hacking away on my own.

Other phpBB users may like to use this script, but may not find it here. Should I write up a summary on how to use it and post it somewhere? Or will you add it into the phpBB converter?

Offline jockel

  • Semi-Newbie
  • *
  • Posts: 11
Re: Search and replace script for database file
« Reply #8 on: July 23, 2005, 08:03:37 AM »
hi davo88, [Unkown]

this thread/script made my day :-)

Thank you !

Jo

Offline [Unknown]

  • SMF Friend
  • SMF Master
  • *
  • Posts: 36,102
  • Gender: Male
Re: Search and replace script for database file
« Reply #9 on: July 25, 2005, 10:58:44 PM »
Other phpBB users may like to use this script, but may not find it here. Should I write up a summary on how to use it and post it somewhere? Or will you add it into the phpBB converter?

Consider it yours - do with it as you like ;).

I'm afraid I'd personally prefer to keep the converter as light as possible - this can always be done afterward, but could take several hours (or at least one) on larger forums.

-[Unknown]

Offline btechnix

  • Newbie
  • *
  • Posts: 7
Re: Search and replace script for database file (internal links)
« Reply #10 on: March 22, 2007, 04:41:32 AM »
Hello,
I am very interested by your script,

I have just converted a phpBB2 to  SMF with the convert script but all internal script aren't converted

When i use your script, i obtain this error from server:
Quote
500 Internal Server Error

Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request.

Apache/ProXad [Dec 3 2006 11:06:17] Server at myserver.free.fr Port 80

Have you some idea to solve this ?

Best regards

Bruno