0

Is there a way to find the most popular tag in forum posts? For example, I have a tag called [card=x]cardname[/card]. I want to find all [card] tags sorted by the number of times x occurs. Also note that there can be multiple [card] tags in a single post.

LordZardeck
  • 7,953
  • 19
  • 62
  • 119

1 Answers1

0

I think this will help you a lot:

http://www.thingy-ma-jig.co.uk/comment/7200

Assuming that you know how you total the query from there. If not, it will be something like:

SET @findme="[card";
SELECT
  count((LENGTH(n.FIELD) - LENGTH(REPLACE(n.FIELD, @findme, ""))) / LENGTH(@findme)) AS findme_count
FROM table n
ORDER BY findme_count DESC;

You would have to loop or case that for each tag. I'm guessing REGEXP is overrated for this, since you probably only need to look for something like "[card" as opposed to "\\\[card .?\\\].?\\\[\\\/card\\\]"

EDIT: I'm sorry, I misread the post, it looks like you don't want to count tags but rather parameters, in which case you would need a capturing REGEXP.

Jason
  • 556
  • 1
  • 5
  • 21
  • i've seen that. while that would work for finding how many tags there are, I want to know how many of a specific card. More precisely, i wan the tags ordered by how many times the `x` in the tag occurs. – LordZardeck Mar 07 '12 at 06:34
  • Aye, I'm sorry I misread that. The above is still the right concept, just without the capturing REGEXP. :( – Jason Mar 07 '12 at 06:37