CODEWITHSUNDEEP
phpmysqlsmfsmf-forum
LordZardeck
LordZardeck

Reputation: 8283

find most popular tag

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.

Upvotes: 0

Views: 142

Answers (1)

Jason
Jason

Reputation: 556

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.

Upvotes: 0

Related Questions