Update links in posts after server URL change

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Anti-Spam Guide
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
Locked
Dan Epps
Registered User
Posts: 46
Joined: Fri Dec 14, 2007 3:36 pm

Update links in posts after server URL change

Post by Dan Epps » Wed Dec 03, 2008 8:40 pm

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.

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 50659
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: Update links in posts after server URL change

Post by stevemaury » Wed Dec 03, 2008 8:56 pm

Can you give me a sample link for each of the old and new domain names?
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

Dan Epps
Registered User
Posts: 46
Joined: Fri Dec 14, 2007 3:36 pm

Re: Update links in posts after server URL change

Post by Dan Epps » Wed Dec 03, 2008 10:17 pm

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.
Last edited by stevemaury on Fri Dec 05, 2008 3:47 pm, edited 1 time in total.
Reason: Make URLs not parse so discussion is clearer.

Dan Epps
Registered User
Posts: 46
Joined: Fri Dec 14, 2007 3:36 pm

Re: Update links in posts after server URL change

Post by Dan Epps » Fri Dec 05, 2008 3:24 pm

Anyone?

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 50659
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: Update links in posts after server URL change

Post by stevemaury » Fri Dec 05, 2008 3:57 pm

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.
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

Locked

Return to “[3.0.x] Support Forum”