mysql - Find page of result record in pagination

问题:

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?

回答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;

回答2:

@Bere have a nice solution but the problem is that your PRIMARY KEY is autoinc one so it is possible that 2 id are not consecutive ones.

You should add a column to put a number that you can control.

After use the program in the response of @Bere but don't use the primary key, use the new column.

回答3:

First of all, we need to determine the page number where the desired row is:

SELECT d.myRowSerial, FLOOR((d.myRowSerial-1)/10) AS pageNumber
-- Say, 10 is per page;
FROM (
    SELECT *, @rownum:=@rownum + 1 AS myRowSerial 
    FROM myTable, (SELECT @rownum:=0) AS nothingButSetInitialValue 
    WHERE 1=1 -- Optional: filter if required, otherwise, omit this line;
    ORDER BY AnyColumn -- Apply the order you like; 
) d
WHERE d.myColumn = 'Anything'; -- If you like to limit it to only
-- for any specific row(s), similar to the *MAIN query.

You will have pageNumber==0 for page 1, and pageNumber==1 for page 2 and so on..... Then we can simply calculate the OFFSET number with a simple calculation:

$offset = $myRowSerial * $perPage;

Now we can use this $offset value for our MAIN query.

回答4:

As Reza Mamun offer his solution, it works fantastic... I want also create SELECT HTML element to navigate between pages. This require bit another approach.

For creating SELECT element I use array with 'name' and 'value' keys, so SQL query looks that :

 $qry='
SELECT d.RowNumber, FLOOR( ( d.RowNumber ) / %s ) AS value, d.name 
    FROM( 
            SELECT  %s AS ID, 
                        %s AS name,
                        @rownum:=@rownum+1 AS RowNumber 
                FROM    %s, 
                        ( SELECT @rownum:=-1 ) AS Initialize 
                %s 
                %s 
         ) AS d
    WHERE d.Rownumber MOD %s = 0';


$sqry   = sprintf( $qry, $pagelength, $this->_tableindex, $sort_field, $this->_tablename, $cond, $sql_order, $pagelength );

query works with filtering by WHERE ( defined by user via form ), sorting ORDER BY (chosed by used ). I think the meaning of the variables and properties can be distinguished. This solution is intended for (almost) universal data pagging and user-defined page length.