vb.org Archive - Most popular smilies

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- Community Lounge (https://vborg.vbsupport.ru/forumdisplay.php?f=13)

- - Most popular smilies (https://vborg.vbsupport.ru/showthread.php?t=66721)

ogetbilo

06-30-2004 05:07 PM

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.

assassingod

06-30-2004 05:14 PM

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

Dean C

06-30-2004 05:36 PM

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

ogetbilo

06-30-2004 08:17 PM

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

07-01-2004 01:35 PM

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

Xenon

07-01-2004 01:43 PM

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

07-01-2004 03:11 PM

Quote:

Originally Posted by Xenon

What query would you use then?

Xenon

07-01-2004 04:36 PM

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

07-01-2004 04:37 PM

Quote:

Originally Posted by Xenon

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

Xenon

07-01-2004 04:40 PM

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

Boofo

07-01-2004 04:45 PM

Quote:

Originally Posted by Xenon

Ok, then, how would you do the combination?

Xenon

07-01-2004 05:00 PM

aww, comeone, someone else please ;)

ok, here you are, just snipets, not tested, nor implemented:

PHP Code:


		
			
$smilie_counts = array();

$posts = $DB_site->query("

  SELECT smilietext, pagetext 

  FROM post, smilie 

  WHERE pagetext like concat("%",smilie.smilietext,"%") 

");

while ($post = $DB_site->fetch_array($posts))

{

  $smilie_counts["$post[smilietext]"] += substr_count($post['pagetext'], $post['smilietext']);

}

then $smilie_counts should contain the correct ammount of smilieusage for each smilie.
(at least if i didn't mess something up)

Logician

07-01-2004 05:04 PM

I recall we discussed the very same thing before and I guess I offered a solution to add a new column to smilie table and populate it everytime a smilie is used while posting so that heavy load can be avoided.

Is it dejavu or am I dreaming? :)

Xenon

07-01-2004 05:06 PM

definitelly a d?j?-vu pal ;)

and iirc i said, that you ahve to populate the table as initialisation to get the real ammounts ;)

Boofo

07-01-2004 06:26 PM

Then there is no real way to get the correct amount now?

Stefan, I tried your code and got a non-object error. :(

Dean C

07-01-2004 07:03 PM

What error exactly?

Xenon

07-01-2004 07:24 PM

erm, i said code snipet Bob, not a full file ;)

you have to require global.php before of course :)

Boofo

07-01-2004 07:29 PM

Doh! on me. Sorry about that. ;)

Boofo

07-01-2004 08:22 PM

This is what I am using and I get a Query is empty error:

PHP Code:


		
			
<?php
require_once('./global.php');
$smilie_counts = array();
$posts = $DB_site->query("
  SELECT smilietext, pagetext 
  FROM post, smilie 
  WHERE pagetext like concat("%",smilie.smilietext,"%") 
");
while ($post = $DB_site->fetch_array($posts))
{
  $smilie_counts["$post[smilietext]"] += substr_count($post['pagetext'], $post['smilietext']);
}
echo "$smilie_counts";
?>

Xenon

07-01-2004 08:29 PM

try that one:

PHP Code:


		
			
<?php

require_once('./global.php');

$smilie_counts = array();

$posts = $DB_site->query("

  SELECT smilietext, pagetext 

  FROM post, smilie 

  WHERE pagetext like concat('%', smilie.smilietext, '%') 

");

while ($post = $DB_site->fetch_array($posts))

{

  $smilie_counts["$post[smilietext]"] += substr_count($post['pagetext'], $post['smilietext']);

}

$output = '';

foreach ($smilie_counts AS $name => $val)

{

  $output .= $name . ' -> ' . $val . '<br />';

}



print_output($output);

?>

sonic3d

07-01-2004 08:31 PM

works for me. though dunno if its accurate.

http://www.rpgadvance.com/forum/smilies.php

l8er
sonic

Boofo

07-01-2004 08:45 PM

Quote:

Originally Posted by Xenon

try that one:

PHP Code:


		
			
<?php
require_once('./global.php');
$smilie_counts = array();
$posts = $DB_site->query("
SELECT smilietext, pagetext 
FROM post, smilie 
WHERE pagetext like concat('%', smilie.smilietext, '%') 
");
while ($post = $DB_site->fetch_array($posts))
{
$smilie_counts["$post[smilietext]"] += substr_count($post['pagetext'], $post['smilietext']);
}
$output = '';
foreach ($smilie_counts AS $name => $val)
{
$output .= $name . ' -> ' . $val . '<br />';
}
 
print_output($output);
?>

Tahnk you, sir. That seems to be a lot faster than the other query. ;)

The only strange thing is that in your query:

:p -> 218

and in the first query:

:p -> 228

That was the only one that was lower with your query.

Also, how would we order the listing from the highest count to the lowest?

Xenon

07-01-2004 08:59 PM

hmm, interesting...

ah well, just don't care ^^

Boofo

07-01-2004 09:01 PM

Quote:

Originally Posted by Xenon

hmm, interesting...

ah well, just don't care ^^

Nice attitude. :p

I added something to my last post about how we would list them according to count. How can we do that, sir?

Xenon

07-01-2004 09:04 PM

you mean sorting?

well asort() is your friend then ;)

btw, those whoe are intersted in vb.org stats:

Code:

:p -> 9666

;) -> 33380

:D -> 18562

:) -> 72293

:cool: -> 971

:rolleyes: -> 1799

:( -> 11810

:eek: -> 779

:confused: -> 2262

:dead: -> 291

:o -> 1106

:mad: -> 470

:laugh: -> 373

:up: -> 525

:paranoid: -> 308

:cry: -> 517

:pirate: -> 103

:ermm: -> 1368

:banana: -> 1258

:down: -> 20

:squareeyed: -> 220

:angry: -> 83

:surprised: -> 339

:cheeky: -> 224

:bored: -> 113

:beard: -> 62

:bandit: -> 146

:alien: -> 98

:tired: -> 253

:smoke: -> 207

:sleep: -> 68

:rambo: -> 270

:hurt: -> 55

:glasses: -> 162

:devious: -> 247

:classic: -> 478

:chinese: -> 71

:ogre: -> 84

:ninja: -> 199

:nervous: -> 770

:lick: -> 217

:knockedout: -> 195

:speechless: -> 192

:disappointed: -> 403

:cross-eyed: -> 120

:bunny: -> 621

Dean C

07-01-2004 09:10 PM

I would have thought the wink smilie would have been used more than that ;)

Boofo

07-01-2004 09:16 PM

I tried this but it wouldn't work. :(

asort($output, SORT_STRING);

colicab-d

07-01-2004 09:17 PM

hmm no one likes :hurt: or :beard: what a shame :(

Xenon

07-01-2004 10:15 PM

@Dean: me, too ;)

@coli: let's start the initiative to use differnt smilies ^^

@Bob: hmm, try just asort($smilie_counts); before the foreach loop :p

Boofo

07-01-2004 11:49 PM

Quote:

Originally Posted by Xenon

@Bob: hmm, try just asort($smilie_counts); before the foreach loop :p

Thank you, sir. That worked. But I changed it to:

arsort($smilie_counts);

so the highest number would show first. ;)

colicab-d

07-01-2004 11:55 PM

xenon im with you :beard: hopefully my sig shall help the fight to give Unpopular Smilies More Credit lol , or the USMC lol..

anyway i added some to tmy sig

Xenon

07-02-2004 11:06 AM

hmm, sigs are not countet in the query unfortunatelly.

maybe i should add them ^^
:rambo:

Xenon

07-02-2004 11:06 AM

Quote:

Originally Posted by Boofo

Thank you, sir. That worked. But I changed it to:

arsort($smilie_counts);

so the highest number would show first. ;)

just as you like it bob ;)

Boofo

07-02-2004 11:45 AM

Quote:

Originally Posted by Xenon

hmm, sigs are not countet in the query unfortunatelly.

maybe i should add them ^^
:rambo:

When you add those, I'd like to update the query. And thank you, again, sir. ;)

Xenon

07-02-2004 02:09 PM

erm, that wasn't meant serious bob :p

All times are GMT. The time now is 06:11 AM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.02105 seconds Memory Usage 1,826KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)bbcode_code_printable (4)bbcode_php_printable (8)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (35)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: