无法使用PDO更新行 [英] Not able to update rows using PDO
问题描述
当我运行以下代码时:
// Loop through each store and update shopping mall ID
protected function associateShmallToStore($stores, $shmall_id) {
foreach($stores as $store_id) {
$sql .= 'UPDATE my_table SET fk_shmallID = :shmall_id WHERE id = :store_id';
$stmt = $this->db->prepare($sql);
$stmt->bindParam(':shmall_id', $shmall_id);
$stmt->bindParam(':store_id', $store_id);
$stmt->execute();
}
}
我收到以下消息:
Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters
I get the following message:
Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters
我也尝试了以下失败的尝试(没有$stmt->bindParam):
$stmt->execute( array($shmall_id, $store_id));
I've also tried the following without success (without $stmt->bindParam):
$stmt->execute( array($shmall_id, $store_id));
我不明白我在做什么错.
I don't understand what I'm doing wrong.
更新 我已经更新了代码，以反映出源代码中的实际含义.这里不应该有错别字.
UPDATE I've updated my code to reflect what I actually got in my source code. There should not be any typos here.
更新2
我尝试了此操作，但仍然收到相同的错误消息.
UPDATE 2
I tried this, but I still get the same error message.
protected function associateShmallToStore($stores, $shmall_id) {
$i = 0;
$sql .= "UPDATE sl_store ";
foreach($stores as $store_id) {
$i++;
$sql .= 'SET fk_shmallID = :shmall_id, lastUpdated = NOW() WHERE id = :store_id_'.$i.',';
}
$sql = removeLastChar($sql);
$stmt = $this->db->prepare($sql);
$stmt->bindParam(':shmall_id_'.$i, $shmall_id);
$i = 0;
foreach($stores as $store_id) {
$i++;
$stmt->bindParam(':store_id_'.$i, $store_id);
}
$stmt->execute();
}
这是SQL查询的输出:
This is the output of the SQL query:
UPDATE sl_store
SET fk_shmallID = :shmall_id, lastUpdated = NOW() WHERE id = :store_id_1,
SET fk_shmallID = :shmall_id, lastUpdated = NOW() WHERE id = :store_id_2
更新3
我使用的代码是:
UPDATE 3
The code I endet up using was this:
foreach($stores as $store_id) {
$sql = "UPDATE sl_store SET fk_shmallID = :shmall_id WHERE id = :store_id";
$stmt = $this->db->prepare($sql);
$stmt->bindParam(':shmall_id', $shmall_id);
$stmt->bindParam(':store_id', $store_id);
$res = $stmt->execute();
}
推荐答案
就像错误所言，您混合了命名和位置参数:
It's just as the error says, you have mixed named and positional parameters:
-
:name(命名) -
:person_id(命名) -
?(位置)
:name(named):person_id(named)?(positional)
除此之外，您还具有命名参数:person_id，但是您要绑定到:id.
More than that, you have the named parameter :person_id, but you're binding to :id.
这些是您的参数，我将它们称为P1，P2和P3:
These are your parameters, I'll call them P1, P2 and P3:
UPDATE my_table SET name = :name WHERE id = :person_id ?
^ P1 ^ P2 ^ P3
这是绑定它们的地方:
$stmt->bindParam(':name', $name); // bound to P1 (:name)
$stmt->bindParam(':id', $person_id); // bound to nothing (no such param :id)
您可能想将第二个参数绑定到:person_id，而不是:id，并删除最后一个位置参数(查询末尾的问号).
You probably want to bind the second parameter to :person_id, not to :id, and remove the last positional parameter (the question mark at the end of the query).
此外，通过foreach循环的每次迭代都会向查询添加更多内容，因为您使用的是串联运算符而不是赋值运算符:
Also, each iteration through the foreach loop appends more to the query, because you're using the concatenation operator instead of the assignment operator:
$sql .= 'UPDATE my_table SET name = :name WHERE id = :person_id ?';
您可能要删除=之前的..
有关此的更多信息，请查看已准备好的语句并存储PDO手册中的步骤页面.您将了解如何绑定参数，以及命名参数和位置参数之间的区别.
For more about this, take a look at the Prepared statements and stored procedures page in the PDO manual. You will find out how to bind parameters and what the difference is between named and positional parameters.
所以，总结一下:
-
将SQL行替换为:
Replace the SQL line with:
$sql = 'UPDATE my_table SET name = :name WHERE id = :person_id';
将第二个bindParam()调用替换为:
Replace the second bindParam() call with:
$stmt->bindParam(':person_id', $person_id);
这篇关于无法使用PDO更新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
