XF 1.4 Find all topics that contain string

[Dakis]

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?

 

[Dakis]

Hm responding to myself here, what if I just do this?

SELECT thread_id FROM xf_post WHERE message LIKE 'http://mysite.com/photopost'

Is that going to cause a ridiculous load to the mysql?

 

[Paul B]

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');

 

[Dakis]

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]

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?

 

[Paul B]

Why do you need the node id?

What data are you actually trying to retrieve and why?

 

[Dakis]

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.

 

[Paul B]

You don't need the node ID though as it is unrelated to the thread.

You can load any thread using just the thread ID: https://xenforo.com/community/threads/93076

 

[Dakis]

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]

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%');

 

[Dakis]

Thanks heaps Mike!