1. 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

Discussion in 'XenForo Questions and Support' started by Dakis, Feb 17, 2015.

  1. Dakis

    Dakis 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?
  2. Dakis

    Dakis Well-Known Member

    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?
  3. Brogan

    Brogan 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');
    Dakis likes this.
  4. Dakis

    Dakis 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.
  5. Dakis

    Dakis 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?
  6. Brogan

    Brogan XenForo Moderator Staff Member

    Why do you need the node id?

    What data are you actually trying to retrieve and why?
  7. Dakis

    Dakis 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.
  8. Brogan

    Brogan XenForo Moderator Staff Member

  9. Dakis

    Dakis 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 ;)
  10. Mike

    Mike 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%');
    Dakis likes this.
  11. Dakis

    Dakis Well-Known Member

    Thanks heaps Mike!

Share This Page