mysql fetch previous or next record order by anyother field name and not by using order by id

Question

Hi,

I am displaying a list.

The user can edit any item.

I am displaying next previous buttons which when clicked it dynamically fetches the next previous records form db. no page reload.

this is working well with id. like http://stackoverflow.com/questions/3780642/how-can-i-get-next-and-previous-field-id-with-php-from-mysql and http://stackoverflow.com/questions/528806/mysql-next-previous-ids-arbitrary-sort-order

Currently i am displaying like

select * from table order by name asc

and for next and previous i used like this...

select * from table where id > $id order by name asc limit 1
select * from table where id < $id order by name desc limit 1

the above is what i got when i searched the internet.

in this order the id will not be in an order so i cannot use id to fetch next and previous record i think so.

i want to get next and previous records with respect to any other field and not by id.

so some thing like this.

select * from table where id > 5 order by name asc or desc limit 1.

what would be the query?

Note

There are 15000 records. which is half filled by default. the rest half is supposed to be updated by admin. so instead of reloading the page after the admin had updated one record i fetch the other and show dynamically on the screen so that he can continue updating content. there will not any page reload. he just clicks update and the next record appears. he can select previous or next button to navigate between records.

Answer 1

You need to filter on the value of name:

SELECT * FROM `table`
WHERE name < 'Foo'
ORDER by name DESC
LIMIT 1

This only works if the names are unique. If there can be duplicates you need a tie-breaker. You can for example use the id column to tie-break.

SELECT * FROM `table`
WHERE name < 'Foo' OR (name = 'Foo' AND id < 42)
ORDER by name DESC, id DESC
LIMIT 1

Answer 2

You're better doing more tradition pagination.

$itemsPerPage = 10;

$page = (int)$_GET['page'];  // do more validation to check this is a valid integer

$start = ($page * $itemsPerPage) - $itemsPerPage;

$sql = "SELECT * FROM `table`WHERE name < ? ORDER by name DESC limit $start, $itemsPerPage";