XF 1.4 Find all topics that contain string


Well-known member
I'm trying to prepare my forum to migrate from old galleries (photopost + xengallery) to XFMG, and to make sure I can convert all existing links from within the forum, I'm trying to identify which topics have links to the old galleries.

I figured the best way to do this is to run a query in the database, that will return a list of all threads that contain specific strings in there, such as "mysite.com/photopost" OR "mysite.com/gallery".

Something like

SELECT thread_id FROM xf_thread WHERE how-on-earth-do-i-connect-this-to-the-posts-table LIKE 'http://mysite.com/photopost' OR 'http://mysite.com/gallery"

I need some help with connecting the posts table, as that's where the texts sit.

Or am I thinking about this the wrong way?


XenForo moderator
Staff member
It will be fine.

If you want to replace a specific string you can use:
UPDATE xf_post SET message = REPLACE(message,'current_content','new_content');


Well-known member
Thanks @Brogan - actually replacing won't work unfortunately here, I will have to manually change the embedded images to point to the new gallery.


Well-known member
Ok I've narrowed it down to this :

SELECT `thread_id` FROM `xf_post` WHERE `node_id` in (57,58,59,60,61,62,63,72) AND `message` LIKE '%http://www.travelstories.gr/photo/%';
except of course there is no node_id field in the post table - how do I connect it to the threads table so I can get the node_id condition check from there? Do I need to use the JOIN command?


Well-known member
I need to find all threads from specific forums, that contain posts with that URL string.

As to the why, so that I can locate the threads that have urls pointing to the "old" galleries and replace those urls with the "new" xfmg urls after the conversion.


Well-known member
I know that, however I am only targeting threads within specific forums. These are let's say my "main" content , which I need to protect from the changes ;)


XenForo developer
Staff member
This should work:
SELECT t.thread_id, t.title, t.node_id
FROM xf_post AS p
INNER JOIN xf_thread AS t ON (t.thread_id = p.thread_id)
WHERE t.node_id IN (1, 2, 3)
AND (p.message LIKE '%example 1%' OR p.message LIKE '%example 2%');