How to insert point data into mysql using PDO bindParam?

DETAILS

I’d like to make use of mysql’s spatial extension, so I am trying to store longitude and latitude in a mysql table of datatype POINT using bindParam.
Unfortunately, I keep getting the error SQLSTATE[23000]: Integrity constraint violation: 1048 Column ‘location’ cannot be null.

I’ve checked that longitude and latitude have values. So the problem has to be with my code, but I cannot see what I am doing wrong.

Here is the code I am using.

$location=$latitude." ".$longitude;
$sql = "INSERT INTO my_geodata SET location = PointFromText('POINT(:location)')";
      //INSERT INTO my_geodata SET location = PointFromText('POINT(-41 12)');    

try 
{
    $stmt = $dbh->prepare($sql);            
    $stmt->bindParam(':location', $location, PDO::PARAM_STR);           
    $stmt->execute();   
    $dbh = null;
}

catch(PDOException $e)
{               
    echo $error=$e->getMessage();
}

QUESTION

What am I doing wrong? How can I insert longitude and latitude into a mysql table (that uses POINT datatype) with PDO and bindParam?

VARIATION

Based on AgreeOrNot’s answer, a slightly different way to achieve this is

$location = 'POINT(' . $latitude . " " . $longitude . ')';    
$sql = "INSERT INTO my_geodata (location) VALUES (PointFromText(:location))";

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Note that parameterizing queries is not (simple) string replacement. In your code your query parameter is put in a string literal which will be kept untouched.

Try this:

$location = 'POINT(' . $latitude . " " . $longitude . ')';
$sql = "INSERT INTO my_geodata SET location = PointFromText(:location)";

Method 2

Spatial extensions aren’t designed for latitude and longitude yet, they are designed for Cartesian coordinates. The distance functions don’t take into account the curvature of the earth so always give wrong results. Quite sad I know…

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0