使用PDO将变量插入SELECT子句？

I am attempting to get the distance from a user to each venue stored in a MySQL database, using the spherical law of cosines. The user inputs their location, and the following query is executed.

我试图通过使用球面的余弦定律来获取用户到存储在MySQL数据库中的每个场所的距离。用户输入其位置,并执行以下查询。

$data = array(':lat' => $lat, ':lon' => $lon);

$qry = "SELECT ACOS(SIN(v.Latitude) * SIN(:lat) + COS(v.Latitude) * COS(:lat) * COS(:lon - v.Longitude)) * 3963 AS distance FROM Venue v";

$stmt = $pdo->prepare($qry);
$stmt->execute($data);
$rows = $stmt->fetchAll();

The problem is, I get the following error.

问题是,我得到以下错误。

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number'

When I remove the variables (:lat and :lon) from the SELECT clause, it works just fine. Other variables further on in the statement (not shown here) work just fine, it is only the variables in the SELECT clause that cause an issue. Is this inability to use PDO variables within SELECT clauses a limitation of PDO, or is there a way around this issue?

当我从SELECT子句中删除变量(:lat和:lon)时,它可以正常工作。语句中的其他变量(此处未显示)工作正常,只有SELECT子句中的变量才会导致问题。是否无法在SELECT子句中使用PDO变量限制PDO,或者是否有解决此问题的方法?

I am using PHP 5.4.15, and my PDO options are as follows.

我使用PHP 5.4.15,我的PDO选项如下。

$options = array(PDO::MYSQL_ATTR_INIT_COMMAND   => 'SET NAMES utf8',        // UTF-8 to prevent issue sending special characters with JSON
                 PDO::ATTR_ERRMODE              => PDO::ERRMODE_EXCEPTION,  // fire exceptions for errors (turn this off for release)
                 PDO::ATTR_DEFAULT_FETCH_MODE   => PDO::FETCH_ASSOC,        // only return results indexed by column name
                 PDO::ATTR_EMULATE_PREPARES     => false                    // actually prepare statements, not pseudo-prepare ( http://*.com/questions/10113562/pdo-mysql-use-pdoattr-emulate-prepares-or-not )
                 );

1 个解决方案

#1

$data = array($lat, $lat, $lon);

$qry = "SELECT ACOS(SIN(v.Latitude) * SIN(?) + COS(v.Latitude) * COS(?) * COS(? - v.Longitude)) * 3963 AS distance FROM Venue v";

$stmt = $pdo->prepare($qry);
$stmt->execute($data);
$rows = $stmt->fetchAll();