changing query datetime range on a single board

[Troytime]

greetings.

i run a forum where one of the boards frequently contains sensitive information.
I want the data to remain public, but I'd like to offer a 'perk' for logged in users so they see it first.

I'd like logged in users to see ALL the content of this board, but NON logged in users to see only topics started 24 hours ago (and older)

I know php and am very familiar with mysql - but I don't know the smf codebase all that well.

Can anyone instruct me to which file I need to be working with?
Any other advice is certainly appreciated.

[Sir Osis of Liver]

Have a look in /Sources/Display.php.

[Troytime]

I don't think its in display.php, display.php displays a topic and its messages.

I'm looking to alter the query that grabs all of a boards topics.
I believe i need to be working in messageindex.php, but I haven't dug deep enough yet.

[Sir Osis of Liver]

Ok, I see what you're trying to do.  Looks like you'd have to work with this query -

Code Select Expand



$result = $smcFunc['db_query']('substring', '
SELECT
t.id_topic, t.num_replies, t.locked, t.num_views, t.is_sticky, t.id_poll, t.id_previous_board,
' . ($user_info['is_guest'] ? '0' : 'IFNULL(lt.id_msg, IFNULL(lmr.id_msg, -1)) + 1') . ' AS new_from,
t.id_last_msg, t.approved, t.unapproved_posts, ml.poster_time AS last_poster_time,
ml.id_msg_modified, ml.subject AS last_subject, ml.icon AS last_icon,
ml.poster_name AS last_member_name, ml.id_member AS last_id_member,
IFNULL(meml.real_name, ml.poster_name) AS last_display_name, t.id_first_msg,
mf.poster_time AS first_poster_time, mf.subject AS first_subject, mf.icon AS first_icon,
mf.poster_name AS first_member_name, mf.id_member AS first_id_member,
IFNULL(memf.real_name, mf.poster_name) AS first_display_name, SUBSTRING(ml.body, 1, 385) AS last_body,
SUBSTRING(mf.body, 1, 385) AS first_body, ml.smileys_enabled AS last_smileys, mf.smileys_enabled AS first_smileys
FROM {db_prefix}topics AS t
INNER JOIN {db_prefix}messages AS ml ON (ml.id_msg = t.id_last_msg)
INNER JOIN {db_prefix}messages AS mf ON (mf.id_msg = t.id_first_msg)
LEFT JOIN {db_prefix}members AS meml ON (meml.id_member = ml.id_member)
LEFT JOIN {db_prefix}members AS memf ON (memf.id_member = mf.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 = {int:current_board} AND lmr.id_member = {int:current_member})'). '
WHERE ' . ($pre_query ? 't.id_topic IN ({array_int:topic_list})' : 't.id_board = {int:current_board}') . (!$modSettings['postmod_active'] || $context['can_approve_posts'] ? '' : '
AND (t.approved = {int:is_approved}' . ($user_info['is_guest'] ? '' : ' OR t.id_member_started = {int:current_member}') . ')') . '
ORDER BY ' . ($pre_query ? 'FIND_IN_SET(t.id_topic, {string:find_set_topics})' : (!empty($modSettings['enableStickyTopics']) ? 'is_sticky' . ($fake_ascending ? '' : ' DESC') . ', ' : '') . $_REQUEST['sort'] . ($ascending ? '' : ' DESC')) . '
LIMIT ' . ($pre_query ? '' : '{int:start}, ') . '{int:maxindex}',
array(
'current_board' => $board,
'current_member' => $user_info['id'],
'topic_list' => $topic_ids,
'is_approved' => 1,
'find_set_topics' => implode(',', $topic_ids),
'start' => $start,
'maxindex' => $maxindex,
)
);




If $user_info['is_guest'], you'd need poster_time for first message in each topic, basically this -

Code Select Expand



SELECT id_first_msg FROM smf_topics WHERE id_topic = ' '

SELECT poster_time FROM smf_messages WHERE id_msg = ' '. 



If your MySQL is a lot better than mine, you should be able to integrate it into the main query.  Then you'd have to filter the results for time() - 86400, possibly in the LIMIT array.  It would not affect recent messages in topics that are less than 24 hrs. old.

[Troytime]

Thank you!!