PDO Prepared Statement上的参数号无效 [英] Invalid parameter number on PDO Prepared Statement

问题描述

我正在使用PDO类创建的一系列查询，在某些情况下，我的查询需要相同的参数。
我创建了一个在foreach语句中使用的数组，它保存数据，但有些变量来自外部，我可以在一个查询中使用这两个数据吗？

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?

示例：

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

此页面返回我的错误：

SQLSTATE [HY093]：参数号无效：绑定变量的数量与令牌数不匹配

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

究竟是什么问题，在一个UPDATE语句上，该技术有效

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

推荐答案

>

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

数组数据不保存在查询中 [surname] 和 [name] 生成错误。
似乎 execute（）; 需要精确的数组数据结构。
我已经解决了这个问题：

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

这篇关于PDO Prepared Statement上的参数号无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！