Search and replace script for database file

Started by davo88, July 15, 2005, 07:34:37 PM

Previous topic - Next topic

davo88

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

[Unknown]

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]

davo88

#2
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.

[Unknown]

#3
Something like this?  Doesn't do all of them...

<?php

require_once('SSI.php');

$_REQUEST['start'] = (int) @$_REQUEST['start'];

$from_url 'http://www.example.com/phpbb';

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

/*
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 = '" 
addslashes(preg_replace($from$to$row['body'])) . "'
WHERE ID_MSG = 
$row[ID_MSG]
LIMIT 1"
__FILE____LINE__);
mysql_free_result($request);

$_REQUEST['start'] += 20;
}

?>


-[Unknown]

davo88

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

davo88

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?

[Unknown]

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]

davo88

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?

jockel

hi davo88, [Unkown]

this thread/script made my day :-)

Thank you !

Jo

[Unknown]

Quote from: davo88 on July 22, 2005, 05:02:19 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]

btechnix

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

Advertisement: