Page 2 of 2

Re: [REQ] move locked topics to bottom of viewforum?

Posted: Thu Jan 22, 2009 12:46 am
by thejdm
didnt seem to work.

here is the phpBB2 version, anyone feel like porting to bb3?

Code: Select all

#-----[ OPEN ]------------------------------------------------ 
# 
modcp.php 

# 
#-----[ FIND ]------------------------------------------------ 
# 
ORDER BY t.topic_type DESC

# 
#-----[ IN-LINE FIND ]---------------------------------------- 
# 
t.topic_type DESC

# 
#-----[ IN-LINE AFTER, ADD ]---------------------------------- 
# 
, t.topic_status ASC

# 
#-----[ OPEN ]------------------------------------------------ 
# 
search.php 

# 
#-----[ FIND ]------------------------------------------------ 
# 
$sql .= " ORDER BY "; 

# 
#-----[ AFTER, ADD ]------------------------------------------ 
# 

// Start add - Locked at the end MOD
$sql .= ' t.topic_type DESC,t.topic_status ASC, '; 
// End add - Locked at the end MOD

# 
#-----[ OPEN ]------------------------------------------------ 
# 
viewforum.php 

# 
#-----[ FIND ]------------------------------------------------ 
# 
ORDER BY t.topic_type DESC

# 
#-----[ IN-LINE FIND ]---------------------------------------- 
# 
t.topic_type DESC

# 
#-----[ IN-LINE AFTER, ADD ]---------------------------------- 
# 
, t.topic_status ASC

# 
#-----[ SAVE/CLOSE ALL FILES ]-------------------------------- 
# 

Re: [REQ] move locked topics to bottom of viewforum?

Posted: Tue Jan 27, 2009 8:16 pm
by DeViAnThans3
Strange, the code I gave you is perfectly working on 2 of my boards, both running version phpBB 3.0.4.
Which MySQL version do you have?

Re: [REQ] move locked topics to bottom of viewforum?

Posted: Wed Jan 28, 2009 1:47 am
by thejdm
i re-tried the code and still nothing. all the locked topics just vanish

MySQL version 5.0.51a-community

Re: [REQ] move locked topics to bottom of viewforum?

Posted: Wed Feb 11, 2009 7:57 am
by tumba25
There is two small errors in DeViAnThans3 edits.

Code: Select all

topic_status
in both places should be

Code: Select all

t.topic_status

Re: [REQ] move locked topics to bottom of viewforum?

Posted: Sun Feb 22, 2009 5:06 pm
by thejdm
i retired with those 2 fixes and it still didn't work. :( ah well. eventually.

Re: [REQ] move locked topics to bottom of viewforum?

Posted: Sun Feb 22, 2009 8:50 pm
by mtrs
thejdm wrote:i retired with those 2 fixes and it still didn't work. :( ah well. eventually.
I tried Johan's code, it works fine for one page forum list, but doesn't send locked topic bottom of last forum llist page.
Also, the code, given later in this topic, to send locked topics down the all list doesn't work.

So, in order to do what thejdm wanted, I improved Johan's code.
But,the way I made it work, needs one more query for the last page of viewforum. Because, I eliminated locked topics until last page sql run, then retrieved locked topic list with another query.
As the code is long, If anyone can make it smaller, it will be better. It works at localhost, but you need to test again.

Open
viewforum.php

Find

Code: Select all

// Grab just the sorted topic ids
$sql = 'SELECT t.topic_id
    FROM ' . TOPICS_TABLE . " t
    WHERE $sql_where
        AND t.topic_type IN (" . POST_NORMAL . ', ' . POST_STICKY . ")
        $sql_approved
        $sql_limit_time
    ORDER BY t.topic_type " . ((!$store_reverse) ? 'DESC' : 'ASC') . ', ' . $sql_sort_order;
$result = $db->sql_query_limit($sql, $sql_limit, $sql_start);

while ($row = $db->sql_fetchrow($result))
{
    $topic_list[] = (int) $row['topic_id'];
}
$db->sql_freeresult($result); 
Replace with

Code: Select all

// Grab just the sorted topic ids

//Begin: Locked topics down in the forum
if ($config['topics_per_page'] > $topics_count)
{    
    $sql = 'SELECT t.topic_id, t.topic_status, t.topic_type
        FROM ' . TOPICS_TABLE . " t
        WHERE $sql_where
            AND t.topic_type IN (" . POST_NORMAL . ', ' . POST_STICKY . ")
            $sql_approved
            $sql_limit_time
        ORDER BY t.topic_type " . ((!$store_reverse) ? 'DESC' : 'ASC') . ', ' . $sql_sort_order;
    $result = $db->sql_query_limit($sql, $sql_limit, $sql_start);

    $closed_topics = array();
    while ($row = $db->sql_fetchrow($result))
    {
       if ($row['topic_status'] == ITEM_LOCKED && $row['topic_type'] == POST_NORMAL)
       {
          $closed_topics[] = (int) $row['topic_id'];
       }
       else
       {
          $topic_list[] = (int) $row['topic_id'];
       }
    }
    $topic_list = array_merge($topic_list, $closed_topics);  
    $db->sql_freeresult($result);

}
else if (floor($start / $config['topics_per_page']) < floor($topics_count / $config['topics_per_page']))
{
    $sql = 'SELECT t.topic_id
        FROM ' . TOPICS_TABLE . " t
        WHERE $sql_where
            AND t.topic_type IN (" . POST_NORMAL . ', ' . POST_STICKY . ")
            AND (t.topic_status <> (" . ITEM_LOCKED . ") OR t.topic_type = (" . POST_STICKY . "))
            $sql_approved
            $sql_limit_time
        ORDER BY t.topic_type " . ((!$store_reverse) ? 'DESC' : 'ASC') . ', ' . $sql_sort_order;
    $result = $db->sql_query_limit($sql, $sql_limit, $sql_start);
    while ($row = $db->sql_fetchrow($result))
    {
        $topic_list[] = (int) $row['topic_id'];
    }
}
else
{
    $sql = 'SELECT t.topic_id
        FROM ' . TOPICS_TABLE . " t
        WHERE $sql_where
            AND t.topic_type IN (" . POST_NORMAL . ', ' . POST_STICKY . ")
            AND (t.topic_status <> (" . ITEM_LOCKED . ") OR t.topic_type = (" . POST_STICKY . "))
            $sql_approved
            $sql_limit_time
        ORDER BY t.topic_type " . ((!$store_reverse) ? 'DESC' : 'ASC') . ', ' . $sql_sort_order;
        $result = $db->sql_query_limit($sql, $sql_limit, $sql_start);
   
    while ($row = $db->sql_fetchrow($result))
    {
        $topic_list[] = (int) $row['topic_id'];          
    }
    $db->sql_freeresult($result);    
    
    $sql = 'SELECT t.topic_id
    FROM ' . TOPICS_TABLE . " t
    WHERE $sql_where
        AND t.topic_type IN (" . POST_NORMAL . ', ' . POST_STICKY . ")
        AND (t.topic_status = (" . ITEM_LOCKED . ") AND t.topic_type = (" . POST_NORMAL . "))
        $sql_approved
        $sql_limit_time
    ORDER BY t.topic_type " . ((!$store_reverse) ? 'DESC' : 'ASC') . ', ' . $sql_sort_order;
    $result = $db->sql_query_limit($sql, $topics_count, $sql_start);

    $closed_topics = array();
    while ($row = $db->sql_fetchrow($result))
    { 
        $closed_topics[] = (int) $row['topic_id'];
    }
    $db->sql_freeresult($result);

    $topic_list = array_merge($closed_topics, $topic_list);            
}
//End: Locked topics down in the forum 
Edit: Replaced the code, as it was sending locked stickies down the forum too.

Re: [REQ] move locked topics to bottom of viewforum?

Posted: Mon Feb 23, 2009 3:34 am
by thejdm
thanks! that worked great!

Re: [REQ] move locked topics to bottom of viewforum?

Posted: Mon Feb 23, 2009 11:12 am
by mtrs
thejdm wrote:thanks! that worked great!
You need to update that code, because It was also sending locked stickies down, I fixed that and edited my post above. http://www.phpbb.com/community/viewtopi ... 5#p8699435

Re: [REQ] move locked topics to bottom of viewforum?

Posted: Thu Mar 12, 2009 6:28 pm
by janine
Awesome! Thanks for this! :D

Re: [REQ] move locked topics to bottom of viewforum?

Posted: Tue Apr 14, 2009 12:24 pm
by janine
Actually, I had a lot of locked topics and this really screwed up the pagenation :( Bummer Had to take the code out.

Re: [REQ] move locked topics to bottom of viewforum?

Posted: Tue Apr 14, 2009 3:20 pm
by UseLess

Re: [REQ] move locked topics to bottom of viewforum?

Posted: Wed Apr 15, 2009 11:21 pm
by janine
This works much better! Thank you so much!! :D