问题描述
我有一个 mysql php pdo 语句,它有单引号来调用 Mysql GeolocationPOINT"函数.
I have a mysql php pdo statement that has single quotes to call the Mysql Geolocation "POINT" function as such.
$sql = "INSERT INTO userTrip
(userId, fromLat, fromLon, fromLoc, fromPOI,
toLat, toLon, toLoc, toPOI,
tripFinished, isMatched, departureTime, createdAt)
values
(:user,:fromLat,:fromLon, GeomFromText('POINT(:fromLat1 :fromLon1)'),:fromPOI,:toLat,
:toLon, GeomFromText('POINT(:toLat1 :toLon1)'),:toPOI,0,0,:departureTime,:date)";
$stmt = $db->prepare($sql);
$stmt->bindParam(':user', $userId, PDO::PARAM_INT);
$stmt->bindParam(':fromLat', $fromLat, PDO::PARAM_STR);
$stmt->bindParam(':fromLon', $fromLon, PDO::PARAM_STR);
$stmt->bindParam(":fromLat1", $fromLat, PDO::PARAM_STR);
$stmt->bindParam(':fromLon1', $fromLon, PDO::PARAM_STR);
$stmt->bindParam(':fromPOI', $fromPOI, PDO::PARAM_STR);
$stmt->bindParam(':toLat', $toLat, PDO::PARAM_STR);
$stmt->bindParam(':toLon', $toLon, PDO::PARAM_STR);
$stmt->bindParam(':toLat1', $toLat, PDO::PARAM_STR);
$stmt->bindParam(':toLon1', $toLon, PDO::PARAM_STR);
$stmt->bindParam(':toPOI', $toPOI, PDO::PARAM_STR);
$stmt->bindParam(':departureTime', $departureTime, PDO::PARAM_STR);
$stmt->bindParam(':date', date('Y-m-d H:i:s'), PDO::PARAM_STR);
当我执行查询时,它会抛出异常PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens",即使参数的数量是正确的.我怀疑单引号会抛出查询,但我需要将它们放入.我尝试使用反斜杠和其他所有我能想到的方法来转义它们,但查询不会执行.有没有办法解决这个问题?
when I execute the query it throws an exception "PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens" even though the number of parameters are correct. I suspect the single quotes throw off the query but I need to put them in. I tried escaping them using backslash and everything else I could think of but the query wont execute. Is there a way around this?
推荐答案
您不需要引号.您需要将参数传递给函数.这就是准备好的语句的用途.
You don't need quotes. You need to pass a parameter into function. That's what prepared statements are for.
先定义一个值
$point = "POINT($fromLat $fromLon)";
然后以通常的方式准备查询
then prepare your query usual way
..., GeomFromText(:point), ...
然后将此 $point 变量绑定到 :point 占位符.
then bind this $point variable to :point placeholder.
这篇关于带单引号的 PHP PDO sql 语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!