Mysql - Find pagination result page

Question

imagine that we use pagination to split and display the mysql result, like this, sorted by id and date of automatic discovery:

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

we show links to the result and a link to the page below using php.

how can we find that the record identification number x is on the page of this result, so we set the page number on this page and display this page, and the end user should not click the navigation and find it?

Answer 1

First get the total number of records.

 select count(*) as total from members; 

Find the line member number 'x' which is in the list of entries

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

The above query returns noLess record number from x. that is, "x" is 1 less than + 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; 

Answer 2

@ You have a good solution, but the problem is that your PRIMARY KEY is auto-synchronized, so it is possible that 2 id are not sequential.

You must add a column to place a number that you can control.

After using the program in response to @Bere, but do not use the primary key, use the new column.

Answer 3

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

 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 a page number == 0 for page 1 and a page number == 1 for page 2, etc. ...... Then we can just calculate the OFFSET number with a simple calculation:

 $offset = $myRowSerial * $perPage; 

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

Answer 4

Thank you Fantastic decision :)

  'works' LIKE "%charm%" 

Answer 5

Since Reza Mamun offers his solution, it works fantastically ... I also want to create a SELECT HTML element for navigating between pages. This requires a slightly different approach.

To create a SELECT element, I use an array with the keys 'name' and 'value', so the SQL query looks like this:

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

The query works with filtering by WHERE (user-defined through the form), ORDER BY sorting (selected by used). I think the meaning of variables and properties can be distinguished. This solution is designed for (almost) universal pagination and user-defined page lengths.