Most popular smilies
 

To get the number of occurences of each smilie in user's posts on all over the forum, run the sql query:


[SQL]
select smilietext , count(*) as count
from post, smilie
where pagetext like concat("%",smilie.smilietext,"%")
group by smilietext
order by count desc;[/sql]

You will get an output like:

+------------+-------+
| smilietext | count |
+------------+-------+
| :D | 14063 |
| :) | 4596 |
| :lol: | 2381 |
| :( | 1526 |
| ;) | 1518 |
...


This is useful to determine the popular smilies and to put them in the smiliebox in the message posting interface.

Great idea, maybe you could implent it into the vB3 Admin CP and release it as a hack?:)

Gosh that query would kill a large board ;) Thanks for sharing the tip nevertheless! :D

You're right Dean C. It took 5.57 in my board (with ~35000 posts) to execute the query.
It will take roughly 1 minute for vbulletin.org, and more for larger boards, which may exceed their php timeout.

I will try to write the hack for the admincp when I have some time (maybe this weekend?)

Go for it :)! I look forward to seeing your first release ;)

actually, that query isn't correct.

for example if you have used :) 3 times in a post, it will just be counted one time, because you er doing the count on ammounts of posts, not ammounts of smilies used in a particular post.

What query would you use then?

hmm, that's a bit more complicate and i fear even much more intese.
I didn't find a way to do substring counts in mysql, maybe i'm just blind or there isn't such a function.

so the only way i can see now is to query the pagetext of each post, and to the php string count on it, but that's not fast of course ;)

Fast or not, I would still like to see the query. :p

as i said, i don't find a way to do it in a query.

i don't think there is the needed function in mysql, or if it is, then i can't figure out the nae, so the only way i see right now to get the correct value would be a php/mysql combination