mysql通过id寻找字段名,mysql通过其他任何字段名而不是通过id使用order来获取上一个或下一个记录顺序...

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.

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.

解决方案

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