eht eht - 1 month ago 20
MySQL Question

Incorporating Haversine formula with PHP PDO

Originally using the following query:

SELECT username, latitude, longitude, ( 3959 * acos( cos( radians(':lat') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(':lng') ) + sin( radians(':lat') ) * sin( radians( latitude ) ) ) ) AS distance FROM users HAVING distance < '500' ORDER BY distance LIMIT 0 , 20


This works great when I run it in console (replacing my PDO placeholders (:lat, :lng, etc) with actual numbers) but as soon as I run it through PHP-PDO ie:

$stmt = db::getInstance()->prepare($sql);
$stmt->bindParam(':lat', $lat, PDO::PARAM_STR);
$stmt->bindParam(':lng', $lng, PDO::PARAM_STR);
$stmt->bindParam(':radius', $radius, PDO::PARAM_STR);
$stmt->execute();
$result = $stmt->fetchAll();


It has issues and returns nothing.

The issue was that PDO was processing the HAVING differently than a raw query, apparently

So that's fine, I have rewritten the query replacing the HAVING and the new query looks like this:

SELECT * FROM ( SELECT username, latitude, longitude, ( 3959 * acos( cos( radians(':lat') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(':lng') ) + sin( radians(':lat') ) * sin( radians( latitude ) ) ) ) ) WHERE distance < '500' ORDER BY distance LIMIT 0 , 20


This results in the following error from PDO:


exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error
or access violation: 1248 Every derived table must have its own alias'
in
/home/bessr/local_html/site.com/pieces/functions/geo.functions.php:47
Stack trace: #0
/home/bessr/local_html/site.com/pieces/functions/geo.functions.php(47):
PDOStatement->execute() #1 {main}


Alright, so I give it an alias and my altered query looks like this:

SELECT * FROM ( SELECT username, latitude, longitude, ( 3959 * acos( cos( radians(':lat') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(':lng') ) + sin( radians(':lat') ) * sin( radians( latitude ) ) ) AS t ) AS alias ) WHERE distance < '500' ORDER BY distance LIMIT 0 , 20


Even though I added an alias I'm still getting the same error. I'm not sure exactly where I'm going wrong. Any help appreciated.

Answer

You are quoting the named place holders in query. Also you are not using :radius.The following query removing the quotes and adding :radius works with my database.

SELECT username, latitude, longitude,
( 3959 * acos( cos( radians(:lat) ) * cos( radians( latitude ) ) 
* cos( radians( longitude ) - radians(:lng) ) + sin( radians(:lat) )
* sin( radians( latitude ) ) ) ) AS distance FROM users
HAVING distance < :radius ORDER BY distance LIMIT 0 , 20
Comments