Invalid parameter number on PDO Prepared Statement

Question

I'm working with a sequence of queries created with PDO class, in some case, my queries needs the same parameter. I've created an array used in a foreach statement which save the data but some variables come from outside, can I use both data in one query?

the example:

// $connection is the PDO object;
// $full_data contains:
// $full_data[$i]["address"]
// $full_data[$i]["phone"]
// $full_data[$i]["email"]
// $full_data[$i]["user_id"]
// $full_data[$i]["surname"] // not used but present
// $full_data[$i]["name"] // not used but present

$sql = "UPDATE users_table SET city = :address, phone = :phone, email = :email, admin_id = :admin_id, admin_name = :admin_name WHERE user_id = :user_id";
$statement = $connection->prepare ($sql);


$statement->bindParam (':admin_id', trim($admin_id), PDO::PARAM_INT);
$statement->bindParam (':admin_name', trim($admin_name), PDO::PARAM_STR);


foreach ($full_data as $value) {
    $ok = $statement->execute ($value);
    $num = $statement->rowCount ();
}

} catch (PDOException $e) {
    return $e->getMessage ();
}

this page return me the error: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

what is exactly the problem, on an UPDATE statement the technique works

vitto · Accepted Answer · 2010-01-21T14:50:48.123

damn, I've found the problem after hours...

// $connection is the PDO object;
// $full_data contains:
// $full_data[$i]["address"]
// $full_data[$i]["phone"]
// $full_data[$i]["email"]
// $full_data[$i]["user_id"]
// ==> $full_data[$i]["surname"] // not used but present
// ==> $full_data[$i]["name"] // not used but present

the array data not saved in the query ["surname"] and ["name"] generate the error. It seems like execute (); needs precise array data structure. I've solved the problem by using this:

$sql = "UPDATE users_table SET city = :address, phone = :phone, email = :email, admin_id = :admin_id, admin_name = :admin_name WHERE user_id = :user_id";
$statement = $connection->prepare ($sql);

// must be removed ==> $statement->bindParam (':admin_id', trim($admin_id), PDO::PARAM_INT);
// must be removed ==> $statement->bindParam (':admin_name', trim($admin_name), PDO::PARAM_STR);

for ($i = 0; $i < count($full_data); $i++) {
    $full_data[$i]["admin_name"] = "the admin name";
    $full_data[$i]["admin_id"] = "100";
    unset ($full_data[$i]["surname"]); // IMPORTANT: must remove the unused vars
    unset ($full_data[$i]["name"]); // IMPORTANT: must remove the unused vars
}



foreach ($full_data as $value) {
    // bindParam can be avoided, but it's recommended for data type security
    $statement->bindParam(':address', trim($value['address']), PDO::PARAM_STR);
    $statement->bindParam(':phone', trim($value['phone']), PDO::PARAM_STR);
    $statement->bindParam(':email', trim($value['email']), PDO::PARAM_STR);
    $statement->bindParam(':admin_id', trim($value['admin_id']), PDO::PARAM_INT);
    $statement->bindParam(':admin_name', trim($value['admin_name']), PDO::PARAM_STR);

    $ok = $statement->execute ($value);
    $num = $statement->rowCount ();
}

} catch (PDOException $e) {
    return $e->getMessage ();
}

score 2 · Answer 2 · answered Jan 20 '10 at 21:19

2

You need to bind the :address, :phone, and :email parameters.

answered Jan 20 '10 at 21:19

BD.

880
5
10

sorry, can you help me a little bit more? this explanation doesn't, I've tested personally with this example I can avoid to bind data if it is in array form: http://stackoverflow.com/questions/2066320/basics-rules-on-update-statement-for-relational-mysql-table-for-vs-while" – vitto Jan 20 '10 at 21:29
In that case I believe you need to not use bindparam and instead add :admin_id and :admin_name to your array prior to calling the foreach. – BD. Jan 20 '10 at 21:40

score 2 · Answer 3 · edited May 23 '17 at 12:14

2

To elaborate on BD answer you're missing the following lines of code:

$statement->bindParam (':address', trim($address), PDO::PARAM_STR);
$statement->bindParam (':phone', trim($phone), PDO::PARAM_STR);
$statement->bindParam (':email', trim($email), PDO::PARAM_STR);

Plus, something seems to be wrong with your foreach loop, I think this is what you want:

$sql = "UPDATE users_table SET city = :address, phone = :phone, email = :email, admin_id = :admin_id, admin_name = :admin_name";
$statement = $connection->prepare($sql);

$statement->bindParam(':admin_id', trim($admin_id), PDO::PARAM_INT);
$statement->bindParam(':admin_name', trim($admin_name), PDO::PARAM_STR);

foreach ($full_data as $value)
{
    $statement->bindParam(':address', trim($value['address']), PDO::PARAM_STR);
    $statement->bindParam(':phone', trim($value['phone']), PDO::PARAM_STR);
    $statement->bindParam(':email', trim($value['email']), PDO::PARAM_STR);

    $ok = $statement->execute();
    $num = $statement->rowCount();
}

edited May 23 '17 at 12:14

Community

1
1

answered Jan 20 '10 at 21:34

Alix Axel

151,645
95
393
500

I still have troubles, I have done other queries without use the bindParam inside the foreach loop, as described here, it works http://www.php.net/manual/en/pdostatement.execute.php#71929 – vitto Jan 20 '10 at 23:02
@Vittorio Vittori: I had a typo in my code, try it again. Regarding the comment on PHP.net I wouldn't follow that advice it can potentially lead to some obscure bugs in your code. But I guess the reason that doesn't work is because you cannot use `bindParam()` and `execute()` with addicional params - I'm not sure about this but it kinda makes sense. – Alix Axel Jan 20 '10 at 23:07
@Vittorio Vittori: You can try adding the `$admin_id` and `$admin_name` to the `$_POST` array and avoid calling `bindParam()` if you really want to... You would just have to do `$statement->execute($values)` inside the foreach loop. – Alix Axel Jan 20 '10 at 23:08
thanks for directions, they've been important for me to find the problem – vitto Jan 21 '10 at 00:13
I've solved the problem as explained in my answer corrections, but your help has been important, when you said "something seems to be wrong with your foreach loop". I did not expect it was an array size problem, the unused vars "name" and "surname" were the main issue – vitto Jan 21 '10 at 08:27
@Vittorio Vittori: Oh... Nice catch! ;) – Alix Axel Jan 21 '10 at 14:28

Invalid parameter number on PDO Prepared Statement

3 Answers3

Linked