• 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?

Vincent

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

Brogan

XenForo moderator
Staff member
#2
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
 

Vincent

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

Brogan

XenForo moderator
Staff member
#4
Were there many non-queried threads?
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].
 
R

ragtek

Guest
#5
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
 

Kier

XenForo Developer
Staff member
#6
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].
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] '.
 

Vincent

Well-known member
#7
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.
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] '.
Awesome, this will certainly be useful for me and save me a lot of time :D