Using a PHP function while Quering Database

Question

I am doing a project in PHP. I have a Restaurants table with LAT and LONG fields. I want to get 10 Restaurants (with specific cuisine_id) whose distance is less than 5km from the USER. I am using the below function to calculate distance :

// Function for Getting distance between two coordinates.
function getDistance( $latitude1, $longitude1, $latitude2, $longitude2 ) {   
    $earth_radius = 6371;
    $dLat = deg2rad( $latitude2 - $latitude1 );  
    $dLon = deg2rad( $longitude2 - $longitude1 );  

    $a = sin($dLat/2) * sin($dLat/2) + cos(deg2rad($latitude1)) * cos(deg2rad($latitude2)) * sin($dLon/2) * sin($dLon/2);  
    $c = 2 * asin(sqrt($a));  
    $d = $earth_radius * $c;  
    return $d;  
}

Is it possible to use the function while selecting records from database?

Can someone please tell me a sample query for getting (using as LIMIT value ) Restaurants which are at a distance of 5km or less (User LAT and LONG are available) in one go (if possible using above mentioned getDistance function).

You either use it to generate the query mysql runs, or run a mysql query and then use this on the results. mysql cannot run a php function as part of a query — Anigel, Jul 31 '13 at 13:06
"You either use it to generate the query mysql runs" - How can I do that ? Can you show an example since I am new to SQL. — noob, Jul 31 '13 at 13:09
Considering you have not given any details of your database it would be hard to give you an answer — Anigel, Jul 31 '13 at 13:10
If you're doing work with geographic coordinates, you might want to look at the [spatial extensions](http://dev.mysql.com/doc/refman/5.5/en/spatial-extensions.html). — tadman, Jul 31 '13 at 14:45

score 1 · Answer 1 · answered Jul 31 '13 at 13:10

A sql server query and php function execution are two separate things, they cannot be mixed. You either have to do that task in separate steps (so first calculate, then query) or you have to implement your function inside the sql server, not inside php. That is possible, it is called a Stored procedure.

score 1 · Answer 2 · edited May 23 '17 at 10:31

1

this question is somehow similar with mysql-great-circle-distance-haversine-formula

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance 
FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

edited May 23 '17 at 10:31

Community

1
1

answered Jul 31 '13 at 13:21

Anonymous

1,405
4
17
26

score 1 · Accepted Answer · edited May 23 '17 at 10:25

Take a look at this question. I think if you write your function with mysql instead, it will be eaiser to use with queries. I attempted to write a version of your function.. (syntax might not be perfect)

DELIMITER $$

CREATE FUNCTION distance(LAT1 DOUBLE(10,6), LON1 DOUBLE(10,6), LAT2 DOUBLE(10,6), LON2 DOUBLE(10,6))
RETURNS DOUBLE(10,2)
DETERMINISTIC
BEGIN
DECLARE r, dLat, dLon, a, c, d DOUBLE(10,6);
SET r=6371.0;
SET LAT1 = RADIANS(LAT1);
SET LON1 = RADIANS(LON1);
SET LAT2 = RADIANS(LAT2);
SET LON2 = RADIANS(LON2);
SET dLat = LAT2-LAT1;
SET dLon = LON2-LON1;
SET a = SIN(dLat/2.0) * SIN(dLat/2.0) + COS(LAT1) * COS(LAT2) * SIN(dLon/2.0) * SIN(dLon/2.0);
SET c = 2 * ASIN(SQRT(a));
SET d = c * r;
RETURN d;
END
$$

DELIMITER ;

Then, you could do something like this...

$userLatitude = ...
$userLongitude = ...
$cuisine = ...

SELECT cuisine_id, distance(LAT,LONG,$userLatitude,$userLongitude) as Distance FROM Restaurants WHERE cuisine_id = $cuisine HAVING Distance < 5 ORDER BY Distance LIMIT 10;

Using a PHP function while Quering Database

3 Answers3