警告:PDOStatement::execute():SQLSTATE[HY093]:无效的参数号:参数未在...文件文本中定义

2021-12-26 00:00:00 php mysql pdo prepare

$fields 是一个数组，在打印后得到如下值:

$fields is an array that after printing gets values like:

Array ( [first_name] => Nisse [last_name] => Example [ssn] => 198306205053 [address] =>           Stockholm, Sverige [phone_number] => 54654987321546 [latitude] => 55.717089999999999 [longitude] => 13.235379 )

我像这样从我的数据类调用更新函数:

I call the update function from my dataclass like so:

DataManager::update_user($fields, $user_data['id'];

但我收到错误:

警告:PDOStatement::execute():SQLSTATE[HY093]:无效的参数号:参数未在...文件文本中定义

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in...filetext

我已经检查了其他几个类似的线程，但我想我在这里遗漏了一些基本概念，因为我仍然找不到答案.据我所知，我的数组中有 7 个 ? 和 7 个项目，如果我定义了所有值，我可以在 SQL 工作台中完美地运行它，即:

I have checked several other similar threads but I guess I´m missing some basic concept here because I still can´t find the answer. There are 7 ?'s and 7 items in my array as far as I can see, and if I define all the values I can run it perfectly in SQL workbench, i.e.:

UPDATE users SET first_name = 'Kalle', last_name = 'Anka', ssn = 242345234, address = 'Stockholm', phone_number = 53423434, latitude = 17.189889231223423423424324234, longitude = 109.234234 WHERE id = 4

我已经尝试了 PDO 准备好的语句，其中 $user_id 设置为特定值并且没有纬度/经度参数.

I have tried the PDO prepared statement both with the $user_id set to a specific value and also without the latitude/longitude parameters.

如果我忘记了任何重要信息，请指出，我会得到.address 是 varchar 并且 lat/long 是 DB 中的浮点数.使用 MYSQL.

If I have forgotten any critical information just point it out and I will get it. address is varchar and lat/long are floats in the DB btw. Using MYSQL.

函数如下:

public static function update_user($fields, $user_id)
{
    $db = self::_connect();

    $st = $db->prepare("UPDATE users SET first_name = ?, last_name = ?, ssn = ?, address = ?, phone_number = ?, latitude = ?, longitude = ? WHERE id = '{$user_id}'");
    $st->execute($fields);

    return ($st->rowCount()) ? true : false;
}

推荐答案

如果使用位置参数，则传递给 execute() 的参数数组必须是序数数组.同样，如果使用命名参数，则数组必须是关联数组.

If you use positional parameters, the array of parameters you pass to execute() must be an ordinal array. Likewise, if you use named parameters, the array must be an associative array.

这是确认行为的测试:

$stmt = $db->prepare("SELECT ?, ? ,?");

$params = array( 'a', 'b', 'c' );
// OK
if ($stmt->execute($params)) {
  print_r($stmt->fetchAll());
}

$params = array( 'A'=>'abc', 'B'=>'def', 'C'=>'ghi' );
// ERROR!
if ($stmt->execute($params)) {
  print_r($stmt->fetchAll());
}

$stmt = $db->prepare("SELECT :A, :B, :C");

$params = array( 'a', 'b', 'c' );
// ERROR!
if ($stmt->execute($params)) {
  print_r($stmt->fetchAll());
}

$params = array( 'A'=>'abc', 'B'=>'def', 'C'=>'ghi' );
// OK
if ($stmt->execute($params)) {
  print_r($stmt->fetchAll());
}

请注意，在当前版本的 PHP 中，关联数组键不必以 : 为前缀作为@prodigitalson 注释.: 前缀在旧版 PHP 中的数组键中是必需的.

Note that in current versions of PHP, the associative array keys don't have to be prefixed with : as @prodigitalson comments. The : prefix used to be required in array keys in older versions of PHP.

还值得一提的是，当我尝试在单个查询中混合位置参数和命名参数时，我遇到了错误和不可预测的行为.您可以在应用的不同查询中使用任一样式，但为给定查询选择一种样式或另一种样式.

It's also worth mentioning that I've encountered bugs and unpredictable behavior when I tried to mix positional parameters and named parameters in a single query. You can use either style in different queries in your app, but chose one style or another for a given query.

相关文章