Replace link for external images

[bikeridr]

Don't know if tis goes here or in another forum, but here goes..

Before I enabeled upload images to forum direct, my users (and myself) stored images/files elsewere and linked the images/files.
Since I enabeled "forum upload", most users uploads images direct to the forum (host storage space).
I have several thousands posts in my forum from the era before "forum upload" was enabeled.

I also own several domains, and have used one specific domain to host the "external" photos/files.
I plan to migrate all those photos/files to a new (owned) domain, cancelling the domain whrere my files are.
Is there an easy way to replace the links/urls for the photos/files in all my posts from:
"myOLDdomain/forumfiles/image.jpg" to "myNEWdomain/forumfiles/image.jpg"

That is, I want to mass replace the linking in all my posts/replys from "[ img ]myOLDdomain/forumfiles/image.jpg[ /img ]" to "[ img ]myNEWdomain.com/forumfiles/image.jpg[ /img ]"
All files will have the same name/structure, only the domain will change.

[MarkDHamill]

This can be done in the database but it's somewhat dangerous. I'd disabled the board and back up the phpbb_posts table first. Then I'd find an example post and study the content of the post_text column in a tool like phpMyAdmin. You can get the topic_id from the URL to the topic (ex: viewtopic.php?t=1).

The post_text column contains a special markup, neither HTML or BBCode exactly. See how it is encoded. Here's an example:

Code: Select all

<r><p><STRONG><s>**</s>Hello<e>**</e></STRONG> <E>:D</E> 

Next, you need to study the MySQL REPLACE function:

https://www.w3schools.com/mysql/func_mysql_replace.asp

Once you verify the code looks correct from a SELECT statement (select the post_text and your replacement against a number of posts), you would need to use the REPLACE function inside a SQL UPDATE statement. For example, to change THE to THEM, something like this should work:

Code: Select all

UPDATE phpbb_posts SET post_text = REPLACE(post_text,'THE','THEM') ;

[bikeridr]

Thank you for your answer.
I will try that on my mock forum and test this thoroughly before doing so to my real forum.

[warmweer]

Just recently there was a similar support question, and the solution offered (basically the answer MarkDHamill gave here), didn't work (yet it is correct).
Coincidentally I had a similar issue yesterday and encountered some problems in the sense that the same SQL MarkDHamill provided just now, didn't work for me this time (although it works on another system), and neither did the SQL I had posted (which also worked on another system). I still haven't found out what the reason is (tried on different systems) but I needed to move on so I used another solution I proposed:

Put the board in maintenance mode, download the posts_table, edit the posts_table (SQL format) with a good text editor capable of handling large files (in my case 3 GB). I needed to remove a part of a url in all posts containing a certain link and used Notepad+.
It needed about 30 seconds and it was done. Then uploaded the edited SQL to restore the posts_table.

All done in about 30 minutes. (taking a full backup, backup the posts_table, download, edit, upload, restore the edited post_table).

I will try that on my mock forum and test this thoroughly before doing so to my real forum.

Exactly!!


Edited: reformulated the first paragraph: I was probably half asleep when I wrote it originally

[MarkDHamill]

My SQL could be improved:

Code: Select all

UPDATE phpbb_posts SET post_text = REPLACE(post_text,'THE','THEM') WHERE post_text LIKE '%THE%;

To find these posts and see how they might change, something like:

Code: Select all

SELECT post_id, topic_id, post_text, REPLACE(post_text,'THE','THEM') WHERE post_text LIKE '%THE%;

[ssl]

or this

Code: Select all

UPDATE phpbb_posts SET post_text = REPLACE(post_text, 'myolddomain/forumfiles/image.jpg', 'mynewdomain/forumfiles/image.jpg') WHERE post_text LIKE '%myolddomain/forumfiles/image.jpg%';