4

$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'];

But I get the error:

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

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

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

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.

The 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;
}
Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
HolyMackerel
  • 157
  • 2
  • 3
  • 12
  • 1
    I can't remember if `execute()` incorrectly handles an associative array if the params are not named (using `?` instead of `:first_name`, for ex). Does it work if you do `$st->execute(array_values($fields));` to strip off the associative keys? – Michael Berkowski Feb 11 '13 at 18:49
  • @MichaelBerkowski: You are correct. When using positional params you must use 0 indexed integer keys. Additionally if you use named params then both the placeholders in the statement and the string keys for the params passed to execute must match and be prefixed with `:`. – prodigitalson Feb 11 '13 at 18:53
  • @prodigitalson I thought that might be the case, but it isn't mentioned [in the docs](http://php.net/manual/en/pdostatement.execute.php) that I have found. – Michael Berkowski Feb 11 '13 at 18:55
  • Yay thanks so much, you were right. I was about to try bindparams to define which values were ints and floats etc. glad I didn´t go down that road. This was my first thread, so how do I give you points now ? :P – HolyMackerel Feb 11 '13 at 18:58
  • FWIW, the pdo_mysql driver ignores the type values you pass to bindParam(). – Bill Karwin Feb 11 '13 at 18:59
  • @user1948698 I don't need them :) Mark Bill's answer below as accepted by clicking the checkmark beside it. That will inform readers of this that your issue has been solved. – Michael Berkowski Feb 11 '13 at 19:01
  • haha you are a noble man sir. Bill´s answer has been checked, and thanks for the answers and explanations. – HolyMackerel Feb 11 '13 at 19:02

1 Answers1

6

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.

Here's a test to confirm 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());
}

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 1
    Very good and thorough answer this one. I settled for just reading through the tests trying to understand the different behaviors, and you make several good points. Some damn competent people on here... – HolyMackerel Feb 11 '13 at 19:11