Finding closest number to given x and given y in the database.

[cody4camp]

I am designing a mmorpg guild site, and decided to make a coordinate database. A user will enter their own coords, x and y, and the 10 closest coords to them will be displayed.

 

I need help doing this. My table is:

 

id

coordx

coordy

alliance

region

 

I am using $_GET to retrieve the user's input. so

$_GET['mex']

$_GET['mey']

 

Thanks in advance for your help.

 

[requinix]

Forgotten geometry class already?

distance = sqrt((x1 - x2) ^ 2 + (y1 - y2) ^ 2)

Sort on that value.

[cody4camp]

Excuse me for my ignorance, but how will I sort out the lowest ones closest to my number?

[BlueSkyIS]

i would include that formula in a MySQL query, sorting by distance ascending, limit something more than 10 in case there are multiples at the same distance. i don't know mysql square root syntax without a google search...

[BlueSkyIS]

pseudo-query (after validating/escaping get values):

 

SELECT id, alliance, region, ABS(sqrt(($_GET['mex'] - coordx) ^ 2 + ($_GET['mey'] - coordy) ^ 2)) AS distance FROM some_table ORDER BY distance

 

you'll probably want to use an absolute value, as negative distances will be encountered.

[cody4camp]

Well, luckily all the coords are positive. I'll try yours.

[cody4camp]

Thanks a million! Solved!

[cody4camp]

Oops, not solved. The distance is getting returned the same for each coord.

[BlueSkyIS]

post code?

[cody4camp]

$result=dbquery("SELECT alliance, region, coordx, coordy, ABS(sqrt(($playerx - coordx) ^ 2 + ($playery - coordy) ^ 2)) AS distance FROM ".DB_COORDFUSION." ORDER BY distance LIMIT 25");

 

Also, the distance shows up in the table aswell. here is what it returns:4294967296