Most popular smilie !

[corsacrazy]

i would like to display the most used smilie on forumhome ! is this possible ? ? eg :

most popular smilie = : :cross-eyed: has been used 504 times in the forums !

thanks !

[Xenon]

well, yes it's possible, but it would be quite intensitve to get the value, as long as noone knows a good query i wouldn't do it

[corsacrazy]

so is there a possiblility that a coder wood b able 2 do it , or is this too intense ? ?

[Xenon]

i just know a tooooo intense way.
it's because you have to go through each posts pagetext and count how often every smilie is used in it.....

if you are on a hosted server your host would contact you very soon

[corsacrazy]

Originally posted by Xenon
if you are on a hosted server your host would contact you very soon

huh ? y ?

[Xenon]

they wouldn't be amused of the strongly increased serverload :)
especially on forumhome this could kill every server :)

[corsacrazy]

seriously :( ive gotta think more carfully of my requests lol so wood it b possis just to grab the most used smilie and not have it say "has been used XXX times " ?

[Xenon]

no, if you give out the number or not doesn'T help, the problem is to find out how often a smilie has been use so you can say it's the mostused one...

[corsacrazy]

right :( wood u know of any other hacks along these lines to add a bit of FUN stats to forumhome ?

[Logician]

I agree with Stefan. Easy way is too server intensive and your hosting company will kick you out at no time if you use such an algorithm.

However if any hackers take it, here is an algorithm which requires more hacking but is performance friendly: (Don't look at me, I don't have time, sorry!) :)

You can add a new field to smilie table named "usagecount" and populate this field with a script once. Then you can hack newreply.php, newthread.php, editpost.php and private.php and apply a hack which updates this field on the fly whenever a new post is sent by counting the smilies.

Then queries and smilies stats can easily be compiled without any server load.

[Xenon]

Yes, this would be the only way to keep it less server intensive, but it wouldn't work for older posts....

ok you can run the intensive algorithm first to get the ammount for all old posts.

[zajako]

I have an idea that would make this more do able.

make a sql entry everytime a smily is parsed, it would ad multiple more queries per post, but if optimized sql wise should be able to get it down pretty low. the porblem woul dthen be remodling the smilie system to fit this.

[Kars10]

I know about a Statistic (PHPBB) with that smiliething.
But there you have the Top10 Smilies. ;)

[corsacrazy]

top 10 smilies is also good coz then i could break it down just to display top one smilie but u say thats 4 phbb :(

[fury]

I was thinking of doing something similar to this, except I would only count the amount of posts that contain each smilie, not how many times the smilie appears.

[Kars10]

Here is the smilie-code from PhbBB.
Maybe we could re-write this for vb?!?

// DB queries for top Smileys:

$query = "SELECT * FROM smiles GROUP BY smile_url";

$all_smileys = mysql_query($query, $db);

$topten = array();
$total_smiles = 0;

while ($aktsmile = mysql_fetch_array($all_smileys)) {

$aktcount = 0;


$query2 = "SELECT post_text FROM posts_text
WHERE post_text LIKE '%$aktsmile[smile_url]%'";

$all_posts = mysql_query($query2, $db);
while ($aktpost = mysql_fetch_array($all_posts)) {

$aktcount = $aktcount + substr_count ($aktpost[post_text], $aktsmile[smile_url]);
}


$topten[$aktsmile[smile_url]] = $aktcount;
$total_smiles = $total_smiles + $aktcount;

}

[fury]

I made a query that does it. (counting how many posts contain each smilie)

select count(postid) as count,smilie.smilietext from post,smilie where post.pagetext like concat("%",smilie.smilietext,"%") group by smilie.smilietext order by count;

Warning, this query can take a few minutes and may cause your server to stop responding. Don't run it from phpMyAdmin.

It could possibly be run like once a week or so and the results put in its own table (but it would require being able to disable the exec time limit on a per-file basis) which could then be called in a fraction of a second each time the page is displayed

[Xenon]

@fury: sorry to say, but your query is wrong. it just count how many posts contain a special smilie, but not how ofter the smilie is in this post...

@Minifreunde: yes, this is the code i was thinking for.
On great boards this will be very intense, because it goes more than once through nearly the whole posttable
not a good idea if you have over a million posts ;)