dongxun2903 2013-06-13 05:15
浏览 76
已采纳

将Haversine公式与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.

  • 写回答

2条回答 默认 最新

  • douqin7086 2013-06-13 10:21
    关注

    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
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 数学的三元一次方程求解
  • ¥20 iqoo11 如何下载安装工程模式
  • ¥15 本题的答案是不是有问题
  • ¥15 关于#r语言#的问题:(svydesign)为什么在一个大的数据集中抽取了一个小数据集
  • ¥15 C++使用Gunplot
  • ¥15 这个电路是如何实现路灯控制器的,原理是什么,怎么求解灯亮起后熄灭的时间如图?
  • ¥15 matlab数字图像处理频率域滤波
  • ¥15 在abaqus做了二维正交切削模型,给刀具添加了超声振动条件后输出切削力为什么比普通切削增大这么多
  • ¥15 ELGamal和paillier计算效率谁快?
  • ¥15 蓝桥杯单片机第十三届第一场,整点继电器吸合,5s后断开出现了问题