时间戳

首页 > 解决方案 > PHP PDO query not inserting - Error HY093

php - PHP PDO query not inserting - Error HY093

问题描述

After a lot of searching the web, the times I see this error, it looks really scenario specific. So far, I haven't found one that matched my scenario. I think my issue is coming from a prepared statement with spatial data type params.

The way I'm executing my code is:

$sql = $conn->prepare("INSERT INTO states(`name`, `poly`) VALUES(':name',GeomFromText('GEOMETRYCOLLECTION(:coords)'));");
$res = $sql->execute(['name'=>$name, 'coords'=>$coords]);

if($res){
    echo "... Successfully Inserted<br><br>";
}
else{
    echo "... Failed<br><br>";
    print_r($sql->errorInfo());
    echo "<br><br>";
}

The above is failing. The connection to the database has been tested. Since these are rather large geometry sets, instead of pasting my code, I'll show how I verified my SQL:

Dumping a raw SQL file and copy/pasting the SQL into a phpMyAdmin window, everything inserted just fine.

$sqlStr = "INSERT INTO states(`name`, `poly`) VALUES('$name',GeomFromText('GEOMETRYCOLLECTION($coords)'));";
$check = file_put_contents('./states/'.$name.'2.sql', $sqlStr);

So it's because of this, that I believe my sql is correct, but it my problem is likely due to the prepare/execute portion somehow. I'm not sure if spatial data types can't be assigned like this?

Edit

I also want to note that I am on PHP version 5.5.9 and I've executed queries in the original method, with the params in the execute just fine.

标签： phpmysqlpdo

解决方案

最后的代码不可能工作。查询中的参数不得放在引号内。

由于GEOMETRYCOLLECTION(:coords)必须在字符串中，因此您需要使用CONCAT()来创建此字符串。

$sql = $conn->prepare("
    INSERT INTO states(`name`, `poly`) 
    VALUES(:name,GeomFromText(CONCAT('GEOMETRYCOLLECTION(', :coords, ')')));");

推荐阅读