SQL Query merging with PHP assistance please

I want to sort the messages by poster_time instead of the usual id incrementing as I have restored a database with auto incrementing id’s and very old messages show up on top I had like to be able to sort them by poster_time to fix that problem.

I’ve attempted to fix this myself but I don’t want to lose any functionality, here is my attempt.

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}',

to this for testing purposes

SELECT t.id_topic
            FROM topics AS t
                INNER JOIN messages AS ml ON (ml.id_msg = t.id_last_msg)
                INNER JOIN messages AS mf ON (mf.id_msg = t.id_first_msg)
                LEFT JOIN members AS memf ON (memf.id_member = mf.id_member)
                LEFT JOIN members AS meml ON (meml.id_member = ml.id_member)
            WHERE t.id_board = 1
      ORDER BY ml.poster_time DESC
            LIMIT 0, 500

Using the code above I’ve figured out I need ml.poster_time DESC how do I bundle it up with the code at the very top.

Here the structures

boards structure alt text

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

Thanks – I have been suffering with this for a whole week now.

Answer

It looks like you’re wanting topics to be sorted in this order: 1) sticky, 2) sort from query string or if empty, 3) post date.

Before you assemble your query variable, add this line:

$_REQUEST['sort'] = (empty($_REQUEST['sort'])) ? 'ml.poster_time' : $_REQUEST['sort'];

Basically this is saying that if another sort method hasn’t already been chosen (i.e. alphabetical), then default to sorting by the date posted.

Advertisement

Answer