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))";
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)";
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments