order by subject disordered

General Community > Scripting Help

<< < (3/3)

Grudge:
What A.M.A said.

If you sort my subject it is looking at the title of the last post in the thread. I would recommend changing

--- Code: ---if ($board == 28) # notas y articulos laakademia.com
$ordrsql = 'm.subject DESC' ;

--- End code ---
and asign $ordersql to m2.subject DESC

and you can either add an extra line to the first query getting another 'FROM {$db_prefix}messages as m2' and in the WHERE part adding m2.ID_MSG=t.ID_FIRST_MSG

That will add slightly to the thread load time I guess so you could just make up some if's to decide whether to look at the last or first message in that query.

That may not be the whole bug but it must be a big part of it...

Anguz:
the query things were already there, but I change the 'm.subject DESC' to 'm2.subject DESC' like you said, Grudge, and didn't work, but it was a similar error to when I wanted to order by date started, so I fixed it like A.M.A. sugested for that one, putting it a few lines ahead, so it now looks like this:

--- Code: ---   // Grab the appropriate topic information

      $ordrsql = 'm.posterTime DESC' ;

   $stickyOrder = ($modSettings['enableStickyTopics'] == 1 ? 't.isSticky DESC,' : '');
   $result = mysql_query("
      SELECT t.ID_TOPIC
      FROM {$db_prefix}topics AS t, {$db_prefix}messages AS m
      WHERE m.ID_MSG=t.ID_LAST_MSG
         AND t.ID_BOARD=$currentboard
      ORDER BY $stickyOrder $ordrsql
      LIMIT $start,$maxindex") or database_error(__FILE__, __LINE__);
   $topics = array();

   if ($board == 28) # notas y articulos laakademia.com
      $ordrsql = 'm2.subject DESC' ;

   while ($row = mysql_fetch_assoc($result))
      $topics[] = $row['ID_TOPIC'];
   if (count($topics))
   {
      $result = mysql_query("
         SELECT t.ID_LAST_MSG, t.ID_TOPIC, t.numReplies, t.locked, m.posterName, m.ID_MEMBER, IFNULL(mem.realName, m.posterName) AS posterDisplayName, t.numViews, m.posterTime, m.modifiedTime, t.ID_FIRST_MSG, t.isSticky, t.ID_POLL, m2.posterName as mname, m2.ID_MEMBER as mid, IFNULL(mem2.realName, m2.posterName) AS firstPosterDisplayName, m2.subject as msub, m2.icon as micon, IFNULL(lt.logTime, 0) AS isRead, IFNULL(lmr.logTime, 0) AS isMarkedRead, m2.posterTime AS mtime
         FROM {$db_prefix}topics as t, {$db_prefix}messages as m, {$db_prefix}messages as m2
            LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER=m.ID_MEMBER)
            LEFT JOIN {$db_prefix}members AS mem2 ON (mem2.ID_MEMBER=m2.ID_MEMBER)
            LEFT JOIN {$db_prefix}log_topics AS lt ON (lt.ID_TOPIC=t.ID_TOPIC AND lt.ID_MEMBER=$ID_MEMBER)
            LEFT JOIN {$db_prefix}log_mark_read AS lmr ON (lmr.ID_BOARD=$currentboard AND lmr.ID_MEMBER=$ID_MEMBER)
         WHERE t.ID_TOPIC IN (" . implode(',', $topics) . ")
            AND m.ID_MSG=t.ID_LAST_MSG
            AND m2.ID_MSG=t.ID_FIRST_MSG
         ORDER BY $stickyOrder $ordrsql") or database_error(__FILE__, __LINE__);
--- End code ---

it now orders the threads correctly in each page, but not not as a whole... I mean, each page is ordered independent of the other pages in that board, with whatever threads appear in that page... how can I make it work with the board as a whole?

I'm so glad it's starting to work... it was really annoying not knowing what the heck was going on! thank you guys :)

Grudge:
I meant for you to change the first statement as well :) I have changed your code to what I think *may* work. I've basically moved the if $board=28 statement back up top and added an extra line to the first query

--- Code: ---   // Grab the appropriate topic information

      $ordrsql = 'm.posterTime DESC' ;
     if ($board == 28) # notas y articulos laakademia.com
$ordrsql = 'm2.subject DESC' ;
   $stickyOrder = ($modSettings['enableStickyTopics'] == 1 ? 't.isSticky DESC,' : '');
   $result = mysql_query("
      SELECT t.ID_TOPIC
      FROM {$db_prefix}topics AS t, {$db_prefix}messages AS m, {$db_prefix}messages AS m2
      WHERE m.ID_MSG=t.ID_LAST_MSG
AND m2.ID_MSG=t.ID_FIRST_MSG
         AND t.ID_BOARD=$currentboard
      ORDER BY $stickyOrder $ordrsql
      LIMIT $start,$maxindex") or database_error(__FILE__, __LINE__);
   $topics = array();

   while ($row = mysql_fetch_assoc($result))
      $topics[] = $row['ID_TOPIC'];
   if (count($topics))
   {
      $result = mysql_query("
         SELECT t.ID_LAST_MSG, t.ID_TOPIC, t.numReplies, t.locked, m.posterName, m.ID_MEMBER, IFNULL(mem.realName, m.posterName) AS posterDisplayName, t.numViews, m.posterTime, m.modifiedTime, t.ID_FIRST_MSG, t.isSticky, t.ID_POLL, m2.posterName as mname, m2.ID_MEMBER as mid, IFNULL(mem2.realName, m2.posterName) AS firstPosterDisplayName, m2.subject as msub, m2.icon as micon, IFNULL(lt.logTime, 0) AS isRead, IFNULL(lmr.logTime, 0) AS isMarkedRead, m2.posterTime AS mtime
         FROM {$db_prefix}topics as t, {$db_prefix}messages as m, {$db_prefix}messages as m2
            LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER=m.ID_MEMBER)
            LEFT JOIN {$db_prefix}members AS mem2 ON (mem2.ID_MEMBER=m2.ID_MEMBER)
            LEFT JOIN {$db_prefix}log_topics AS lt ON (lt.ID_TOPIC=t.ID_TOPIC AND lt.ID_MEMBER=$ID_MEMBER)
            LEFT JOIN {$db_prefix}log_mark_read AS lmr ON (lmr.ID_BOARD=$currentboard AND lmr.ID_MEMBER=$ID_MEMBER)
         WHERE t.ID_TOPIC IN (" . implode(',', $topics) . ")
            AND m.ID_MSG=t.ID_LAST_MSG
            AND m2.ID_MSG=t.ID_FIRST_MSG
         ORDER BY $stickyOrder $ordrsql") or database_error(__FILE__, __LINE__);
--- End code ---

That should work I believe. The only problem I have with the code is it's a little inefficient as you are always pulling out on variable you don't need but I can't believe you'll notice a load time difference.

Anguz:
it's working! it's working! :o

THANK YOU! :D

Navigation

[0] Message Index

[*] Previous page

Go to full version