[Beta 1.5.3]Alternative MessageIndex sort

[Anguz]

A.M.A.

I have moded a forum to show the date started together with the member that started it using this mod:
http://www.yabbse.org/community/index.php?board=141;action=display;threadid=25094&start=0

what would I need to change in the second zip file mod to sort it by date started?

here's the forum I'm doing some tests in:
http://www.laakademia.com/forums

thx!

[A.M.A.]

after you apply the mod .. look in MessageIndex.php for:

Code Select Expand

  $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();
add after it:

Code Select Expand

 if ($ordrmindex == 9)
    $ordrsql = 'm2.posterTime ASC' ;
 else if ($ordrmindex == 10)
    $ordrsql = 'm2.posterTime DESC' ;

and look for :

Code Select Expand

     $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
add after the last word (in the same line)

Code Select Expand

, m2.posterTime AS mtime

this will cause the sort of the [Last post] to be sorted by the date of the original post starter.

Anguz:
It can be done with Grudge's Mod .. but you need to put an extra arrows for the extra field. If you can not do it I might help you.

[dthomas]

Thanks for all the work my members will appriciate it. They have been asking for this for a long time.

[Anguz]

I installed the mod with the menu, but not the last changes mentioned in your reply yet, just the mod as it is in the zip

it doesn't seem to be sorting the threads correctly, check my test forum and you'll see what I mean

http://llama.lewismedia.com/~anguz/forums/index.php?board=19&ordrmindex=3
that's for "Started by -> ASC"

I checked the other options and the problem seems to be with the two Started by and the two Subject ones... the numbers and dates work fine

what could it be?

[Anguz]

I took the mod out of my test forum, so the previous link won't work

but you can see the bug in Webby's forum (it's the only other forum I know with this mod)

http://www.salesplaza.nl/forum/index.php?board=37&ordrmindex=3
that's for "Started by -> ASC" too

I'll hardcode the alt order for a couple of forums that are ordered by date started, but 'm2.posterTime DESC' isn't working for this, I get this error "Unknown table 'm2' in order clause"

what should I change it to?

[A.M.A.]

for "Started by -> ASC" check my Reply #23

"Unknown table 'm2' in order clause" error is caused when the "m2.posterTime" is not defined! check Reply #31 the last quote!

[Anguz]

for "Started by -> ASC" check my Reply #23

"Unknown table 'm2' in order clause" error is caused when the "m2.posterTime" is not defined! check Reply #31 the last quote!

I see, so that fixes the "started by" thing, thx... would that also fix the same problem in "subject"?

about the "unknown table" errer, I did define it like you said! but it doesn't work... here's the code I have:

Code Select Expand

   // Grab the appropriate topic information

   if ($board == 30)     # Testing Forum
      $ordrsql = 'm.subject DESC' ;
   else if ($board == 19)    # Another Test Forum
      $ordrsql = 'm2.posterTime DESC' ;
   else
      $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();

   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__);



is it ok to define $ordrsql using m2.posterTime before defining it? this my be a silly question, but I'm still learning  

[A.M.A.]

... would that also fix the same problem in "subject"?

what problem?

   // Grab the appropriate topic information

   if ($board == 30)     # Testing Forum
      $ordrsql = 'm.subject DESC' ;
   else if ($board == 19)    # Another Test Forum
      $ordrsql = 'm2.posterTime DESC' ;
   else
      $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();
\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/
   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__);

in my last reply I put the code in the green line!

is it ok to define $ordrsql using m2.posterTime before defining it? this my be a silly question, but I'm still learning

Yes it is OK .. but when using it with DB the 'm2.posterTime' must be there.
same here am still learning

[Anguz]

heh... I did it like that the first time, but didn't work, or so I thought, I was probably so sleepy that time that I didn't notice that it did... sorry

it's working great now

about the subject thing... look at this board I have set to "subject DESC"
http://llama.lewismedia.com/~anguz/forums/index.php?board=19

I was wondering if there's a problem ordering alphabetically, but that doesn't make much sense since only one is out of place... so maybe there's a difference in how the data was saved? I don't know, I don't understand it...

the good thing is that I don't need to worry too much about it, cause the forum I'll order by subject will have all the subjects starting with a date, but it still makes me think and thought I should mention it to include a fix for it in your mod

I remember seeing it happen in Webby's forum too, but I couldn't load it a moment ago that I wanted to check again just to make sure it wasn't just me getting this

thank you for your great help A.M.A.!

I made my first mod the other day, pls check it out and tell me what you think ^^