Nick - 2 years ago 67

MySQL Question

Making a dealer locator where people search for a dealer near them. I want it to work in such a way that if a dealer has multiple branches near the person, only the closest branch shows. So the "name" field should be unique in the results with the result shown having the least distance to the person searching versus other rows that have the same "name" field. I also want only the closest 5 dealers shown ordered by their dealer level, or medal. Right now I have the following:

`$query = sprintf("SELECT`

name, address, contact, image, medal, phone, email, website, lat, lng,

( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance

FROM dealers

HAVING distance < 60

ORDER BY medal, distance

LIMIT 0 , 5",

mysql_real_escape_string($center_lat),

mysql_real_escape_string($center_lng),

mysql_real_escape_string($center_lat),

mysql_real_escape_string($radius));

$result = mysql_query($query);

From what I've read, it sounds like I need to do something like:

`SELECT * FROM (`

SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY distance) AS num

FROM dealers)a

WHERE a.num = 1

Or something like that, but I can't get it to work right. Any insights on how I can get this to function would be greatly appreciated.

Recommended for you: Get network issues from **WhatsUp Gold**. **Not end users.**

Answer Source

Final Solution was:

```
SELECT name, address, contact, image, medal, phone, email, website, lat, lng,
( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance
FROM dealers
WHERE (name,
( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) )
IN
(SELECT name, distance
FROM (SELECT name,
MIN( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance
FROM dealers
WHERE (3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) < 90
GROUP BY name)
t)
ORDER BY medal, distance
LIMIT 0, 5",
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lng),
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lng),
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lng),
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lng),
mysql_real_escape_string($center_lat)
```

Thank you Thorsten Kettner, for putting me on the right track!

Recommended from our users: **Dynamic Network Monitoring from WhatsUp Gold from IPSwitch**. ** Free Download**