SMF 2.0 Sorting

[sspoke]

This is probably the wrong section, hell wrong forum but it's worth a try as SMF forums are abandoned

 

this from  MessageIndex.php function MessageIndex()

which shows the list of topics on the current board you are viewing

 

 

each topics table (threads) can hold many messages table (replies)

each message has a poster_time which is the epoch unix timestamp

 

Now the problem is it's not properly getting sorted.

 

The topics (threads) are sorted by the primary ID

and are bumped to top if messages (replies) which contain also a primary id are highest.

Pretty much what i'm trying to say messages or topics doesn't matter the highest primary id wins the spotlight.

 

Now I am restoring my database I did some edits to it so it would be compatible with SMF 2.0.

 

Some threads are as old as 1997 yet the last thread posted is on top of the page..

 

So my question is how do I convert the below SQL queries so I can sort by the timestamp and not the highest Id.

 

( I know this won't be a problem if you run the forum properly but if I restored a database what can I do now ).

 

Thank you.

 

function MessageIndex()
{
$topic_ids = array();
$context['topics'] = array();

// Sequential pages are often not optimized, so we add an additional query.
$pre_query = $start > 0;
if ($pre_query && $maxindex > 0)
{
	$request = $smcFunc['db_query']('', '
		SELECT t.id_topic
		FROM {db_prefix}topics AS t' . ($context['sort_by'] === 'last_poster' ? '
			INNER JOIN {db_prefix}messages AS ml ON (ml.id_msg = t.id_last_msg)' : (in_array($context['sort_by'], array('starter', 'subject')) ? '
			INNER JOIN {db_prefix}messages AS mf ON (mf.id_msg = t.id_first_msg)' : '')) . ($context['sort_by'] === 'starter' ? '
			LEFT JOIN {db_prefix}members AS memf ON (memf.id_member = mf.id_member)' : '') . ($context['sort_by'] === 'last_poster' ? '
			LEFT JOIN {db_prefix}members AS meml ON (meml.id_member = ml.id_member)' : '') . '
		WHERE 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 ' . (!empty($modSettings['enableStickyTopics']) ? 'is_sticky' . ($fake_ascending ? '' : ' DESC') . ', ' : '') . $_REQUEST['sort'] . ($ascending ? '' : ' DESC') . '
		LIMIT {int:start}, {int:maxindex}',
		array(
			'current_board' => $board,
			'current_member' => $user_info['id'],
			'is_approved' => 1,
			'id_member_guest' => 0,
			'start' => $start,
			'maxindex' => $maxindex,
		)
	);
	$topic_ids = array();
	while ($row = $smcFunc['db_fetch_assoc']($request))
		$topic_ids[] = $row['id_topic'];
}

// Grab the appropriate topic information...
if (!$pre_query || !empty($topic_ids))
{
	// For search engine effectiveness we'll link guests differently.
	$context['pageindex_multiplier'] = empty($modSettings['disableCustomPerPage']) && !empty($options['messages_per_page']) && !WIRELESS ? $options['messages_per_page'] : $modSettings['defaultMaxMessages'];

	$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, t.is_solved, 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,
		)
	);

	// Begin 'printing' the message index for current board.
	while ($row = $smcFunc['db_fetch_assoc']($result))
	{
		//...
	}
}