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

Question

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

your distance function needs to return a value witch you then add to the query — , Jul 08 '15 at 00:31
distance returns the distance between the two points. Should i put the rest of the function on there? — coal175, Jul 08 '15 at 00:32
i think this is just a pp string concatenation issue `$fooo='text'.my_function_output().'more text';` my_function_output() needs to return the string you want inside $foo — , Jul 08 '15 at 00:33
possible duplicate of [a php function inside a mysql query?](http://stackoverflow.com/q/4750891) (No, you can't. You could craft a stored procedure perhaps. Otherwise do a roundtrip. Note that SQLite can call back PHP functions easily, because it runs in the same process.) — mario, Jul 08 '15 at 00:34

score 1 · Answer 1 · answered Jun 08 '16 at 12:37

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

score 0 · Answer 2 · answered Jul 08 '15 at 00:37

0

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.

answered Jul 08 '15 at 00:37

Ghaith J.

5
1

But I need each locations longitude and latitude. That way I only get the locations that are in the search radius. – coal175 Jul 08 '15 at 00:50

score 0 · Answer 3 · answered Jul 08 '15 at 00:43

0

Try this..

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

answered Jul 08 '15 at 00:43

Malik Naik

1,472
14
16

Still no luck. Thanks though. – coal175 Jul 08 '15 at 00:51
Yea just added the distance method – coal175 Jul 08 '15 at 01:00
Use semicolon after return $miles `return $miles;` – Malik Naik Jul 08 '15 at 01:17
The semicolon is there I just miss copied it. – coal175 Jul 08 '15 at 13:42
When i run that string in an echo to see if it is making the right string it outputs right but when i try to do it inside my query it doesn't work. – coal175 Jul 08 '15 at 14:22

score 0 · Answer 4 · edited May 23 '17 at 12:16

0

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.

edited May 23 '17 at 12:16

Community

1
1

answered Jul 08 '15 at 14:49

tomsv

7,207
6
55
88

I am sorry I am very new to all this. Which of these two options would probably be easier for a beginner to figure out. Also what do you mean by 'inline' your distance calculation? – coal175 Jul 08 '15 at 15:24
The easiest to do for a beginner would be the second alternative where you would create an SQL query similar to the one in the linked answer. – tomsv Jul 08 '15 at 15:48
Oh gotcha. So basically do the calculation and assign it with AS. So in my php file where I do the SELECT statement can I use my variables liks &longitude and &latitude of will it be a problem because I pass it as a string to mysql_query – coal175 Jul 09 '15 at 19:32
I used a version of the linked answer and I can now find the distance away all the locations are but how do I use this information to only pull the once that are within the radius. I read somewhere that the calculation I did in SELECT to find 'distance_in_km' is not calculated until after WHERE is run, so I cannot say WHERE distance_in_km < $radius – coal175 Jul 13 '15 at 01:40

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

4 Answers4