Most popular smilie !

[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?!?

PHP Code:

// 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