如何使用PDO bindParam将点数据插入mysql? [英] How to insert point data into mysql using PDO bindParam?
问题描述
详细信息
我想利用mysql的空间扩展,因此我试图使用bindParam将经度和纬度存储在数据类型为POINT的mysql表中. 不幸的是,我不断收到错误SQLSTATE [23000]:违反完整性约束:1048列位置"不能为空.
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();
}
问题
我做错了什么?如何使用PDO和bindParam将经度和纬度插入到mysql表(使用POINT数据类型)中?
What am I doing wrong? How can I insert longitude and latitude into a mysql table (that uses POINT datatype) with PDO and bindParam?
变量
根据AgreeOrNot的回答,实现此目的的方法略有不同
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.
尝试一下:
$location = 'POINT(' . $latitude . " " . $longitude . ')';
$sql = "INSERT INTO my_geodata SET location = PointFromText(:location)";
这篇关于如何使用PDO bindParam将点数据插入mysql?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!