问题描述
如标题所示,我将 MySQL PDO 与准备好的语句一起使用,并且在执行时,我将一个值(最后提供的值)推送到所有字段中.
As titled, I'm using MySQL PDO with a prepared statement, and when executing, I'm getting a single value (the last supplied value) pushed into all fields.
表格看起来像这样:
- id (int - 自动递增)
- a_id (int)
- b_id (int)
插入看起来像这样:
INSERT INTO my_table(a_id, b_id)
VALUES (:a_id, :b_id)
要插入的代码看起来像这样...
Code to insert looks like this...
$stmt = $conn->prepare($sql);
foreach($params as $k => $v) {
$stmt->bindParam(
$k,
$v
);
}
$stmt->execute();
该语句成功插入一个值(这确认 $conn 存在、活动和打开,并且该语句已设置且参数已绑定).
The statement successfully inserts a value (which confirms $conn is present, active and open and the statement is set up and parameters bound).
我已经通过在 foreach 中的调试确认了这些值,在那里我得到了两个参数(在 $params 中):":a_id" => 1、":b_id" => 9999...
I have confirmed the values by debugging inside the foreach, where I get two parameters (in $params) of ":a_id" => 1, ":b_id" => 9999...
不过我的记录是 ({new_id}, 9999, 9999).*
这意味着它看到并使用 ":b_id" => 9999,但似乎删除或覆盖 ":a_id" => 1...
This means it sees and uses ":b_id" => 9999, but appears to drop or overwrite ":a_id" => 1...
我认为下划线可能有问题(但显然有几个人过去曾查看过来源以表明下划线很好......尽管连字符不是).
I thought maybe the underscore was a problem (but apparently several people have reviewed source in the past to indicate underscore is fine... though hyphen is not).
我没有重复使用参数(我的理解是每个准备好的语句允许使用一次)...
I'm not reusing parameters (my understanding is one use allowed per prepared statement)...
这里有我遗漏的命名参数问题吗?由于它基本上是 ->bindParam() 到 ->execute(),所以我没有太多的空间来排除故障.
Is there some named parameter issue I'm missing here? Since it's basically ->bindParam() to ->execute(), there's not a lot of room for me troubleshoot.
我正在尝试解决使用 Fat Free 框架时基于 ORM 的数据访问的速度问题,但我认为那里不会存在干扰.
I'm trying to address speed issues with ORM-based data access while using the Fat Free Framework, but I can't think that there's interference there.
作为参考,这是在 PHP 5.5.8/Windows/IIS 下运行的.
*
我可以确认移动到位置参数正在做同样的事情.
I can confirm that moving to positional parameters is doing the same thing.
INSERT INTO my_table(a_id, b_id)
VALUES (?, ?)
代码更改为...
$stmt = $conn->prepare($sql);
$i = 1;
foreach($params as $value) {
$stmt->bindParam(
$i,
$value
);
$i++;
}
$stmt->execute();
为了阐明 $params 数组是如何设置的...事情正在传递给这段代码(这是抽象数据库处理程序的核心),并且该数组是手动构造的...
To clarify how the $params array is being set... things are being passed through to this code (which is the heart of an abstract db handler), and the array is manually constructed...
即
$results = \XX\DB::do_cmd(
$db,
self::SQL_INSERT,
array(
':a_id' => intval($this->a_id),
':b_id' => intval($this->b_id),
)
);
仍然得到 ({new_id}, 9999, 9999)...
Still got ({new_id}, 9999, 9999)...
注意:为了消除混淆,除了进行基于位置的传递之外,我还对值进行了硬编码以查看我会得到什么...
NOTE: To remove confusion, in addition to going to a positional based pass, I also hardcoded values to see what I'd get...
$results = \XX\DB::do_cmd(
$db,
self::SQL_INSERT,
array(
1,
1234,
)
);
我的记录出来了 ({new_id}, 1234, 1234).同样的问题,不同的值.
My record came out ({new_id}, 1234, 1234). Same problem, differing values.
我感觉这里有一个陷阱"......但我不知道它是什么.
I have the feeling there's a "gotcha" here... but I have no idea what it is.
有趣的是,我在 INSERT 之前根据这两个值(以防止重复)仔细检查表中是否存在现有记录,并且检查正确识别出该记录存在......尽管它在数据库...这意味着 SELECT 和 INSERT 对参数做同样的事情(虽然并不那么令人惊讶,因为参数处理是相同的).
The interesting thing, is that I double check the table for an existing record before the INSERT based on those two values (to prevent duplication) and the check correctly identifies that the record is there... despite it being wrong in the database... which means SELECT and INSERT are doing the same thing with the parameters (though not all that surprising, since parameter handling is the same).
编辑 2:
更新注意已解决.使用...
Update to note as solved. Using...
$stmt->execute($params); // scrap the foreach nonsense...
bindValue() 而不是 bindParam() 也是合适的.
bindValue() rather than bindParam() is also appropriate.
注意:我从以下 PHP 文档(http://php.net/manual/en/pdo.prepared-statements.php) 不区分 bindValue() 和 bindParam()...
NOTE: I was working from the following PHP documentation (http://php.net/manual/en/pdo.prepared-statements.php) which doesn't differentiate bindValue() vs bindParam()...
推荐答案
感谢大家的帮助!
我采用了 Michael 的解决方案,但也测试了 Ryan 的解决方案.
I went with Michael's solution, but tested Ryan's too.
即
更新注意已解决.使用...
Update to note as solved. Using...
$stmt->execute($params); // scrap the foreach nonsense...
bindValue() 而不是 bindParam() 也是合适的.
bindValue() rather than bindParam() is also appropriate.
总结一下,根据 Ryan 的评论,我正在推出一个答案.
To wrap things up, as per Ryan's comment, I'm pushing an answer out.
再次感谢!
这篇关于仅使用最后一个参数的 MySQL PDO 名称-值准备语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!