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

Question

$ fields is an array that after printing receives values ​​such as:

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 as follows:

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

But I get the error:

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

I checked several other similar threads, but I assume that I am missing a basic concept because I still cannot find the answer. Got 7? and 7 elements in my array, as far as I can see, and if I define all the values, I can perfectly implement it in SQL SQL environment, 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 

I tried to prepare a prepared PDO report with both $ user_id set to a specific value and no latitude / longitude parameters.

If I forget any critical information, just indicate it and I will receive it. the address is varchar, and lat / long is a float in DB btw. Using MYSQL.

Function below:

 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; } 

Answer 1

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

Here is a test that confirms the behavior:

 $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()); } 

Please note that in current versions of PHP, associative array keys must not have a prefix : as @prodigitalson comments. Prefix : which was previously required in array keys in older versions of PHP.

It is also worth mentioning that I encountered errors and unpredictable behavior when I tried to mix positional parameters and named parameters in a single query. You can use any style in different requests in your application, but you have chosen one style for a given request.