CODEWITHSUNDEEP
phpmysql
user3404390
user3404390

Reputation: 97

Filter database query by distance from user

I have a database of events and activities that a user can search. I would like the user to be able to filter, for example only activities and events within 50km of me (lol km #canada). Obviously the distance from each user changes.

I already have a script that calculates the distance based on latitude and longitude and displays it. I cannot figure out how to only display activities and events within x km's.

Not sure what code I should post, but can post whatever is needed. 

//MySQLi results query
$query= "SELECT id,name FROM activity WHERE active =1".$typequery.$eventtypequery.$catquery.$datequery.$keywordsquery." ORDER BY $orderby $order LIMIT $start_from,$perpage";

//Count query
$count = $mysqli->query("SELECT COUNT(*) FROM `activity` WHERE active=1".$typequery.$eventtypequery.$catquery.$datequery.$keywordsquery."");

$row = $count->fetch_row();

$total_records = $row[0];
$total_pages = ceil ($total_records /$perpage);

if (isset($page)&&($total_pages>1)) {$nextpage = $page+1;} else {$nextpage=0;}

if ($page>1) {$prevpage=$page-1;} else {$prevpage=0;}

$result=$mysqli->query($query);

function distance($lat1, $lon1, $lat2, $lon2, $unit) {
$theta = $lon1 - $lon2;
$unit="K";
$dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +  cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta));
  $dist = acos($dist);
  $dist = rad2deg($dist);
  $miles = $dist * 60 * 1.1515;
  $unit = strtoupper($unit);

  if ($unit == "K") {
    return ($miles * 1.609344);
  } else if ($unit == "N") {
      return ($miles * 0.8684);
    } else {
        return $miles;
      }
}

Upvotes: 1

Views: 826

Answers (1)

lauw
lauw

Reputation: 545

This is how i query stuff with a distance, just enter the $LONG and $LAT of your user and you will get the things close to that user (edit the HAVING to however close you want to have it)

//so for example you will have 1 user with 
$lat = <something>;
$long = <something>;

SELECT <some other info you want to select>, 
((ACOS(SIN($lat * PI() / 180) * SIN($lat * PI() / 180) + COS($lat * PI() / 180) * COS($lat * PI() / 180) * COS(($long - <table>.longitude) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance 
FROM <your table>
WHERE <maybe some filters here?>
HAVING distance<=10000
ORDER BY distance = '' DESC, distance ASC

Upvotes: 1

Related Questions