1

How to check if data exists or not in database using mysqli and PHP reference by latitude and longitude?

test table

______________________________________________________________
|__id__|__langtitude_coolumn_name__|__lattitude_coolumn_name_|
|___1__|_______37.802924___________|______-98.448061_________|
|___2__|_______37.798518___________|______-98.459890_________|
|___3__|_______37.797262___________|______-98.454006_________|

I use this code for check data exist or not in database using mysqli and PHP reference by latitude and longitude less than 100 km from my location latitude = 37.799773 and longitude = -98.460878

But I not have any result, how can I do?

<?PHP
$lattitude_value= "37.799773";
$langtitude_value = "-98.460878";
$stmt = $db_mysqli->prepare("SELECT U.* FROM table_name WHERE st_distance_sphere( POINT(U.langtitude_coolumn_name, U.lattitude_coolumn_name), POINT(?, ?)) <= 100000");
$stmt->bind_param("ss", $langtitude_value, $lattitude_value);
$stmt->execute();
$arr = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
if(!$arr)
{
    echo "not have data";
}
else
{
    echo "get data";
}
?>
Cœur
  • 37,241
  • 25
  • 195
  • 267
tempori
  • 45
  • 5
  • 3
    Don't bind strings if you really want to pass numbers. – Peter Apr 10 '18 at 14:22
  • Try running the query in a MySQL client. Then you will know if the problem is with the query, or with the PHP code. – jjok Apr 10 '18 at 14:23
  • 2
    Possible duplicate of [MySQL Great Circle Distance (Haversine formula)](https://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula) – Stefan Crain Apr 10 '18 at 14:23
  • 2
    Also, there is no table named U in your query. – Peter Apr 10 '18 at 14:23

1 Answers1

0

Try this, it will give you the Id, Latitude, Longitude of the GPS co-ordinate pairs and the distance in Kilometers of all the matched Co-ordinates within 100km.

Combined HTML / PHP / MySQL:

   $host = 'localhost';
   $dbname = 'dbname';
   $user = 'dbusername';
   $pass = 'password';

   $conn = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
   $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   $conn->exec("SET CHARACTER SET utf8");

   $sql = "SELECT
                id,latitude,longitude, (
                  6371 * acos (
                  cos ( radians(37.799773) )
                  * cos( radians( latitude ) )
                  * cos( radians( longitude ) - radians(-98.460878) )
                  + sin ( radians(37.799773) )
                  * sin( radians( latitude ) )
                )
            ) AS distance
            FROM table_name
            HAVING distance < 100
            ORDER BY distance";

   $sqlprep = $conn->prepare($sql);

   if($sqlprep->execute()) {
       while($row = $sqlprep->fetch(PDO::FETCH_OBJ)){
             echo "Id: ".$row->id." Latitude: ".$row->latitude." Longitude: ".$row->longitude." Distance: ".$row->distance."<br>\n";
       }
   }
Grant
  • 2,413
  • 2
  • 30
  • 41