Update links in posts after server URL change

[Dan Epps]

After changing the server URL, links to threads in old posts are no longer active as the reference the old server URL. Is there a utility to update all links to the new URL? The database is on SQL Server.

[stevemaury]

Can you give me a sample link for each of the old and new domain names?

[Dan Epps]

Sure. They are located in the post_text column of the phpbb3_posts table. This is a text column.

The links are like any URL that is posted in a thread, but as an example, here is a link to this thread:

http://www.phpbb.com/community/posting.php?mode=reply&f=46&t=1335675

Lets say that instead of http://www.phpbb.com, the server was changed to forum.phpbb.com. Finding and changing the first occurrence in each row is easy using UPDATETEXT. The big problem comes when a row contains multiple occurences of the text being searched for.

For example, the link above to this thread occurs twice in the database row--once for the link and once for the text being displayed. Some posts may have several such links in a single data row.

I can run my update script repeatedly to get the next occurence of the string, but I have no idea of how many times to run it. With arount 50,000 total posts, this could get to be very time comsuming.

[Dan Epps]

Anyone?

[stevemaury]

Well, you could do it with this query. But if there are links that use [url] tags, you would have to reparse the BBCode - see this - http://www.phpbb.com/community/viewtopi ... 45&start=0

This uses the urls from your example above:

Code: Select all

UPDATE phpbb_posts SET post_text = replace(post_text, 'www&#46phpbb&#46com', 'forum&#46phpbb&#46com');

If your table prefix is not phpbb_, change accordingly. I also strongly suggest you disable the board and backup the posts_text table before running this query.