1

I have an issue with PDO binding parameters, the parameters that are set are not being pushed into the query when it is executed on the database. I have witnessed this by getting MySQL to log the queries to a file.

If I execute against the server - I see this in the log file.

SELECT sg.locID, ( 3959 * acos( cos( radians(53.21333) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(-2.4354014) ) + sin( radians(53.21333) ) * sin( radians( latitude ) ) ) ) AS distance FROM location_geo sg HAVING distance < 1000 ORDER BY distance LIMIT 0 , 20

When executed from within PHP/PDO

SELECT
  sg.locID,
  ( 3959 * acos( cos( radians(NULL) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(NULL) ) + sin( radians(NULL) ) * sin( radians( latitude ) ) ) ) AS distance
FROM
  location_geo sg
HAVING
  distance < 1000
ORDER BY distance LIMIT 0, 20

Here is the code thats executed by PHP

$lat = 53.21333;
$lng = -2.4354014;
$limit = 10;
$start = 0;

$query = "
    SELECT
      sg.locID,
      ( 3959 * acos( cos( radians(:latitude) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(:longitude) ) + sin( radians(:latitude) ) * sin( radians( latitude ) ) ) ) AS distance
    FROM
      location_geo sg
    HAVING
      distance < :radius
    ORDER BY distance LIMIT :start, :limit
";

$stm = Modules::db()->prepare($query);
$stm->bindValue(":radius", 1000, PDO::PARAM_INT);
$stm->bindParam(":latitude", $lat, PDO::PARAM_INT);
$stm->bindParam(":longitude", $lng, PDO::PARAM_INT);
$stm->bindParam(":start", $start, PDO::PARAM_INT);
$stm->bindParam(":limit", $limit, PDO::PARAM_INT);
$stm->execute();

Heres my table

CREATE TABLE `location_geo` (
  `locID` int(11) unsigned NOT NULL,
  `latitude` double DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  PRIMARY KEY (`locID`)
)

Dummy data - 

INSERT INTO `location_geo` (`locID`, `latitude`, `longitude`)
VALUES
    (1, 53.21333, -2.4354014),
    (2, 53.213435, -2.4345214);

I have tried replacing the bindParams and putting the data into an array within the execute call however the outcome is the same.

In case you missed it, the values for latitude and longitude remain null. No errors from PDO.

Weird.

Damon Skelhorn
  • 1,491
  • 11
  • 18

2 Answers2

1

Ok, I have never used raw PDO before, but I think the error is here:

$stm->bindParam(":latitude", $lat, PDO::PARAM_INT);
$stm->bindParam(":longitude", $lng, PDO::PARAM_INT);

Why do you say it is a INT (I presume that this PDO::PARAM_INT says what type of value is being passed) , if you pass a FLOAT? Probably that's why it is being converted to NULL, because floating point value is not an integer...

Mārtiņš Briedis
  • 17,396
  • 5
  • 54
  • 76
  • There's no option for float, even setting as PARAM_STR returns the same behaviour. The int option doesn't wrap the value in single quotes as the str option would. However as you point out this could be the problem, I'll investigate around this. Thanks – Damon Skelhorn Apr 21 '12 at 21:41
  • There are some topic about this already that you should look in to: http://stackoverflow.com/questions/1335081/what-is-the-best-way-to-bind-decimal-double-float-values-with-pdo-in-php ..and maybe you could just skip the PDO::PARAM_* parameter and go just with `$stm->bindParam(":latitude", $lat);` for example. – Mārtiņš Briedis Apr 21 '12 at 21:47
  • I have tried without defining the type as suggested in that post, you can see in the query log for mysql it wraps the values in single quotes, the same as PARAM_STR. The values for latitude and longitude remain NULL. I can't see this as being the cause :( – Damon Skelhorn Apr 21 '12 at 22:00
1

For debugging purposes I use setAttribute(). Like this for example:

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

This way it will tell you if you make any errors and what they are.

Mads
  • 724
  • 3
  • 10