CODEWITHSUNDEEP
phpmysqlgeolocation
Ben Sewell
Ben Sewell

Reputation: 1

FInding nearest cities using geolocation

I have MySQL 5.1 and I'm looking to populate a select box with the nearest cities based on IP. I've downloaded Max Mind's GeoLite database but I can't work out how to only populate the cities which are close to the IP. I would also like to "pre select" the nearest city based on IP.

I've written some code but I can only show all the cities in the country I have found. So, can someone show me how to do this?

<?php
$ip=$_SERVER["REMOTE_ADDR"];
$user="user";
$pass="password";

$conn=mysql_connect("localhost:3306",$user,$pass);
$link=mysql_select_db("db");

$ipConv=ip2long($ip);
$sql="SELECT CITY,REGION,LATITUDE,LONGITUDE
FROM location, blocks
WHERE $ipConv 
BETWEEN blocks.startIpNum
AND blocks.endIpNum
AND location.locId = blocks.locid";
$result=mysql_query($sql) or die (mysql_error());
while($row = mysql_fetch_assoc($result))
{
    $tcity=$row['CITY'];
    $tregion=$row['REGION'];
}

$sql2="SELECT COUNTRY FROM location WHERE CITY='$tcity'";
$result2=mysql_query($sql2);
while($row2 = mysql_fetch_assoc($result2))
{
    $country=$row2['COUNTRY'];
}

$sql3="SELECT CITY,REGION FROM location WHERE COUNTRY='$country' GROUP BY REGION";
$result3=mysql_query($sql3);
?>
<form>
<select>
<?php
while ($row3=mysql_fetch_assoc($result3))
{
?>
     <option <?php if ($row3['CITY']==$tcity){ ?> selected="selected"<?php } ?>)>
     <?php echo $row3['CITY']; ?>
     </option><?php
} ?>

</select>
</form>

Thanks

Upvotes: 0

Views: 1740

Answers (1)

Benno
Benno

Reputation: 3008

You need to retrieve the longitude/latitude of the IPs city, instead of city/region, and use that lat/lng in the $sql2 query you have there.

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( $lat ) ) * cos( radians( $lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( $lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

Here's the SQL statement that will find the closest 20 locations that are within a radius of 25 miles to the 37, -122 coordinate. It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, and then asks for only rows where the distance value is less than 25, orders the whole query by distance, and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371.

https://developers.google.com/maps/articles/phpsqlsearch?hl=hu-HU#findnearsql

Upvotes: 3

Related Questions