Post location with pagination


I have two tables, topics and posts. Each post belongs to only one topic. There are 30 posts per page on a topic (paginated).

I am developing a search feature for my custom-programmed forum and I’ve tweaked the search query to return valuable results. I’m pleased with it. However, what’s difficult is linking to the post itself with the correct pagination (i.e. /forums/topic/1/60, where 1 is the topic ID and 60 is the pagination variable for that topic). Just because the search returns a post ID 12 that’s located in topic ID 10 doesn’t necessarily mean that post will be found on /forums/topic/10… it might be found on a paginated page like /forums/topic/10/90.

What I am wondering is… how can I efficiently calculate each individual post’s “sequence number” in its respective topic (sorted by time ASC) in my search query? If I know a post’s sequence number in the topic, I can certainly find what paginated page it’ll be located on. Basically, my search results need to return where a post is actually located in a topic (on what paginated page).

All of my attempts to efficiently do this have failed thus far. Any thoughts? Thanks in advanced!

count the number of other posts which have an earlier time

if you show your query, maybe i can figure out where to put this count

Here it is:

SELECT, t.topic_id, t.topic_title, u.username, u.session_active 
FROM forums_posts p
INNER JOIN forums_topics t
	ON (p.topic_id = t.topic_id)
INNER JOIN users u
	ON (p.user_id =
WHERE [...searchstuff...]
LIMIT 0, 30
     , t.topic_id
     , t.topic_title
     , u.username
     , u.session_active 
     ,[COLOR="#0000FF"] ( SELECT COUNT(*) + 1
           FROM forums_posts
          WHERE `time` < p.`time` ) AS rank[/COLOR]
  FROM forums_posts p
  JOIN forums_topics t
    ON t.topic_id = p.topic_id
  JOIN users u
    ON = p.user_id
 WHERE [...searchstuff...]
    BY p.`time` DESC
LIMIT 0,30

Perfect! Thank you. I had to change the subquery WHERE to WHERE time < p.time AND topic_id = t.topic_id.

nice catch :slight_smile: