Find and Replace in database

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Suggested Hosts
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
Locked
MetalRush
Registered User
Posts: 17
Joined: Thu Jan 16, 2003 10:58 pm
Location: The Netherlands
Contact:

Find and Replace in database

Post by MetalRush » Mon Jan 04, 2010 2:49 pm

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!

User avatar
HGN
Former Team Member
Posts: 4706
Joined: Wed Dec 03, 2008 1:53 pm
Location: The Netherlands
Name: Alfred
Contact:

Re: Find and Replace in database

Post by HGN » Mon Jan 04, 2010 3:10 pm

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.

MetalRush
Registered User
Posts: 17
Joined: Thu Jan 16, 2003 10:58 pm
Location: The Netherlands
Contact:

Re: Find and Replace in database

Post by MetalRush » Mon Jan 04, 2010 3:40 pm

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?

User avatar
HGN
Former Team Member
Posts: 4706
Joined: Wed Dec 03, 2008 1:53 pm
Location: The Netherlands
Name: Alfred
Contact:

Re: Find and Replace in database

Post by HGN » Mon Jan 04, 2010 4:11 pm

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');

MetalRush
Registered User
Posts: 17
Joined: Thu Jan 16, 2003 10:58 pm
Location: The Netherlands
Contact:

Re: Find and Replace in database

Post by MetalRush » Mon Jan 04, 2010 11:34 pm

Ok, thanks I'll make sure the . is also out of the query.

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

User avatar
HGN
Former Team Member
Posts: 4706
Joined: Wed Dec 03, 2008 1:53 pm
Location: The Netherlands
Name: Alfred
Contact:

Re: Find and Replace in database

Post by HGN » Mon Jan 04, 2010 11:40 pm

Yes

MetalRush
Registered User
Posts: 17
Joined: Thu Jan 16, 2003 10:58 pm
Location: The Netherlands
Contact:

Re: Find and Replace in database

Post by MetalRush » Thu Jan 14, 2010 1:19 am

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

User avatar
Kiweed
Registered User
Posts: 29
Joined: Sun Jan 01, 2006 9:30 pm

Re: Find and Replace in database

Post by Kiweed » Sun Jun 22, 2014 3:00 am

Thanks so much for this post ..... worked like a charm for me tooooo

:)

Locked

Return to “[3.0.x] Support Forum”