• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

XF 1.4 Find all topics that contain string

Dakis

Well-known member
#1
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?
 

Brogan

XenForo moderator
Staff member
#3
It will be fine.

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

Dakis

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

Dakis

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

Code:
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?
 

Dakis

Well-known member
#7
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.
 

Dakis

Well-known member
#9
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 ;)
 

Mike

XenForo developer
Staff member
#10
This should work:
Code:
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%');