How to insert point data into mysql using PDO bindParam?
gis, mysql, pdo, php
Solution
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)";
Problem
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))"; ```