警告:PDOStatement :: execute():SQLSTATE [HY093]:无效的参数编号:在...文件文本中未定义参数 [英] Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in...filetext

问题描述

$ 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

我尝试了将$ user_id设置为特定值并且不包含纬度/经度参数的PDO准备语句.

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

如果我忘记了任何关键信息，请指出来，我会得到的.地址是varchar，而lat/long是DB btw中的浮点数.使用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.

这篇关于警告:PDOStatement :: execute():SQLSTATE [HY093]:无效的参数编号:在...文件文本中未定义参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！