MODS.DB9.DK

[wweoee3]

with the global announcement mod, global announcement threads always stay at the top of the viewforum.php topic list. I need them to move down the list like normal threads. I've looked through all the code changes that the mod makes and tried a bunch of changes, but I can't get it to do this.

any ideas?
I'm hoping the author can tell me exactly what code makes them stay at the top.

thanks

[yltim8_zpamr]

It's quite easy, actually. Namely, when you look at the code in viewforum.php, you will see that the global announcements are fetched from the database before the announcements, which, in turn, are fetched before the normal and sticky announcements. The code looks like this:

Code: Select all

//
// All GLOBAL announcement data, this keeps GLOBAL announcements
// on each viewforum page ...
//
$sql = "SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_time, p.post_username
   FROM " . TOPICS_TABLE . " t, " . USERS_TABLE . " u, " . POSTS_TABLE . " p, " . USERS_TABLE . " u2
   WHERE t.topic_poster = u.user_id
      AND p.post_id = t.topic_last_post_id
      AND p.poster_id = u2.user_id
      AND t.topic_type = " . POST_GLOBAL_ANNOUNCE . "
   ORDER BY t.topic_last_post_id DESC ";
if( !$result = $db->sql_query($sql) )
{
   message_die(GENERAL_ERROR, "Couldn't obtain topic information", "", __LINE__, __FILE__, $sql);
}

$topic_rowset = array();
$total_announcements = 0;
while( $row = $db->sql_fetchrow($result) )
{
   $topic_rowset[] = $row;
   $total_announcements++;
}

$db->sql_freeresult($result);
// End add - Global announcement MOD





//
// All announcement data, this keeps announcements
// on each viewforum page ...
//
$sql = "SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_time, p.post_username
   FROM " . TOPICS_TABLE . " t, " . USERS_TABLE . " u, " . POSTS_TABLE . " p, " . USERS_TABLE . " u2
   WHERE t.forum_id = $forum_id
      AND t.topic_poster = u.user_id
      AND p.post_id = t.topic_last_post_id
      AND p.poster_id = u2.user_id
      AND t.topic_type = " . POST_ANNOUNCE . "
   ORDER BY t.topic_last_post_id DESC ";
if ( !($result = $db->sql_query($sql)) )
{
   message_die(GENERAL_ERROR, 'Could not obtain topic information', '', __LINE__, __FILE__, $sql);
}


// 2 rows deleted - Global announcement MOD

while( $row = $db->sql_fetchrow($result) )
{
   $topic_rowset[] = $row;
   $total_announcements++;
}

$db->sql_freeresult($result);





//
// Grab all the basic data (all topics except announcements)
// for this forum
//
$sql = "SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p2.post_username AS post_username2, p2.post_time
   FROM " . TOPICS_TABLE . " t, " . USERS_TABLE . " u, " . POSTS_TABLE . " p, " . POSTS_TABLE . " p2, " . USERS_TABLE . " u2
   WHERE t.forum_id = $forum_id
      AND t.topic_poster = u.user_id
      AND p.post_id = t.topic_first_post_id
      AND p2.post_id = t.topic_last_post_id
      AND u2.user_id = p2.poster_id
      AND t.topic_type <> " . POST_ANNOUNCE . "
      AND t.topic_type <> " . POST_GLOBAL_ANNOUNCE . "
      $limit_topics_time
   ORDER BY t.topic_type DESC, t.topic_last_post_id DESC
   LIMIT $start, ".$board_config['topics_per_page'];
if ( !($result = $db->sql_query($sql)) )
{
   message_die(GENERAL_ERROR, 'Could not obtain topic information', '', __LINE__, __FILE__, $sql);
}

$total_topics = 0;
while( $row = $db->sql_fetchrow($result) )
{
   $topic_rowset[] = $row;
   $total_topics++;
}

$db->sql_freeresult($result);



As you can see, it fetches global announcements first, then normal announcements, then all other topics, which are first ordered by topic_type, so that stickies are displayed at the top, then by topic_last_post_id, so that they are then ordered by last post time.



To enable the global announcements to descend in a normal way, so that they will be among the normal topics, we must out-comment the code block which fetches the global announcements and fetch them together with the normal and sticky topics. This looks quite easy at first, but there is a catch to this. Namely, the topics are primarily sorted by topic_type descending, so if we just add the fetching to the statement, the global announcements will still always be at the top of the page because their topic_type code is 3, where stickies and normal topics have 1 and 0, respectively.

So, what are we going to do about that? We can solve the problem by using the UNION sql statement. We will first fetch all topics which aren't either normal, announcement or global announcement and sort them by topic_type primarily, then topic_last_post_id. This result set we will UNION with another SELECT statement which, in turn, fetches all normal topics and global announcements.

Keep in mind: a normal topic is forum-bound, a global announcement isn't.

So, what is the code going to look like? We commented out the whole Global Announcement part, from

Code: Select all

//
// All GLOBAL announcement data, this keeps GLOBAL announcements
// on each viewforum page ...
//



to

Code: Select all

// End add - Global announcement MOD



Now we will rewrite the third SQL statement to fetch global announcements too in such a way that they will stand among the normal topics and descend like normal topics. We begin with:

Code: Select all

"SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p2.post_username AS post_username2, p2.post_time
   FROM " . TOPICS_TABLE . " t, " . USERS_TABLE . " u, " . POSTS_TABLE . " p, " . POSTS_TABLE . " p2, " . USERS_TABLE . " u2
   WHERE t.forum_id = $forum_id
      AND t.topic_poster = u.user_id
      AND p.post_id = t.topic_first_post_id
      AND p2.post_id = t.topic_last_post_id
      AND u2.user_id = p2.poster_id
      AND t.topic_type <> " . POST_ANNOUNCE . "
      AND t.topic_type <> " . POST_GLOBAL_ANNOUNCE . "
      $limit_topics_time
   ORDER BY t.topic_type DESC, t.topic_last_post_id DESC
   LIMIT $start, ".$board_config['topics_per_page']



So we want the statement to fetch all topics except normal, anncouncements and global announcements first and sort them like in the original SQL statement, topic_type descending first, then topic_last_post_id descending. To do this, we just take the original SQL statement and add

Code: Select all

AND t.topic_type <> " . POST_NORMAL . "



So it becomes:

Code: Select all

"SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p2.post_username AS post_username2, p2.post_time
   FROM " . TOPICS_TABLE . " t, " . USERS_TABLE . " u, " . POSTS_TABLE . " p, " . POSTS_TABLE . " p2, " . USERS_TABLE . " u2
   WHERE t.forum_id = $forum_id
      AND t.topic_poster = u.user_id
      AND p.post_id = t.topic_first_post_id
      AND p2.post_id = t.topic_last_post_id
      AND u2.user_id = p2.poster_id
      AND t.topic_type <> " . POST_NORMAL . "
      AND t.topic_type <> " . POST_ANNOUNCE . "
      AND t.topic_type <> " . POST_GLOBAL_ANNOUNCE . "
      $limit_topics_time
   ORDER BY t.topic_type DESC, t.topic_last_post_id DESC"



(We will want the LIMIT clause at the end, after the UNION)

Now, we want a SELECT statement which fetches all global and normal topics and orders them by topic_last_post_id descending only. This isn't too hard to do either. We just take the original statement, remove all the "AND topic_type <> " clauses and add the following code right after "WHERE":

Code: Select all

      (
         (
            t.forum_id = $forum_id
            AND t.topic_type = " . POST_NORMAL . "
         )
         OR t.topic_type = " . POST_GLOBAL_ANNOUNCE . "
      )



This ensures that the topic is either normal and in its rightful forum, or it is a global announcement, which is viewable in all forums. Now we just need to remove the "t.topic_type DESC, " from the ORDER BY clause, so that we finally get:

Code: Select all

"SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p2.post_username AS post_username2, p2.post_time
   FROM " . TOPICS_TABLE . " t, " . USERS_TABLE . " u, " . POSTS_TABLE . " p, " . POSTS_TABLE . " p2, " . USERS_TABLE . " u2
   WHERE
      (
         (
            t.forum_id = $forum_id
            AND t.topic_type = " . POST_NORMAL . "
         )
         OR t.topic_type = " . POST_GLOBAL_ANNOUNCE . "
      )
      AND t.topic_poster = u.user_id
      AND p.post_id = t.topic_first_post_id
      AND p2.post_id = t.topic_last_post_id
      AND u2.user_id = p2.poster_id
      $limit_topics_time
   ORDER BY t.topic_last_post_id DESC"



Finally, we UNION the two result sets together, getting the following SQL statement:

Code: Select all

"(
   SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p2.post_username AS post_username2, p2.post_time
   FROM " . TOPICS_TABLE . " t, " . USERS_TABLE . " u, " . POSTS_TABLE . " p, " . POSTS_TABLE . " p2, " . USERS_TABLE . " u2
   WHERE t.forum_id = $forum_id
      AND t.topic_poster = u.user_id
      AND p.post_id = t.topic_first_post_id
      AND p2.post_id = t.topic_last_post_id
      AND u2.user_id = p2.poster_id
      AND t.topic_type <> " . POST_NORMAL . "
      AND t.topic_type <> " . POST_ANNOUNCE . "
      AND t.topic_type <> " . POST_GLOBAL_ANNOUNCE . "
      $limit_topics_time
   ORDER BY t.topic_type DESC, t.topic_last_post_id DESC
   )
   UNION
   (
   SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p2.post_username AS post_username2, p2.post_time
   FROM " . TOPICS_TABLE . " t, " . USERS_TABLE . " u, " . POSTS_TABLE . " p, " . POSTS_TABLE . " p2, " . USERS_TABLE . " u2
   WHERE
      (
         (
            t.forum_id = $forum_id
            AND t.topic_type = " . POST_NORMAL . "
         )
         OR t.topic_type = " . POST_GLOBAL_ANNOUNCE . "
      )
      AND t.topic_poster = u.user_id
      AND p.post_id = t.topic_first_post_id
      AND p2.post_id = t.topic_last_post_id
      AND u2.user_id = p2.poster_id
      $limit_topics_time
   ORDER BY t.topic_last_post_id DESC
   )
   LIMIT $start, ".$board_config['topics_per_page']



The result is as follows:

Code: Select all

/*
//
// All GLOBAL announcement data, this keeps GLOBAL announcements
// on each viewforum page ...
//
$sql = "SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_time, p.post_username
   FROM " . TOPICS_TABLE . " t, " . USERS_TABLE . " u, " . POSTS_TABLE . " p, " . USERS_TABLE . " u2
   WHERE t.topic_poster = u.user_id
      AND p.post_id = t.topic_last_post_id
      AND p.poster_id = u2.user_id
      AND t.topic_type = " . POST_GLOBAL_ANNOUNCE . "
   ORDER BY t.topic_last_post_id DESC ";
if( !$result = $db->sql_query($sql) )
{
   message_die(GENERAL_ERROR, "Couldn't obtain topic information", "", __LINE__, __FILE__, $sql);
}

$topic_rowset = array();
$total_announcements = 0;
while( $row = $db->sql_fetchrow($result) )
{
   $topic_rowset[] = $row;
   $total_announcements++;
}

$db->sql_freeresult($result);
// End add - Global announcement MOD
*/
//
// All announcement data, this keeps announcements
// on each viewforum page ...
//
$sql = "SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_time, p.post_username
   FROM " . TOPICS_TABLE . " t, " . USERS_TABLE . " u, " . POSTS_TABLE . " p, " . USERS_TABLE . " u2
   WHERE t.forum_id = $forum_id
      AND t.topic_poster = u.user_id
      AND p.post_id = t.topic_last_post_id
      AND p.poster_id = u2.user_id
      AND t.topic_type = " . POST_ANNOUNCE . "
   ORDER BY t.topic_last_post_id DESC ";
if ( !($result = $db->sql_query($sql)) )
{
   message_die(GENERAL_ERROR, 'Could not obtain topic information', '', __LINE__, __FILE__, $sql);
}


// 2 rows deleted - Global announcement MOD

while( $row = $db->sql_fetchrow($result) )
{
   $topic_rowset[] = $row;
   $total_announcements++;
}

$db->sql_freeresult($result);

//
// Grab all the basic data (all topics except announcements)
// for this forum
//

/*
$sql = "SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p2.post_username AS post_username2, p2.post_time
   FROM " . TOPICS_TABLE . " t, " . USERS_TABLE . " u, " . POSTS_TABLE . " p, " . POSTS_TABLE . " p2, " . USERS_TABLE . " u2
   WHERE t.forum_id = $forum_id
      AND t.topic_poster = u.user_id
      AND p.post_id = t.topic_first_post_id
      AND p2.post_id = t.topic_last_post_id
      AND u2.user_id = p2.poster_id
      AND t.topic_type <> " . POST_ANNOUNCE . "
      AND t.topic_type <> " . POST_GLOBAL_ANNOUNCE . "
      $limit_topics_time
   ORDER BY t.topic_type DESC, t.topic_last_post_id DESC
   LIMIT $start, ".$board_config['topics_per_page'];
*/

$sql = "(
   SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p2.post_username AS post_username2, p2.post_time
   FROM " . TOPICS_TABLE . " t, " . USERS_TABLE . " u, " . POSTS_TABLE . " p, " . POSTS_TABLE . " p2, " . USERS_TABLE . " u2
   WHERE t.forum_id = $forum_id
      AND t.topic_poster = u.user_id
      AND p.post_id = t.topic_first_post_id
      AND p2.post_id = t.topic_last_post_id
      AND u2.user_id = p2.poster_id
      AND t.topic_type <> " . POST_NORMAL . "
      AND t.topic_type <> " . POST_ANNOUNCE . "
      AND t.topic_type <> " . POST_GLOBAL_ANNOUNCE . "
      $limit_topics_time
   ORDER BY t.topic_type DESC, t.topic_last_post_id DESC
   )
   UNION
   (
   SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p2.post_username AS post_username2, p2.post_time
   FROM " . TOPICS_TABLE . " t, " . USERS_TABLE . " u, " . POSTS_TABLE . " p, " . POSTS_TABLE . " p2, " . USERS_TABLE . " u2
   WHERE
      (
         (
            t.forum_id = $forum_id
            AND t.topic_type = " . POST_NORMAL . "
         )
         OR t.topic_type = " . POST_GLOBAL_ANNOUNCE . "
      )
      AND t.topic_poster = u.user_id
      AND p.post_id = t.topic_first_post_id
      AND p2.post_id = t.topic_last_post_id
      AND u2.user_id = p2.poster_id
      $limit_topics_time
   ORDER BY t.topic_last_post_id DESC
   )
   LIMIT $start, ".$board_config['topics_per_page'];
   
if ( !($result = $db->sql_query($sql)) )
{
   message_die(GENERAL_ERROR, 'Could not obtain topic information', '', __LINE__, __FILE__, $sql);
}

$total_topics = 0;
while( $row = $db->sql_fetchrow($result) )
{
   $topic_rowset[] = $row;
   $total_topics++;
}

$db->sql_freeresult($result);