Using PDO to insert variables into SELECT clause?

Question

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.

$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?

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

$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://stackoverflow.com/questions/10113562/pdo-mysql-use-pdoattr-emulate-prepares-or-not )
                 );

What version of PHP are you using? I just tried this with PHP 5.3.15 and it worked fine. It's possible an earlier version of PHP didn't know how to process two occurrences of the same named parameter placeholder. — Bill Karwin, May 29 '13 at 19:10
I just ran the query again to verify this is exactly what happens with the exact code I posted, and it is. I just updated my question to include the PDO options I pass when initializing it. — Kevin Cooper, May 29 '13 at 19:23
Haha thanks, that comment was written by someone else a long time ago. I will look into that. — Kevin Cooper, May 29 '13 at 19:26
why don't you want to bind :lat two times, just to satisfy the library? Say, as `:lat1` and `:lat2` — Your Common Sense, May 29 '13 at 19:27
@YourCommonSense I changed it to $data = array(':lat' => $lat, ':lat' => $lat, ':lon' => $lon); and I get the exact same error. I will try renaming them. — Kevin Cooper, May 29 '13 at 19:30
I installed PHP 5.4.15 and tested again. It works fine if you use `ATTR_EMULATE_PREPARES=>true`, fails if you use `ATTR_EMULATE_PREPARES=>false`. — Bill Karwin, May 29 '13 at 19:36

score 0 · Accepted Answer · answered May 29 '13 at 19:31

0

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

answered May 29 '13 at 19:31

Your Common Sense

156,878
40
214
345

This makes it more confusing to read, because I have 3 different lines calculating different distances. There are about 10 different question marks that all need to be kept track of. This does solve the problem, but seems like there should be a better way... – Kevin Cooper May 29 '13 at 19:32
you can use named placeholders as well. just give them *different* names – Your Common Sense May 29 '13 at 19:33
I know, it just seems like an unnecessary hassle. PDO should be smarter than that! – Kevin Cooper May 29 '13 at 19:34

Using PDO to insert variables into SELECT clause?

1 Answers1