-1

I want to create a link to a specific forum post. To do this, I need to calculate which page within the thread the post is on since I am using pagination. How can I calculate which page a message will be on based on the number of results per page?

I use the following query to find the total number of messages in the thread:

SELECT
  COUNT(message_id) AS total_messages, thread_id
FROM forum_messages
WHERE thread_id = 1
tadman
  • 208,517
  • 23
  • 234
  • 262
ShoeLace1291
  • 4,551
  • 12
  • 45
  • 81
  • 1
    in which order will they come? You didn't specify that, so you cannot know. – Jeff Sep 16 '17 at 21:07
  • You're using pagination, but you have no clue as to how that works? Weird. – KIKO Software Sep 16 '17 at 21:11
  • (A) What does this have to do with linking to a specific record? (B) For what you're asking, it's impossible to know. All this code does is tell you how many matching records you have. It doesn't tell you anything *about* those records, what order they're in, etc. – David Sep 16 '17 at 21:11
  • see this answer for how to get the position (an increment number) of a row in a select: https://stackoverflow.com/questions/13566695/select-increment-counter-in-mysql Might be helpfull – Jeff Sep 16 '17 at 21:16

2 Answers2

1

You need to number the rows based on the order used to paginate them, and then just calculate the page based on the page size:

-- supposing a page size of 10 and pagination order by message_id
select message_id, ceiling(message_number/10) as page
from (
  select message_id, @row_number:=@row_number+1 AS message_number
  from forum_messages, (select @row_number:=0) AS row_number
  order by message_id
) as numbered_messages
where message_id=<the id of the message>

These blog post explain the trick used for simulating ROW_NUMBER() in MySQL:

MySQL – Generating Row Number for Each Row Using Variable

Alberto Martinez
  • 2,620
  • 4
  • 25
  • 28
0

In general, page number doesn't depend on the number of records, but the number of records per page:

page_no = (message_position DIV records_per_page) + 1

You can translate this to any meaningful only if the message_position is consecutive in a thread_id. If not, you can't calculate page_no.

Jacques Amar
  • 1,803
  • 1
  • 10
  • 12