Finding topic $start from a phpBB post number

[chrishayes]

I am trying to configure my phpBB installation to find the topic start number from the postid. This currently works with the following code:

The SQL:

Code: Select all

$join_sql_table = ( empty($post_id) ) ? '' : ", " . POSTS_TABLE . " p, " . POSTS_TABLE . " p2 ";
$join_sql = ( empty($post_id) ) ? "t.topic_id = $topic_id" : "p.post_id = $post_id AND t.topic_id = p.topic_id AND p2.topic_id = p.topic_id AND p2.post_id <= $post_id";
$count_sql = ( empty($post_id) ) ? '' : ", COUNT(p2.post_id) AS prev_posts";

$order_sql = ( empty($post_id) ) ? '' : "GROUP BY p.post_id, t.topic_id, t.topic_title, t.topic_status, t.topic_replies, t.topic_time, t.topic_type, t.topic_vote, t.topic_last_post_id, f.forum_name, f.forum_status, f.forum_id, f.auth_view, f.auth_read, f.auth_post, f.auth_reply, f.auth_edit, f.auth_delete, f.auth_sticky, f.auth_announce, f.auth_pollcreate, f.auth_vote, f.auth_attachments ORDER BY p.post_id ASC";

$sql = "SELECT t.topic_id, t.topic_title, t.topic_status, t.topic_replies, t.topic_time, t.topic_type, t.topic_vote, t.topic_last_post_id, f.forum_name, f.forum_status, f.forum_id, f.auth_view, f.auth_read, f.auth_post, f.auth_reply, f.auth_edit, f.auth_delete, f.auth_sticky, f.auth_announce, f.auth_pollcreate, f.auth_vote, f.auth_attachments" . $count_sql . "
        FROM " . TOPICS_TABLE . " t, " . FORUMS_TABLE . " f" . $join_sql_table . "
        WHERE $join_sql
                AND f.forum_id = t.forum_id
                $order_sql";

The PHP:

Code: Select all

if ( !empty($post_id) )
{
        $start = floor(($forum_topic_data['prev_posts'] - 1) / intval($board_config['posts_per_page'])) * intval($board_config['posts_per_page']);
}

However, that's great, but it only works on a topic page. I also want to get this working with the forum index page, forum pages and search function pages.

For example, the search SQL is:

Code: Select all

if ( $search_results != '' )
        {
                if ( $show_results == 'posts' )
                {
                        $sql = "SELECT pt.post_text, pt.bbcode_uid, pt.post_subject, p.*, f.forum_id, f.forum_name, t.*, u.username, u.user_id, u.user_sig, u.user_sig_bbcode_uid
                                FROM " . FORUMS_TABLE . " f, " . TOPICS_TABLE . " t, " . USERS_TABLE . " u, " . POSTS_TABLE . " p, " . POSTS_TEXT_TABLE . " pt
                                WHERE p.post_id IN ($search_results)
                                        AND pt.post_id = p.post_id
                                        AND f.forum_id = p.forum_id
                                        AND p.topic_id = t.topic_id
                                        AND p.poster_id = u.user_id";
                }
                else
                {
                        $sql = "SELECT t.*, f.forum_id, f.forum_name, 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, " . FORUMS_TABLE . " f, " . USERS_TABLE . " u, " . POSTS_TABLE . " p, " . POSTS_TABLE . " p2, " . USERS_TABLE . " u2
                                WHERE t.topic_id IN ($search_results)
                                        AND t.topic_poster = u.user_id
                                        AND f.forum_id = t.forum_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";
                }

Looking at the first bit of SQL posted above, what would I need to add/change to the last SQL code in order to get the above $start variable to work correctly on this page and on other pages?

I'm no PHP expert, and any help is very much appreciated.

Thank you.

[chrishayes]

Basically, what I'm trying to do is to eliminate the duplicate post pages and have links to the correct topic pages (with a $start variable) if the topic spans over 1 page. I have managed to do this in the topic pages (the mini-post icons) by using this code:

Code: Select all

if ($start == 0) {
       $mini_post_url = append_sid("viewtopic.$phpEx?" . POST_TOPIC_URL . "=$topic_id") . '#' . $postrow[$i]['post_id'];
       }
       else {
              $mini_post_url = append_sid("viewtopic.$phpEx?" . POST_TOPIC_URL . "=$topic_id&start=$start") . '#' . $postrow[$i]['post_id'];
       }

However, of course this will not work on post links on forum pages, search pages and index page because the SQL to work the $start out isn't there.

Thank you for your help.

[SeO]

For the index, you can inspire from this :
[request] today-yesterday mod with SEO?

The mods, among other things you may not need, adds the link to the latest topic on the index, and this link is a topic link (kind of like the no dupe does for phpBB3).

For search, I don't know how worth it it would be, and I don't know how hard it would be to adapt, but you should find interesting ideas in this mod http://www.phpbb.com/community/viewtopic.php?t=407141 (I'm not found of the urls produced byt the mod, but there is an add-on which seems to do that, maybe you can inspire from it).

[chrishayes]

Thank you for your reply.

I've looked at the today-yesturday mod, however it does not seem to change the link, only the date. The code that I need to change is as follows in the index.php:

Code: Select all

$last_post .= '<a href="' . append_sid("viewtopic.$phpEx?"  . POST_POST_URL . '=' . $forum_data[$j]['forum_last_post_id']) . '#' . $forum_data[$j]['forum_last_post_id'] . '"><img src="' . $images['icon_latest_reply'] . '" border="0" alt="' . $lang['View_latest_post'] . '" title="' . $lang['View_latest_post'] . '"></a>'

However, I need a function of some kind to workout the correct topic number and $start variable so I can then construct my URLs. For example, I need to change $forum_data[$j]['forum_last_post_id'] to something like $forum_data[$j]['forum_last_topic_id'], however there is nothing to "get" the topic. Any ideas?

Thank you for your help.

[chrishayes]

This is the current SQL in the index.php:

Code: Select all

$sql = "SELECT f.*, p.post_time, p.post_username, u.username, u.user_id
            FROM (( " . FORUMS_TABLE . " f
            LEFT JOIN " . POSTS_TABLE . " p ON p.post_id = f.forum_last_post_id )
            LEFT JOIN " . USERS_TABLE . " u ON u.user_id = p.poster_id )
            ORDER BY f.cat_id, f.forum_order";

What would I need to add/change in order to extract/work out the latest topic number from the latest post number? Of course, we also need to work out how many posts were posted before the post in question to figure out the $start variable. I can do this by dividing the topic number -1 by the maximum posts per page.

[chrishayes]

Does anybody have any idea on how to do this?

[SeO]

But have you tried this ?

For search, I don't know how worth it it would be, and I don't know how hard it would be to adapt, but you should find interesting ideas in this mod http://www.phpbb.com/community/viewtopic.php?t=407141 (I'm not found of the urls produced byt the mod, but there is an add-on which seems to do that, maybe you can inspire from it).

[chrishayes]

You are brilliant! I must have missed that link.

I've adapted the code and just taken the functions that generate the topic page links. It works flawlessly! Thank you

[SeO]

You're welcome

I guess I'm starting to have some phpBB culture to remind about mod's addon on phpBB2 (which I do not work on since phpBB3 was beta released)