Topic: [SMF 2.0.2] Block 'top 5 most 'liked' members

[hcfwesker]

Hey, Underdog, was wondering if you could look this one over for me.  It works perfectly, the main issue is it causes heavy load time when the block is displayed, or when modifying it and clicking the 'Preview' button.  Load time goes back to normal when the block is not displayed.  Not sure if I've got unnecessary coding in there or it's just written in a way it is causing stress to the database calls.

Preview:

Code: [Select]

global $smcFunc, $scripturl, $context, $settings, $txt, $boardurl;

$result = $smcFunc['db_query']('', '
SELECT mem.id_member, mem.real_name, lm.id_member, lm.id_message, lm.timestamp, msg.id_msg, msg.subject, msg.id_topic
FROM {db_prefix}members AS mem
LEFT JOIN {db_prefix}likes AS lm ON (lm.id_member = mem.id_member)
LEFT JOIN {db_prefix}messages AS msg ON (msg.id_msg = lm.id_message)
ORDER BY timestamp DESC
LIMIT 5');

while ($row = $smcFunc['db_fetch_assoc']($result))
{
echo'<img src="', $settings['default_images_url'], '/admin/custom_like.png" height="10" />&nbsp;<a href="'.$scripturl.'?action=profile;u='.$row['id_member'].'">'.$row['real_name'].'</a><font size="1"> Liked </font> <a href="'.$scripturl.'?topic='.$row['id_topic'].'.msg'.$row['id_message'].'#msg'.$row['id_message'].'" />' . $row['subject'] . '</a><br><hr>';
}
$smcFunc['db_free_result']($result);

[Chen Zhen]

hcfwesker & kobar,

  I meant to mention to the both of you that attempting to display data smack in the middle of a db query is taboo. It is more prudent to put the needed data into an array, free the query & then do whatever you want with the array. I will skip back to the previous block code & edit it to be correct.


hcfwesker,

  I downloaded that mod to test this but the likes table did not contain all the columns you are selecting. I will assume you have edited the mod or perhaps are using its pro version. With that said, I was not able to test the code shown below. HTML & the db query were corrected.

Code: [Select]

global $smcFunc, $scripturl, $settings;

$result = $smcFunc['db_query']('', '
SELECT mem.id_member, mem.real_name, lm.id_message, lm.timestamp, msg.subject, msg.id_topic
FROM {db_prefix}members AS mem
LEFT JOIN {db_prefix}likes AS lm ON (lm.id_member = mem.id_member)
LEFT JOIN {db_prefix}messages AS msg ON (msg.id_msg = lm.id_message)
ORDER BY timestamp DESC
LIMIT 5');

while ($row = $smcFunc['db_fetch_assoc']($result))
$likes[] = array('id_member' => $row['id_member'],'name' => $row['real_name'], 'topic' => $row['id_topic'], 'id_message' => $row['id_message'], 'subject' => $row['subject']);

$smcFunc['db_free_result']($result);

foreach ($likes as $mem => $data)
echo '<img src="', $settings['default_images_url'], '/admin/custom_like.png" style="height:10px;" />&nbsp;<a href="'.$scripturl.'?action=profile;u='.$data['id_member'].'">',$data['name'],'</a><span style="font-size:1em;"> Liked </span><a href="'.$scripturl.'?topic='.$data['topic'].'.msg'.$data['id_message'].'#msg'.$data['id_message'].'">' . $data['subject'] . '</a><br /><hr />';


[hcfwesker]

I'm new to retrieving info from the database, so to leanr I'm using other codes I see from MODs, and some in here for other blocks.

Thanx for the info, will keep that noted on future attempts. 

And, Yes, I am using the Pro Likes version, the Lite version doesn't allow you to like posts, just first post of a topic.  And I'm not sure if it keeps track of the timestamp either.

Thanx for your kind help and guidance.

[Kobar]

Thank you so much, both of you!