0

I am making a site in which I have to show all stores on a list sorted in a way that the nearer stores, to the users current location, come first and far ones come later. I need a MySQL query that selects all stores from database ORDER BY users current position. I have searched on Google a lot but could not find any query that matches my case.

NoNaMe
  • 6,020
  • 30
  • 82
  • 110
Danish Jameel
  • 23
  • 1
  • 5
  • can u show a query that you are using right now.. to get a clear picture ? – itssajan Mar 18 '15 at 05:22
  • Merging these two scripts/APIs together I think would do it. https://developers.google.com/web/fundamentals/device-access/user-location/obtain-location?hl=en#determine-the-users-current-location https://developers.google.com/maps/articles/phpsqlsearch_v3 – chris85 Mar 18 '15 at 05:27
  • I actually don't have a query right now. I am simply going to post users current lat and long to the php file and I want to run a query that brings all stores order by users current location – Danish Jameel Mar 18 '15 at 05:28

3 Answers3

1

You can try this:

SELECT temp1.* FROM (SELECT s.shop_id, s.shop_name, s.address, 3959 * ACOS( COS( RADIANS( 40.7127 ) ) * COS( RADIANS( s.latitude ) ) * COS( RADIANS( s.longitude ) - RADIANS( 74.0059 ) ) + SIN( RADIANS( 40.7127 ) ) * SIN( RADIANS( s.latitude ) ) ) AS distance FROM shop s) as temp1 WHERE temp1.distance <= 20 ORDER BY temp1.distance

it will give shop listing within 20 miles.
40.7127 is latitude and 74.0059 is longitude

Pathik Vejani
  • 4,263
  • 8
  • 57
  • 98
0

I think that you'll find that you'll just need to get all the stores from your database (or potentially filtered by something like state to make for a smaller data-set) and then let your application handle ordering them by distance.

NathanR
  • 196
  • 10
0

You should go with the ip address of the stores with the ip helper to find (host,country,town,region) of the store and map them so that any can find nearby stores. you can find these from ip

<?php
require_once("userip/ip.codehelper.io.php");

require_once("userip/php_fast_cache.php");

$_ip = new ip_codehelper();

$real_client_ip_address = $_ip->getRealIP();
$visitor_location       = $_ip->getLocation($real_client_ip_address);

$guest_ip   = $visitor_location['IP'];
$guest_country = $visitor_location['CountryName'];
$guest_city  = $visitor_location['CityName'];
$guest_state = $visitor_location['RegionName'];

echo "IP Address: ". $guest_ip. "<br/>";
echo "Country: ". $guest_country. "<br/>";
echo "State: ". $guest_state. "<br/>";
echo "City: ". $guest_city. "<br/>";

?>

You can also find

$ip             = $visitor_location['IP'];
$Continent_Code     = $$$visitor_location['ContinentCode'];
$Continent_Name     = $visitor_location['ContinentName'];
$Country_Code2      = $visitor_location['CountryCode2'];
$Country_Code3      = $visitor_location['CountryCode3'];
$Country        = $visitor_location['Country'];
$Country_Name       = $visitor_location['CountryName'];
$State_Name         = $visitor_location['RegionName'];
$City_Name      = $visitor_location['CityName'];
$City_Latitude      = $visitor_location['CityLatitude'];
$City_Longitude     = $visitor_location['CityLongitude'];
$Country_Latitude   = $visitor_location['CountryLatitude'];
$Country_Longitude  = $visitor_location['CountryLongitude'];
$Country_Longitude  = $visitor_location['CountryLongitude'];
$LocalTimeZone      = $visitor_location['LocalTimeZone'];
$Calling_Code       = $visitor_location['CallingCode'];
$Population         = $visitor_location['Population'];
$Area_SqKm      = $visitor_location['AreaSqKm'];
$Capital        = $visitor_location['Capital'];
$Electrical         = $visitor_location['Electrical'];
$Languages      = $visitor_location['Languages'];
$Currency       = $visitor_location['Currency'];
$Flag           = $visitor_location['Currency'];
Uday
  • 9
  • 2