mysql - Find page of result record in pagination

Question

imagine that we use pagination to split and display a mysql result like this, sorted on auto inceremental ID and date:

SELECT name FROM members ORDER BY id DESC, date DESC LIMIT $start, $len

we display that result and page navigation links below it using php.

how we can find that record ID number x is in which page of that result so we set page number to that page and display that page and end-user do not need to click navigation and find it?

Answer 1

First get total number of records.

select count(*) as total from members; 

Find the number of the row member 'x' is found in the record list

select count(*) oneLess from members where id < (select id from members where name='x');

The above query returns oneLess the record number from x. i.e. 'x' is oneLess+1

Now calculate the page number.

$asc_page_no =  floor((($oneLess+1)/$total)*$len);
$total_pages = floor($total/$len);
$page_no = $total_pages - $asc_page_no; //reverse the page looking direction

Then calculate $start

$start = $page_no * $len;