Nearest ID - PHP / MYSQL Issue

user_12826772 0
Viewed 31

I'm trying to run a query that pulls out the nearest ID to the current, based on another query.

I have a table of suburbs in a city, and I want to display 2 up and 2 down but in the same city my table is this:

id | suburb_name   | region
1  | Caloundra     | Sunshine Coast
2  | Currimundi    | Sunshine Coast
3  | Bribie Island | Morton Bay
4  | Caboolture    | Morton Bay
5  | Golden Beach  | Sunshine Coast

Because I couldn't be bothered to make more examples, Just go 1 up 1 down. We're currently on id 2. If I look up 1 I see Morton Bay which is wrong. I need to see ID 5. Which is the next ID with the same region?

This is completely wrong but this is how far I am off:

SELECT
    slug
    , suburb_name
    , (SELECT location_id FROM rh_suburbs WHERE region = '$sub_region') as suburbs
FROM rh_suburbs
WHERE location_id in ($plus_one, $plus_two, $minus_one, $minus_two)
  AND status = '1'
  AND location_id != '$current'
LIMIT 4
mysql php
1 Answers

With your version of MySQL, this is probably most easily achieved using a UNION of rows before and after the selected location. As a straight SQL query:

(SELECT *
 FROM rh_suburbs
 WHERE region = 'Sunshine Coast'
   AND id < 6
 ORDER BY id DESC
 LIMIT 2)
UNION ALL
(SELECT *
 FROM rh_suburbs
 WHERE region = 'Sunshine Coast'
   AND id > 6
 ORDER BY id ASC
 LIMIT 2)
 ORDER BY id

In this query you would replace 'Sunshine Coast' with '$sub_region' and 6 with $current.

Output (for my slightly expanded demo on SQLFiddle):

id  suburb_name     region
2   Currimundi      Sunshine Coast
5   Golden Beach    Sunshine Coast
7   Currimundi 2    Sunshine Coast
10  Golden Beach 2  Sunshine Coast
user_94737640
Related