Haversine公式中的MySQL句法误差

Question

下面的代码中出现了一个令人沮丧的MySql语法错误。实际错误是：

SQLSTATE42000:语法错误或访问冲突: 1064您的SQL语法出现了错误；请检查与MySQL服务器版本对应的手册，以获得在第9行使用“pricelist.storecode = storetable.storecode连接项表”附近的正确语法。

附加信息:此代码基于Google、Php/MySql示例：http://code.google.com/apis/maps/articles/phpsqlsearch.html

在尝试添加JOIN语句之前，我能够让这段代码正常工作。

$query = sprintf("SELECT storetable.storeaddress, 
                         storetable.storename, 
                         storetable.lat, 
                         storetable.lng,
                         ( 3959 * acos( cos( radians('%s') ) * 
                         cos( radians( storetable.lat ) ) *                              cos(radians(storetable.lng)  - radians('%s') ) + 
                         sin( radians('%s') ) * sin( radians( storetable.lat ) ) ) ) AS distance 
                    FROM pricelist 
                  HAVING distance < 25 
                ORDER BY distance 
                   LIMIT 0 , 20,
                    JOIN storetable ON pricelist.storecode = storetable.storecode
                    JOIN itemlist ON pricelist.upccode = itemlist.upccode",
                 mysql_real_escape_string($latitude),
                 mysql_real_escape_string($longitude),
                 mysql_real_escape_string($latitude));

Answer 1

联接出现在错误的位置-它们出现在FROM子句之后，WHERE子句之前-使用：

$query = sprintf("SELECT storetable.storeaddress, 
                         storetable.storename, 
                         storetable.lat, 
                         storetable.lng,
                         ( 3959 * acos( cos( radians('%s') ) * 
                         cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + 
                         sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance 
                    FROM pricelist 
                    JOIN storetable ON pricelist.storecode = storetable.storecode
                    JOIN itemlist ON pricelist.upccode = itemlist.upccode
                  HAVING distance < 25 
                ORDER BY distance 
                   LIMIT 0, 20",
                 mysql_real_escape_string($latitude),
                 mysql_real_escape_string($longitude),
                 mysql_real_escape_string($latitude));