問題描述

詳情

我想利用 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?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！