PHP MySQL Alphabetical Linking

Question

I have a huge table full of records and with PHP I'm making a seperate page for each one. On the page I want to interlink them alphatbetically, for example the page 'Dunn' would have links like this in the sidebar:

• Darren
• Dave
• Doom
• Dunn
• Dupp
• Duss
• Dutt

Always with 3 links either side going up and down the alphabet. All the data is going to be inserted into the table at once and the only way I can think of doing this is by setting a number upon insertion of where it will be in the alphabet relative to the other data.

This seems complicated and I'm wondering if there was an easier or simply better solution? I've seen this done on quite a few sites before so hopefully I can find one :) Any help would be appreciated.

Answer 1

You need to do two queries:

1. Get 3 records that preceed Dunn:

   SELECT `name`
   FROM `table`
   WHERE `name` < "Dunn"
   ORDER BY `name` DESC
   LIMIT 3
   

2. Get 3 records that follow Dunn:

   SELECT `name`
   FROM `table`
   WHERE `name` > "Dunn"
   ORDER BY `name` ASC
   LIMIT 3
   

Answer 2

Expanding on Gumbo's answer: You can do it in one query if you so wish, by using UNION.

(
SELECT  `name` 
FROM  `table` 
WHERE  `name` <  "Dunn"
ORDER BY  `name` DESC 
LIMIT 3
)
UNION ALL (

SELECT  `name` 
FROM  `table`
WHERE  `name` =  "Dunn"
LIMIT 1
)
UNION ALL (

SELECT  `name` 
FROM  `table` 
WHERE  `name` >  "Dunn"
ORDER BY  `name` ASC
LIMIT 3
)

Thus giving a table with all 7 required entries.

Answer 3

If your name field is a unique index, the first two answers work fine. But if you have more than one 'Dunn,' for instance, you'll have to write a little more SQL than that.

Let's say the 'Dunn' we want is record 123. I assume we'd already know this when the page is requested, because generally to be more precise we search for 'Dunn' by record_id and not name (myscript.php?id=123, rather than myscript.php?name=Dunn). We can then do this to collect the 3 above and 3 below:

SELECT name FROM table WHERE name <= 'Dunn' AND record_id <> 123 ORDER BY name ASC LIMIT 3

And...

SELECT name FROM table WHERE name >= 'Dunn' AND record_id <> 123 ORDER BY name ASC LIMIT 3