title: SQL Query merging with PHP assistance please
body: <p>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.</p>
  <p>I've attempted to fix this myself but I don't want to lose any functionality, here is my attempt.</p>
  <pre><code>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}',
  </code></pre>
  <p>to this for testing purposes</p>
  <pre><code>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
  </code></pre>
  <p>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.</p>
  <p>Here the structures</p>
  <p>boards structure
  <img src="https://i.stack.imgur.com/Rgtt2.png" alt="alt text" />
  <img src="https://i.stack.imgur.com/yemM7.png" alt="alt text" />
  <img src="https://i.stack.imgur.com/px3v3.png" alt="alt text" /></p>
  <pre><code>   $topic_ids = array();
     $context['topics'] = array();
  
     // Sequential pages are often not optimized, so we add an additional query.
     $pre_query = $start &gt; 0;
     if ($pre_query &amp;&amp; $maxindex &gt; 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' =&gt; $board,
              'current_member' =&gt; $user_info['id'],
              'is_approved' =&gt; 1,
              'id_member_guest' =&gt; 0,
              'start' =&gt; $start,
              'maxindex' =&gt; $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']) &amp;&amp; !empty($options['messages_per_page']) &amp;&amp; !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' =&gt; $board,
              'current_member' =&gt; $user_info['id'],
              'topic_list' =&gt; $topic_ids,
              'is_approved' =&gt; 1,
              'find_set_topics' =&gt; implode(',', $topic_ids),
              'start' =&gt; $start,
              'maxindex' =&gt; $maxindex,
           )
        );
  </code></pre>
  <p>Thanks - I have been suffering with this for a whole week now.</p>