Clever Geek Handbook

How to insert point data into mysql using PDO bindParam?

DETAILS

I would like to use mysql spatial extension, so I am trying to store longitude and latitude in a mysql table of POINT datatype using bindParam. Unfortunately I keep getting SQLSTATE error [23000]: Integrity constraint violation: 1048 The "location" column cannot be null.

I have verified that longitude and latitude are relevant. So the problem must be with my code, but I can't 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 to insert longitude and latitude into mysql table (which uses POINT datatype) with PDO and bindParam?

OPTION

Building on the answer from AgreeOrNot, a slightly different way to achieve this is

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


source to share


2 answers


Note that query parameterization is not a (simple) string replacement. In your code, the query parameter is placed in a string literal that will remain intact.

Try the following:



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


source


Spatial extensions have not yet been developed for latitude and longitude, they are for Cartesian coordinates. Distance functions do not take into account the curvature of the Earth, so they always give incorrect results. It's very sad that I know ...



+1


source






More articles:


All Articles