CODEWITHSUNDEEP
phpmysqlselectwhere-clause
coal175
coal175

Reputation: 273

Is it possible to call a function in my SELECT statement in php?

I have a database which holds different locations, each of which has its own longitude and latitude variable. I want to be able to use a distance function I made that returns the distance between two longitudes and latitudes in my WHERE statement. So I am looking for the distance between two points and it pass the WHERE statement if it is less than the radius I am searching.

distance function:

function distance($lat1, $lon1, $lat2, $lon2) {
    $theta = $lon1 - $lon2;
    $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;
    return $miles;
}

inside my SELECT statement I want something like this:

SELECT * FROM Locations WHERE distance($latitude, $longitude, 'latitude', 'longitude')

however that is not working. I have tried these without any luck...

"SELECT * FROM Locations WHERE distance($latitude, $longitude, 'latitude', 'longitude') < $radius"

and

sprintf("SELECT * FROM Locations WHERE %f < $radius", distance($latitude, $longitude, 'latitude', 'longitude'))

Is this even possible to preform? If not what is a good way around this problem? Thanks!

EDIT: I tried to do 

"SELECT * FROM Locations WHERE '" . distance('Latitude', 'Longitude', $latitude, $longitude) . "' < $radius"

but it didn't work however if I just echo it it outputs the result I am looking for. But it doesn't work right when I try to use it inside mysqli_query

Upvotes: 1

Views: 3954

Answers (4)

Jigisha Prajapati
Jigisha Prajapati

Reputation: 36

I have use this same but one issue: I have used in query as follow:

///////////////////////////////////////////////////
$sql = "select ... AND '".distance2($lat,$long,'tbl_parkinglocations.lat',`tbl_parkinglocations.lat`) ."'<=20 ";
///////////////////////////////////////////////////

but in function:

function distance2($lat1,$long1,$lat2,$long2){
            //Google api

            echo $fromAddress=$lat1.','.$long1;
            echo $toAddress=$lat2.','.$long2;
                // echo "https://maps.googleapis.com/maps/api/distancematrix/json?origins=$fromAddress&destinations=$toAddress&sensor=false";
            $URL = json_decode(file_get_contents("https://maps.googleapis.com/maps/api/distancematrix/json?origins=$fromAddress&destinations=$toAddress&sensor=false"));

            echo "<br>".$URL; exit;

            $status = $URL->rows[0]->elements[0]->status;
            if($status != 'ZERO_RESULTS'){
                    $res = round($URL->rows[0]->elements[0]->distance->value/1000);
                     return $res;
            }
        }

it gets 3rd and 4th param as follow: 3rd->tbl_parkinglocations.lat 4th->tbl_parkinglocations.lat

Upvotes: 1

tomsv
tomsv

Reputation: 7277

No you cannot do that.

You could create a stored procedure that can calculate the distance of a location from the specified coordinate, and then use that stored procedure in your SQL query.

Or you could inline your distance calculation in the SQL query for example as shown here in this linked answer.

Upvotes: 0

Malik Naik
Malik Naik

Reputation: 1502

Try this..

$sql = "SELECT * FROM Locations WHERE " . distance($latitude, $longitude, 'latitude', 'longitude') . " < $radius";

Upvotes: 0

Ghaith J.
Ghaith J.

Reputation: 5

The easy way to do it is to store your function's result in a variable and then you can add it into your select query.

Upvotes: 0

Related Questions