Page 1 of 1

Find and Replace in database

Posted: Mon Jan 04, 2010 2:49 pm
by MetalRush
First: a great and healthy 2010 for all you, out there :D

Second: my question seems fairly easy to some of you. Please bare with me, since I am not an expert on MySQL or any other db for that matter...

Situation: I created a new website and started out with phpBB3 (out of the box).
After a few small mods and changes I decided to move the forum to a sub-domain (from http://www.BlaBla.com to http://forum.BlaBla.com). This is working great.

Problem: since the move, there are several links in users postings, where they linked back to another thread (ergo: http://www.BlaBla.com/Link...). I want to change this, with a SQL statement, to http://forum.BlaBla.com/Link...
I do not know which tables to touch and how to create a decent statement.
Is there anyone who can help me out, please? I did try via all kind of Google links, but I did not succeed (my tries ended all in an error (wrong statement)).

Please keep in mind that not only a link is directly in several postings, but also embedded in their text (used the code).

TIA!

Re: Find and Replace in database

Posted: Mon Jan 04, 2010 3:10 pm
by HGN
And maybe within [img] code.

First make a back-up of your database (or at least the table phpbb_posts).
Then run the queries:

Code: Select all

update phpbb_posts set post_text = replace(post_text, 'www.BlaBla.com/Link', 'forum.BlaBla.com/Link');
update phpbb_posts set post_text = replace(post_text, 'www.BlaBla.com/Link', 'forum.BlaBla.com/Link');
Prefix phpbb_ may be different in your database; replace 'BlaBla' and 'com/Link' to what is applicable for you.

Re: Find and Replace in database

Posted: Mon Jan 04, 2010 3:40 pm
by MetalRush
Hi Alfred,

Bedankt voor je snelle reactie :)
(translated: thanks for the quick reply).

If I would change your query to:

Code: Select all

    update phpbb_posts set post_text = replace(post_text, 'www.BlaBla.', 'forum.BlaBla.');
    update phpbb_posts set post_text = replace(post_text, 'www.BlaBla', 'forum.BlaBla');
...in order to change ALL links where www.BlaBla is changed to forum.BlaBla
So I left out the ".com/Link...'
Would that work?

Re: Find and Replace in database

Posted: Mon Jan 04, 2010 4:11 pm
by HGN
Yes, that should work. You should be absolutely sure you want all www.BlaBla changed in forum.BlaBla though.
Note that . actually is a dot (.), so the query could be

Code: Select all

update phpbb_posts set post_text = replace(post_text, 'www.BlaBla', 'forum.BlaBla');
update phpbb_posts set post_text = replace(post_text, 'www.BlaBla', 'forum.BlaBla');

Re: Find and Replace in database

Posted: Mon Jan 04, 2010 11:34 pm
by MetalRush
Ok, thanks I'll make sure the . is also out of the query.

Do I have to run both queries, by the way?

Re: Find and Replace in database

Posted: Mon Jan 04, 2010 11:40 pm
by HGN
Yes

Re: Find and Replace in database

Posted: Thu Jan 14, 2010 1:19 am
by MetalRush
Oh, I thought of this today... would be nice to let you know that it worked like a charm :D
Thanks for your assistance: much appreciated :P

Re: Find and Replace in database

Posted: Sun Jun 22, 2014 3:00 am
by Kiweed
Thanks so much for this post ..... worked like a charm for me tooooo

:)