CODEWITHSUNDEEP
phpmysqlpdo
Damon Skelhorn
Damon Skelhorn

Reputation: 1511

PHP PDO bindParam returning NULL

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.

Upvotes: 1

Views: 1019

Answers (2)

Mads
Mads

Reputation: 724

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.

Upvotes: 1

Mārtiņš Briedis
Mārtiņš Briedis

Reputation: 17752

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...

Upvotes: 1

Related Questions