MySQL 半正矢公式中的语法错误

Question

我在下面的代码中遇到令人沮丧的 MySql 语法错误。实际错误是：

SQLSTATE[42000]：语法错误或访问冲突：1064 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 9 行的“JOIN storetable ON Pricelist.storecode = storetable.storecode JOIN itemlist ON”附近使用的正确语法”

附加信息：此代码基于 Google Maps 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

JOIN 位于错误的位置 - 它们位于 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));