mysql-在分页中查找结果记录页面 [英] mysql - Find page of result record in pagination

问题描述

想象我们使用分页来拆分并显示mysql结果， 按自动确定的ID和日期排序:

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

我们使用php显示该结果和页面下方的页面导航链接.

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

我们如何找到记录ID号x在该结果的哪一页中，以便我们将页码设置为该页并显示该页，而最终用户不需要单击导航并找到它?

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?

推荐答案

首先获取记录总数.

select count(*) as total from members; 

在记录列表中找到行成员"x"的编号

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');

上面的查询从x返回oneLess记录号.即"x"是oneLess + 1

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

现在计算页码.

$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

然后计算$ start

Then calculate $start

$start = $page_no * $len;

这篇关于mysql-在分页中查找结果记录页面的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！