John Davidson

Message:

The function I want to implement is to get data from the DB and sort the locations of users and stores in order of distance based on the latitude and longitude received from the user.
The part I've worked on so far has been to sort the stores in index order and display them on the screen, and to get the user's coordinates.
However, there was a problem in which the coordinates received from the user were sent to php, and the distance was calculated and sorted based on the data received from the db.
This is the php code I wrote in the beginning that did not cause any errors, and was successful in sorting the stores in index order and displaying them on the screen. The empty part in $con = mysqli_connect(""); was filled in at that time.

<?php 

    //echo "hello world!";

error_reporting(E_ALL);



ini_set("display_errors", 1);



    $con = mysqli_connect(""); 

        mysqli_set_charset($con,"utf8"); 

    

        $res = mysqli_query($con, "select * from TABLE"); 

    



        $place = array(); 





        while($row = mysqli_fetch_row($res)) { 

            array_push($place, array('name'=>$row[1],'latitude'=>$row[2],'longitude'=>$row[3])); } 

            echo json_encode(array("place"=>$place),JSON_UNESCAPED_UNICODE); 

        

        mysqli_close($con); 

    

?>

This is the java code inside android studio that i wrote to get the coordinates and send them to php. The php address is replaced with an appropriate address each time it is run.

double user_latitude = gpsTracker.getLatitude();

double user_longitude = gpsTracker.getLongitude();

String storeURL = "phpaddress?user_latitude=" + user_latitude + "&user_longitude=" + user_longitude;

And the php code I modified to get this from php and sort it by distance is as follows.

<?php 

    //echo "hello world!";

error_reporting(E_ALL);



ini_set("display_errors", 1); 

    $con = mysqli_connect("“); // 

        mysqli_set_charset($con,"utf8"); 



$user_latitude=$_GET['user_latitude'];

$user_longitude=$_GET['user_longitude'];



$sql = "SELECT (

            (6371*acos(cos(radians(" . $user_latitude . "))*cos(radians(latitude))

            *cos(radians(longitude)-radians(" . $user_logitude . "))

            +sin(radians(" . $user_latitude . "))*sin(radians(latitude))))

        AS distance

        FROM

            TABLE

        ORDER BY

            distance

        DESC";

 



    $res = mysqli_query($con, $sql); 



    $place = array(); 





        while($row = mysqli_fetch_row($res)) { 

            array_push($place, array('name'=>$row[1],'latitude'=>$row[2],'longitude'=>$row[3])); } 



        if( (($_SERVER['REQUEST_METHOD'] == 'POST') && isset($_POST['submit'])) || $android )

{





    echo json_encode(array("place"=>$place),JSON_UNESCAPED_UNICODE); 

        

 

        mysqli_close($con); 

    

?>

I am a beginner in coding, so there are a lot of inexperienced parts in the code that I googled and put together crudely.
What I had a question about while applying the code was,

SELECT (

(6371*acos(cos(radians(" . $user_latitude . "))*cos(radians(latitude))

*cos(radians(longitude)-radians(" . $user_logitude . "))

+sin(radians(" . $user_latitude . "))*sin(radians(latitude))))

     AS distance

Here, it was whether the latitude variable was receiving the latitude variable of the db that I set. I was connected to the db, but it seems that the column value of the table I wanted was omitted from there. Also, there is no such variable in the column of my table for the variable called distance, so I was wondering if it is right to put codes like AS distance, order by distance.
I'm not trying to teach you this little bit of knowledge, but I'm asking if it has anything to do with not being able to implement it.

---

My Answer:
It looks like there are a few issues in your PHP code that may be causing the problem.

1. In your SQL query, you are calculating the distance between the user's location and the locations in the database table, but you are not selecting any columns from the table. You need to select the columns that you want to display in the result set along with the calculated distance. For example, if you want to select the name, latitude, and longitude columns along with the distance, your query should look like this:

sql
SELECT name, latitude, longitude, (
(6371*acos(cos(radians($user_latitude))*cos(radians(latitude))
*cos(radians(longitude)-radians($user_longitude))
+sin(radians($user_latitude))*sin(radians(latitude))))
AS distance
FROM TABLE
ORDER BY distance DESC


2. In your PHP code, you are trying to access the latitude and longitude values from the database table using `$row[1]`, `$row[2]`, and `$row[3]`, but these indexes may not be correct based on the columns selected in your SQL query. Make sure to use the correct indexes to access the values.

3. It seems like there is a typo in your PHP code where you are trying to access the longitude value. You have `$user_logitude` instead of `$user_longitude`.

4. Make sure that the column names in your database table match the column names used in your SQL query. If they do not match, you will need to adjust the column names in your query accordingly.

By addressing these issues, you should be able to calculate the distance between the user's location and the locations in the database table and sort the results by distance.