CODEWITHSUNDEEP
phpphp-5.4php-5.2
afazolo
afazolo

Reputation: 382

Uncaught exception 'PDOException' with 'SQLSTATE[42000] on PHP 5.2 but not in PHP 5.4

Why does the syntax below run in PHP 5.4 but not in 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;

I am having an error which is supposed due to the PHP 5.2 compatibility. In PHP 5.4 this script works normally.

This link reproduces the same error.

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

Is there any alternative to this script that runs on PHP 5.2?

==UPDATE==

shivanshu patel's answer did works! But now that error is generate by the following SQL statement:

$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) );

Upvotes: 0

Views: 74

Answers (1)

shivanshu patel
shivanshu patel

Reputation: 778

Try to cover columns and tables with `` and make sure max is in 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;

Upvotes: 1

Related Questions