Changing recent posts (info center)

Customizing SMF > SMF Coding Discussion

(1/1)

aquagrrl:
I want to change the way recent posts behaves, and I was hoping I could get advice on how to do it, or where to look.

I'd like recent posts to show the 5 most recently changed topics, rather than 5 of the recent posts. For instance, as it is now, if there is an active thread, you could end up with all 5 being the same topic just different posts. I'd like to show the following info: topic title, topic starter, board, lastest post time, latest poster, views, posts.

I've been looking in sources/subs-recent.php, trying to figure out what options I have for doing this, and I am hitting a brick wall. I can pull all the info as it relates to the last post, but I'm not sure how to pull things like the topic title, or limit to showing a topic just once. Any help would be appreciated.

aquagrrl:
I am afraid I need some mysql help. I got must of it working using the Recent Topics On Board Index, and then some minor modifications to the subs-recent.php. But I am having trouble pulling up a post count for each topic. Here is what i have thus far:

--- Code: ---
SELECT
m.poster_time, ms.subject, m.id_topic, m.id_member, m.id_msg, b.id_board, b.name AS board_name, t.num_views,
t.id_member_started,
IFNULL(tm.real_name, tm.member_name) AS topic_starter,
IFNULL(mem.real_name, m.poster_name) AS poster_name,
SUBSTRING(m.body, 1, 384) AS body, m.smileys_enabled,
(SELECT COUNT(*)
FROM {db_prefix}topics
WHERE {db_prefix}topics.id_topic=ms.id_topic) as post_count
FROM {db_prefix}topics AS t
INNER JOIN {db_prefix}messages AS m ON (m.id_msg = t.id_last_msg)
INNER JOIN {db_prefix}boards AS b ON (b.id_board = t.id_board)
INNER JOIN {db_prefix}messages AS ms ON (ms.id_msg = t.id_first_msg)
INNER JOIN {db_prefix}members AS tm ON (tm.id_member = t.id_member_started)
LEFT JOIN {db_prefix}members AS mem ON (mem.id_member = m.id_member)' . (!$user_info['is_guest'] ? '
LEFT JOIN {db_prefix}log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = {int:current_member})
LEFT JOIN {db_prefix}log_mark_read AS lmr ON (lmr.id_board = b.id_board AND lmr.id_member = {int:current_member})' : '') . '
WHERE t.id_last_msg >= {int:min_message_id}
AND ' . $user_info['query_wanna_see_board'] . ($modSettings['postmod_active'] ? '
AND t.approved = {int:is_approved}
AND m.approved = {int:is_approved}' : '') . '
ORDER BY t.id_last_msg DESC
LIMIT ' . $latestPostOptions['number_posts'],
array(
'current_member' => $user_info['id'],
'min_message_id' => $modSettings['maxMsgID'] - 35 * min($latestPostOptions['number_posts'], 5),
'is_approved' => 1,
)

--- End code ---

Its the count that is throwing me off, I'm afraid I suck at mysql and this subquery doesn't seem to be counting right. It appears to be just showing me the total count of all the topics in the database. I am sure I'm doing something wrong here. My mysql is only good enough to get me in trouble, so I'm not sure what I'm doing wrong.

aquagrrl:
Got it. After much playing around, I was able to get it to work, though I've only got a rough understanding of why. Posting here for posterity. BTW, if anyone wants to comment on how to clean this up, that would be greatly appreciated.

--- Code: ---$request = $smcFunc['db_query']('', '
SELECT
m.poster_time, ms.subject, m.id_topic, m.id_member, m.id_msg, b.id_board, b.name AS board_name, t.num_views,
COUNT(t.id_topic) as post_count,
t.id_member_started,
IFNULL(tm.real_name, tm.member_name) AS topic_starter,
IFNULL(mem.real_name, m.poster_name) AS poster_name,
SUBSTRING(m.body, 1, 384) AS body, m.smileys_enabled,
COUNT(mes.id_topic) AS post_count #tami added
FROM {db_prefix}topics AS t
INNER JOIN {db_prefix}messages AS m ON (m.id_msg = t.id_last_msg)
INNER JOIN {db_prefix}boards AS b ON (b.id_board = t.id_board)
INNER JOIN {db_prefix}messages AS ms ON (ms.id_msg = t.id_first_msg)
INNER JOIN {db_prefix}members AS tm ON (tm.id_member = t.id_member_started)
LEFT JOIN {db_prefix}messages AS mes ON (mes.id_topic = t.id_topic)
LEFT JOIN {db_prefix}members AS mem ON (mem.id_member = m.id_member)' . (!$user_info['is_guest'] ? '
LEFT JOIN {db_prefix}log_topics AS lt ON (lt.id_topic = t.id_topic AND lt.id_member = {int:current_member})
LEFT JOIN {db_prefix}log_mark_read AS lmr ON (lmr.id_board = b.id_board AND lmr.id_member = {int:current_member})' : '') . '
WHERE t.id_last_msg >= {int:min_message_id}
AND ' . $user_info['query_wanna_see_board'] . ($modSettings['postmod_active'] ? '
AND t.approved = {int:is_approved}
AND m.approved = {int:is_approved}' : '') . '
GROUP BY mes.id_topic
ORDER BY t.id_last_msg DESC
LIMIT ' . $latestPostOptions['number_posts'],
array(
'current_member' => $user_info['id'],
'min_message_id' => $modSettings['maxMsgID'] - 35 * min($latestPostOptions['number_posts'], 5),
'is_approved' => 1,
)
);
--- End code ---

Also changed in subs-recent.php

--- Code: ---'topic' => array(
'id' => $row['id_topic'],
'name' => $row['topic_starter'],
'memid' => $row['id_member_started'],
'views' => $row['num_views'],
'posts' => $row['post_count']
),

--- End code ---

Can be used as

--- Code: ---<?php echo $posts['topic']['views']; ?>

--- End code ---
etc . . .

shadelow:
At least you figured it out. Nice Job.

Navigation

[0] Message Index

Go to full version