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.
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;