offset of specific row between selected rows

Question

i'm writnig a very small forum for my website .. i really didn't want to use vb and other forum systems .

so

topics ( title , id )
posts  ( text , id , topic_id )

there is a page that shows all the entry(posts) of given user

in this page for each user post , i show name of the topic and a short summary of that post

here is the problem

when i click on the topic name .. i don't want to go to the first page of topic . i want the exact page that contains that specific post .

so i gont the post id (lets say 666 ) i can get the topic information using that (lets say title : topic A )

so i can get total count of the posts in topic A

lets say

$total_posts_intopic = 250 ;
$per_page            = 15 ;

all i need now is the offset of post #666 to figure out the page number

so how can i get the offset of post #666 between topic A posts ?

or is there any easier way to do this ?

note : post no 666 means it's 666 post overall in all of the topics not in topic A

Tomas Creemers Tomas Creemers · Accepted Answer · 2013-08-21T01:04:00

See this SQL Fiddle for a full demonstration.

The query I used (adapted to your example) is below. The idea is that you fill in the topic_id (where I currently have topic_id = 1) and the post's id (where I currently have id = 666). The result will be a number indicating the position of the given post in the given topic.

To determine the page number, you could use:

$pageNumber = floor(($queryResult - 1) / $postsPerPage);

The query:

SELECT
  currentCount
FROM
  (
    SELECT
      id,
      @counter := @counter + 1 AS currentCount
    FROM
      posts,
      (SELECT @counter := 0) AS countTable
     WHERE
       topic_id = 1
  ) AS associatedCountTable
WHERE
  id = 666;

The query works as follows:

The posts table is cross joined with a counter variable. This works because the variable is set to 0 when MySQL 'creates' countTable (when it looks at the FROM clause) and that variable is incremented by 1 each time a row is selected (because the SELECT clause is 'executed' for each result row).
Because the WHERE clause (selecting the correct topic) is 'executed' before the SELECT clause, only the rows in the topic you're interested in get a counter increase.
The result of the previous steps is a new table associatedCountTable with a row for each post in the selected topic. Each row has the post's id in the first column and a simply incrementing counter in the second column named currentCount.
The final WHERE clause takes the above table and selects the counter column for the row with the id of the post you're interested in (666 in your example).

offset of specific row between selected rows

1 Answers