Most popular smilies

[Boofo]

Originally Posted by 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

Ok, then, how would you do the combination?

[Xenon]

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]

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]

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]

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]

What error exactly?

[Xenon]

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

you have to require global.php before of course

[Boofo]

Doh! on me. Sorry about that.

[Boofo]

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]

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