sql question?

Assuming you know you want to retrieve a specific record number, then getting the adjacent items is simple if you use UNION (I am assuming you are using MySQL):

$idvalue = 27

SELECT * FROM table WHERE id = $idvalue

UNION

SELECT * FROM table WHERE id < $idvalue ORDER BY id DESC LIMIT 1

UNION

SELECT * FROM table WHERE id > $idvalue ORDER BY id ASC LIMIT 1

This query shouldn't be affected at all by adding or deleting records.

Provide a little more details for us please - will there only every be those 3 records (rows) or are you needing to do this more generically? In other words for some reason you need to be able to look up any given "name" and then also find the record immediately before & after it?

If you simply need *all* of the records, just do:

SELECT id, name FROM my_table

Not sure how to do it more generically with SQL. Could you at least count on the ID of the preceding record to be less than the known one and the one after it to be larger? If this is the case you could try this series of queries:

SELECT id FROM my_table WHERE name = 'moose';

store the returned id in a variable ($id)

To get the previous record do:

SELECT max(id), name FROM my_table WHERE id < $id;

To get the next record do

SELECT min(id), name FROM my_table WHERE id > $id;

The above should at least work assuming that the preceding record you're after has the _next_ smallest id (it wouldn't matter if it was 1 smaller or 13 smaller) . Same for the following record, it's just assumed to be the next larger id.

If you must have it in a single SQL query, you could use sub-queries or UNION - the exact details will depend on which database (MySQL, PostgreSQL, Oracle, MS SQL, SQLite ...)

This should work, but it is a little clumsy

select top 1 id, name from myTable

where id < (select id from myTable where name = 'moose')

order by id desc

union

select id, name from myTable where name = 'moose'

union

select top 1 id, name from myTable

where id > (select id from myTable where name = 'moose')

order by id asc

If you're using PHP + MySQL, you can do it in 2 queries.

First one:

$result = mysql_query("SELECT * FROM `tableName` WHERE `name` = 'moose' LIMIT 1")

and then

$id1 = $result['id']--;

$id2 = $result['id'];

$id3 = $result['id']++;

$result2 = mysql_query("SELECT * FROM `tableName` WHERE `id` IN($id1,$id2,$id3)");

I might have made some mistakes in the syntax, but the idea should be clear.