How to get next/previous record in MySQL?

In addition to cemkalyoncu's solution:

next record:

SELECT * FROM foo WHERE id > 4 ORDER BY id LIMIT 1;

previous record:

SELECT * FROM foo WHERE id < 4 ORDER BY id DESC LIMIT 1;

edit: Since this answer has been getting a few upvotes lately, I really want to stress the comment I made earlier about understanding that a primary key colum is not meant as a column to sort by, because MySQL does not guarantee that higher, auto incremented, values are necessarily added at a later time.

If you don't care about this, and simply need the record with a higher (or lower) id then this will suffice. Just don't use this as a means to determine whether a record is actually added later (or earlier). In stead, consider using a datetime column to sort by, for instance.

next:

select * from foo where id = (select min(id) from foo where id > 4)

previous:

select * from foo where id = (select max(id) from foo where id < 4)