# Using Haversine formula filtering with Mysql and PHP for a search value

Get the solution ↓↓↓I have the following PHP script that filters a received value ($r) with location data associated with using the haversine formula to only show values that are within a certain distance of the point received. The below script is failing for some reason and I cannot tell if it is functionally incorrect or if its as something as simple as a syntax error (scanned through character by character multiple times and can't see anything wrong, but I could be wrong)

The value received is in this form: [search_value]latlong[lat_value]latlong[long_value]

Example:

Item1latlong26.2616236latlong25.6010943

The PHP script is as follows:

```
<?php
include('db.php');
$r = strval($_GET['r']);
$parts = explode('latlong', $r);
$term = $parts[0];
$latitude = $parts[1];
$longitude = $parts[2];
$distance = 1;
mysqli_select_db($mysqli,"db");
$search="SELECT *, (((acos(sin((".$latitude."*pi()/180)) * sin(('lat'*pi()/180)) + cos((".$latitude."*pi()/180)) * cos(('lat'*pi()/180)) * cos(((".$longitude."- 'lng')*pi()/180)))) * 180/pi()) * 60 * 1.1515) as distance FROM 'table_data' WHERE distance <= ".$distance." AND type = '".$term."'";
$result = mysqli_query($mysqli,$search);
echo "<script type='text/javascript'>";
echo "var map;";
echo "var markers = [];";
echo "var locations = [";
while($row = mysqli_fetch_array($result)) {
echo "[" . $row['merchant'] . ".info," . $row['merchant'] . ".lat," . $row['merchant'] . ".lng," . $row['merchant'] . ".type," . $row['merchant'] . ".label," . $row['userid'] . "],";
};
echo "];";
echo "</script>";
mysqli_close($mysqli);
```

In the above 'lat' and 'long' correspond to the appropriate columns in the appropriate table where a list of items is stored, each with their own location data.

Any recommendations on improvements are welcome (I am new to php) and help would be appreciated, thanks!

### Answer

#### Solution:

If it's useful, here's a stored function for calculating distances on Earth in km...

```
DELIMITER $$
CREATE FUNCTION geo_distance_km(lat1 double, lon1 double, lat2 double, lon2 double) RETURNS double
begin
declare R int DEFAULT 6372.8;
declare phi1 double;
declare phi2 double;
declare d_phi double;
declare d_lambda double;
declare a double;
declare c double;
declare d double;
set phi1 = radians(lat1);
set phi2 = radians(lat2);
set d_phi = radians(lat2-lat1);
set d_lambda = radians(lon2-lon1);
set a = sin(d_phi/2) * sin(d_phi/2) +
cos(phi1) * cos(phi2) *
sin(d_lambda/2) * sin(d_lambda/2);
set c = 2 * atan2(sqrt(a), sqrt(1-a));
set d = R * c;
return d;
end$$
DELIMITER ;
```

Example of usage:

```
<?php
/*
CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,userid INT NOT NULL
,merchant INT NOT NULL
,latitude DECIMAL(11, 8) NOT NULL
,longitude DECIMAL(11, 8) NOT NULL
,type VARCHAR(12) NOT NULL
);
INSERT INTO my_table VALUES
(1, 100, 1000, 30.45180,31.236083,'Item1'),
(2, 101, 1000, 26.26162,25.601094,'Item1'),
(3, 101, 1001, 30.36100,31.232080,'Item1'),
(4, 102, 1002, 30.45180,31.236083,'Item1'),
(5, 103, 1003, 30.44473,31.241958,'Item1'),
(6, 103, 1003, 26.26150,25.601000,'Item2'),
(7, 103, 1004, 30.45180,31.236083,'Item2');
*/
require('path/to/pdo/connection/stateme.nts');
$r = 'Item1latlong26.2616236latlong25.6010943';
$parts = explode('latlong', $r);
$term = $parts[0];
$latitude = $parts[1];
$longitude = $parts[2];
$distance = 1;
$query="
SELECT merchant
, userid
, geo_distance_km(:latitude,:longitude,latitude,longitude) distance
FROM my_table
WHERE type = :term
HAVING distance < :distance
";
$stmt = $pdo->prepare($query);
$stmt->bindParam(':latitude', $latitude, PDO::PARAM_INT);
$stmt->bindParam(':longitude', $longitude, PDO::PARAM_INT);
$stmt->bindParam(':distance', $distance, PDO::PARAM_INT);
$stmt->bindParam(':term', $term, PDO::PARAM_STR, 12);
$stmt->execute();
$result = $stmt->fetch();
print_r($result);
?>
```

Outputs:

```
Array ( [merchant] => 1000 [userid] => 101 [distance] => 0.0004015440080907 )
```

**Share solution ↓**

### Additional Information:

**Date the issue was resolved:**

Link To Answer People are also looking for solutions of the problem: xmlhttprequest error flutter

### Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

### Similar questions

Find the answer in similar questions on our website.

### Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.