[RESOLVED] Selecting a range of records either side of a certain record?

[Inf51]

I have a table and when someone searches for a particular record I would like the results to include 3 records either side of that record so for example if someone searches for 'john' which has an id of 15 I'd like it to show records 12 - 18

Now that's easy enough to do by finding the id of 'john' and use > (id-4) and < (id+4) in the query but the problem is some records will be deleted over time so if record 13 is deleted this query would only show 12,14,15,16,17,18 rather than 3 records either side of 15 so in this case I'd want records 11,12,14,15,16,17,18

So in other words I'm always ordering the query by ID but I don't know how I can select the 3 records before a particular record and 3 records after when there may be gaps in the numbering sequence?

[djjjozsi]

try to make UNION

(select * from `table` where id<5 LIMIT 3)
UNION
(select * from `table` where id>=5 LIMIT 4)

[Inf51]

Great - thanks!

I've spent hours scratching my head over this one! :-)

[Inf51]

Worked great!

Just needed to modify the query to get it to show consecutive records in the correct order:

(select * from `table` where id<5 ORDER BY id DESC LIMIT 3)
UNION
(select * from `table` where id>=5 ORDER BY id ASC LIMIT 4)
ORDER BY id ASC;

[djjjozsi]

Nice! And don't forget to set this thread as resolved , please.