0

When I drag rows from mysql and rolls them out, I divide the total number of rows with eg. 20 so I get number of pages.
But when I look at the details of a row I would like to go back to the same page with a link. (For example, the row with a unique ID 1451 on page 33. <a href="?show=rows&page=33">Back</a>)

How can I figure out what page x would be in mysql for a unique ID?


***UPDATE*
The solution can be found here:
MySQL get row position in ORDER BY

Community
  • 1
  • 1
Diblo Dk
  • 585
  • 10
  • 26
  • Long story short: you can't do it. Reason why you can't do it is because `auto_increment` can have gaps. You can have Ids that are in the following sequence: `1, 2, 3, 1500, 1501, 2900, 2920` - they all fit the 1st page. What might help is having **another** column, like sequence_id that would enumerate your records in a sequential order with step of 1 (every next row has sequence_id incremented by 1). Problem is that you have to maintain it at deletions from the database. – N.B. Dec 28 '11 at 11:00
  • Yes this is the problem and I use ORDER BY column.created. But I found this solution http://stackoverflow.com/questions/3614666/mysql-get-row-position-in-order-by when it occurred to me that ORDER BY was a problem. – Diblo Dk Dec 28 '11 at 12:11

3 Answers3

1

Assuming you display rows ordered by id, query below will return position:

SELECT COUNT(*) position FROM tbl WHERE uid < 1451

With this position you can calculate page using formula: 1 + floor($position / 20). Query is necesary if you have some gaps in data. If you don't have any gaps use formula: 1 + floor(($id - 1) / 20)

Krzysztof
  • 15,900
  • 2
  • 46
  • 76
  • It works way fine, but I use order by column.create and I have done some manually importing with the same date. So I had to think farther out and I fount this http://stackoverflow.com/questions/3614666/mysql-get-row-position-in-order-by – Diblo Dk Dec 28 '11 at 12:09
1

The ceil() function and some division is all you need:

$pagenumber = ceil($id/$resultsperpage);
Gareth
  • 5,693
  • 3
  • 28
  • 37
  • The Problem is as @N.B. say. A following sequence like this `1, 2, 3, 1500, 1501, 2900, 2920` will give an incorrect result. – Diblo Dk Dec 28 '11 at 12:18
0

Divide the ID by 20 and use round() on the result.

Matthias Van Eeghem
  • 319
  • 2
  • 7
  • 1
    Round won't return valid page number. Eg. `round(1/20) == 0 // page 0`, `round(10/20) == 1 // page 1` while both should be on page 1 – Krzysztof Dec 28 '11 at 10:36
  • 1
    ID 1 would give page 0 using round(). You need `ceil()` instead. – Gareth Dec 28 '11 at 10:40