在按字母顺序排列搜索词之前和之后选择n行

Question

Dilemma: I have a table with several hundred rows in it. I would like to submit a mysql_query with a specific search term wherein the query finds where the term would be located alphabetically in the column and then returns the n rows before the placement and n rows after the placement.

Example: Imagine that I have a column like the following (I'm placing it horizontally for the sake of space, but for sake of argument, let's pretend that this is a vertical list of column values):

|apple|asus|adder|billy|cat|dog|zebra|computer|mouse|cookie|donkey|

If I were to run the query on the term courage, assuming n = 3, I would like to have it return the following rows in this order:

|cat|computer|cookie|dog|donkey|mouse|

Alphabetically, the word courage would land right in the middle of those results and we are met with the preceding 3 entries and the following 3 entries.

Language Notes: I'm using php and mysql. I don't have any code to display because I'm not sure whether this needs to be in the where clause, or if it requires a subquery, or if you need to do something with the variable in php before handing it to the query.

Answer 1

You might try an UNION of two SELECTS. Or do it manually.

SELECT term FROM table WHERE term < 'courage' ORDER BY term DESC LIMIT 3;

will return cookie, computer and cat (in descending order).

Then SELECT term FROM table WHERE term >= 'courage' ORDER BY term ASC LIMIT 3;

will return dog, donkey and mouse.

In PHP, you get the two sets, reverse the first and join.

A wholly-SQL solution might be

SELECT term FROM (
    SELECT term FROM table WHERE term < 'courage' ORDER BY term DESC LIMIT 3 )
    UNION
    SELECT term FROM table WHERE term >= 'courage' ORDER BY term ASC LIMIT 3 )
) ORDER BY term;