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

No, don't tell me Brogan did it by hand?

Discussion in 'General XenForo Discussion and Feedback' started by Vincent, Sep 21, 2011.

  1. Vincent

    Vincent Well-Known Member

    Hey,

    I was just wondering what awesome script Mike or Kier wrote to turn all the [Prefixes] from the "Resolved Bug Reports" forums (etc.) into the cool new prefix feature :D

    If so, do we get access to that script when 1.1 is released? ;)

    It can't be that Brogan did it manually :p
     
    Peggy likes this.
  2. Brogan

    Brogan XenForo Moderator Staff Member

    Not likely :D

    Kier did it using "lots of reasonably complex queries".

    For example:
    Code:
    update xf_thread set prefix_id = 1,
    title = substring(title, 12)
    where node_id in(9,15,18)
    and title like '[duplicate] %'
    I did go through all 94 pages and apply any which weren't caught by the queries though o_O
     
  3. Vincent

    Vincent Well-Known Member

    I hope Kier writes a tutorial on that (or a script) :D

    Well, we only love you more :p
    Were there many non-queried threads?
     
  4. Brogan

    Brogan XenForo Moderator Staff Member

    Enough :D

    I've just about got rid of the cramp in my hand after 24 hours... ;)

    If you read the query, it's actually fairly self-explanatory.
    Set a prefix with id of 1 for any threads in nodes 9, 15 & 18, where the first part of the thread title (12 characters) is like [duplicate].
     
    Hoffi and Vincent like this.
  5. ragtek

    ragtek Guest

    and it also updates the title (removes the first 12 letters) (but isn't this bad, because of the search index?:D )
    once you've updates all the titles via query, you should IMO also rebuild the search index if you use other words in the prefix as in the "title with wannabe prefix" :D
     
    Vincent likes this.
  6. Kier

    Kier XenForo Developer Staff Member

    Yep, pretty much.

    To explain it in depth:
    Code:
    UPDATE xf_thread
    Update the xf_thread table,
    Code:
    SET prefix_id = 1,
    Set the prefix ID for matched threads to 1...
    Code:
    title = SUBSTRING(title, 12)
    and remove the first 12 characters of the title ("[duplicate] ").
    Code:
    WHERE node_id in(9,15,18)
    Apply to threads in the three 'bugs' forums...
    Code:
    AND title LIKE '[duplicate] %'
    ... where the title begins with '[duplicate] '.
     
    otto, dieketzer, CurveGotti and 4 others like this.
  7. Vincent

    Vincent Well-Known Member

    Awesome, this will certainly be useful for me and save me a lot of time :D
     
  8. SchmitzIT

    SchmitzIT Well-Known Member

    I don't have a Brogan to do this at my forums for those prefixes missed by the query.

    Kier, can I borrow yours? :D
     
    Liam23, Fuhrmann, Andrej and 3 others like this.

Share This Page