問題描述

$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.

這篇關于警告:PDOStatement::execute():SQLSTATE[HY093]:無效的參數號:參數未在...文件文本中定義的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！