未知的异常'PDOException'与PHP 5.2上的SQLSTATE [42000]而不是PHP 5.4中的

Question

为什么下面的语法在PHP 5.4中运行，但不在PHP 5.2中运行？

$stmt = $this->pdo->prepare('SELECT *, COALESCE((6371 * acos(cos(radians(:lat_params)) * 
cos(radians(tbl_restaurants_restaurants.lat)) *          
cos(radians(tbl_restaurants_restaurants.lon) - radians(:lon_params)) + 
sin(radians(:lat_params1)) * 
sin(radians(tbl_restaurants_restaurants.lat)))), 0) AS 
distance FROM 
tbl_restaurants_restaurants WHERE tbl_restaurants_restaurants.is_deleted = 0 
ORDER BY distance ASC LIMIT 0, :max'); 

$stmt->execute(array('lat_params' => $lat, 'lon_params' => $lon, 'lat_params1' => $lat, 'max' => $max)); 
    return $stmt; 

我有一个错误，这是由于PHP 5.2的兼容性。在PHP 5.4中，这个脚本正常工作。

这link重现相同的错误。

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''20'' at line 10' in D:...\ControllerRest.php:56 Stack trace: #0 D:...\ControllerRest.php(56): PDOStatement->execute(Array) #1 D:...\get_data.php(42): ControllerRest->getRestaurantsNearbyResultsAtCount('-20.290190', '-40.293366', '20') #2 {main} thrown in D:...\ControllerRest.php on line 56

是否有任何替代此脚本运行在PHP 5.2？

== UPDATE ==

shivanshu patel's answer did did！但现在错误是由以下SQL语句生成的：

$stmt = $this->pdo->prepare("SELECT COALESCE((6371 * acos(cos(radians(:lat_params)) * cos(radians('tbl_restaurants_restaurants'.'lat')) * cos(radians('tbl_restaurants_restaurants'.'lon') - radians(:lon_params)) + sin(radians(:lat_params1)) * sin(radians('tbl_restaurants_restaurants'.'lat')))), 0) AS distance FROM 'tbl_restaurants_restaurants' ORDER BY distance DESC LIMIT 0, :default_to_find_distance"); 

$stmt->execute(array('lat_params' => $lat, 'lon_params' => $lon, 'lat_params1' => $lat, 'default_to_find_distance' => $default_to_find_distance)); 

Answer 1

尝试用``覆盖列和表，并确保max是int。

<?php 
$stmt = $this->pdo->prepare('SELECT *, COALESCE((6371 * acos(cos(radians(:lat_params)) * 
cos(radians(`tbl_restaurants_restaurants`.`lat`)) * 
cos(radians(`tbl_restaurants_restaurants`.`lon`) - radians(:lon_params)) + 
sin(radians(:lat_params1)) * 
sin(radians(`tbl_restaurants_restaurants`.`lat`)))), 0) AS 
distance FROM 
`tbl_restaurants_restaurants` WHERE `tbl_restaurants_restaurants`.`is_deleted` = 0 
ORDER BY distance ASC LIMIT 0, :max'); 

$stmt->bindValue(':max', $max, PDO::PARAM_INT); 
$stmt->execute(array('lat_params' => $lat, 'lon_params' => $lon, 'lat_params1' => $lat)); 
return $stmt;