How do I select a fixed number of elements around (before, and the remainder after) one specific element in a MySQL table?

I need a query (issued via php) to select n records (let's say 4) inserted around a given record (n/2 before + n/2 after). If the number of rows inserted before the current element is less than n/2, the remaining records (n-result1) should be those inserted AFTER the current record. A similar thing for the newest records. (select more/all previous records)

Let's say current record id is #1, then the query would give records 2,3,4,5 as a result If current record 开发者_JAVA百科is 2, the result would be 1,3,4,5 If current record is 3, the result would be 1,2,4,5 If current record is 6, the result would be 4,5,7,8

Let's say there are 9 elements in the table

If current record is 8, the result would be 5,6,7,9 If current record is last (9), the result would be 5,6,7,8

n would be actually a fixed value, determined via php. The use of php (if helpful in context -- for example if mysql_last_insert() can be used) is more than welcome

Thank you

Selecting the records with and ID preceding and following some specific ID is not that complicates. The key is to order the records by their IDs and then select only those that are smaller than or greater than the specific ID:

SELECT * FROM table WHERE id < x ORDER BY id DESC LIMIT 4;
SELECT * FROM table WHERE id > x ORDER BY id ASC LIMIT 4;

This will give you two record sets with the records preceding x and following x respectively. Then you can union the records depending on the actual size of the record sets to always get at most 4 records.

'SELECT ID
FROM Table
WHERE 
ID > '. ($n - 3) .
' AND ID != '. $n .
' ORDER BY ID ASC LIMIT 4'

This has the advantage of being one query.

If you want to address the deleted rows issue, you'll actually have to do 2 queries