存储定位器与 SQL

发布于 2024-11-08 11:58:36 字数 1111 浏览 0 评论 0原文

我正在尝试使用谷歌地图 API 为加油站构建一个商店定位器。我已经完成了此处描述的所有操作（http://code.google.com/ apis/maps/articles/phpsqlsearch.html)，但是

搜索地址功能无法识别大部分输入（我可以接受）
Google 提供的 SQL 语句不会返回任何结果（我'已经通过用“SELECT * FORMmarkers”替换语句来检查了这一点，这有效）

我的数据库表看起来像

ID 
NAME
ADDRESS
LAT
LNG

，而我遇到问题的语句是：

$query = sprintf("SELECT name, address, lat, lng ( 6371 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < '%s' ORDER BY distance LIMIT 0 , 5",
  mysql_real_escape_string($center_lat),
  mysql_real_escape_string($center_lng),
  mysql_real_escape_string($center_lat),
  mysql_real_escape_string($radius));
$result = mysql_query($query);

它从地址搜索中获取其值。

有人有这个问题或类似问题的经验吗？

编辑：发现第一个错误：我错过了 lng 和 (6371... 之间的逗号如果我直接将其插入到 phpmyadmin 中（用实际值替换“%s”），我会得到我想要的结果，所以我认为问题是将变量传递到 SQL 中；希望这有助于缩小范围。

原文

I'm trying to build a store locator for gas stations with google maps api.
I've done everything as described here (http://code.google.com/apis/maps/articles/phpsqlsearch.html), but

The search for address function does not recognize most of the input (I can live with that)
The SQL statement as provided by google does not return any results (I've checked that by substituting the statement with "SELECT * FORM markers", which works)

My DB table looks like

ID 
NAME
ADDRESS
LAT
LNG

and the statement I'm having troubles with is:

$query = sprintf("SELECT name, address, lat, lng ( 6371 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < '%s' ORDER BY distance LIMIT 0 , 5",
  mysql_real_escape_string($center_lat),
  mysql_real_escape_string($center_lng),
  mysql_real_escape_string($center_lat),
  mysql_real_escape_string($radius));
$result = mysql_query($query);

which gets its values from the address search.

Does anybody have experience with this or a similar problem?

edit: found the first error: i missed out a comma between lng and (6371...
if I insert this directly in phpmyadmin (substituting '%s' with real values) i get the result i want, so I suppose the problem is passing the variables into SQL;
hope that helps narrowing it down.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

雅心素梦 2024-11-15 11:58:36

您需要传入浮点数而不是字符串，因此您的 php 应该是

$query = sprintf("SELECT name, address, lat, lng, ( 6371 * acos( cos( radians(%f) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(%f) ) + sin( radians(%f) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < %d ORDER BY distance LIMIT 0 , 5",
  mysql_real_escape_string($center_lat),
  mysql_real_escape_string($center_lng),
  mysql_real_escape_string($center_lat),
  mysql_real_escape_string($radius));
$result = mysql_query($query);

You need to pass in floats instead of strings, so your php should be

$query = sprintf("SELECT name, address, lat, lng, ( 6371 * acos( cos( radians(%f) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(%f) ) + sin( radians(%f) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < %d ORDER BY distance LIMIT 0 , 5",
  mysql_real_escape_string($center_lat),
  mysql_real_escape_string($center_lng),
  mysql_real_escape_string($center_lat),
  mysql_real_escape_string($radius));
$result = mysql_query($query);

回复收藏 0 原文

~没有更多了~