Post location with pagination

Databases
1

Hello,

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!

2

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

3

Here it is:

SELECT p.post, 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 = u.id)
WHERE [...searchstuff...]
ORDER BY p.time DESC
LIMIT 0, 30
4 
SELECT p.post
     , 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
INNER 
  JOIN forums_topics t
    ON t.topic_id = p.topic_id
INNER 
  JOIN users u
    ON u.id = p.user_id
 WHERE [...searchstuff...]
ORDER 
    BY p.`time` DESC
LIMIT 0,30
5

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

6

nice catch :slight_smile: