Most popular smilies

[ogetbilo]

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 |
+------------+-------+
| | 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.

[assassingod]

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

[Dean C]

Gosh that query would kill a large board Thanks for sharing the tip nevertheless!

[ogetbilo]

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

[Dean C]

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

[Xenon]

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.

[Boofo]

Originally Posted by Xenon

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?

[Xenon]

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

[Boofo]

Originally Posted by Xenon

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.

[Xenon]

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