问题描述
我无法让bindParam在foreach循环内工作.如果我在循环外使用bindParam或将值硬编码到sql查询中,则一切工作正常.根据此页面,建议改用bindValue.但是,当我使用bindValue时,它说bindValue内部使用的三个变量是未定义的.他们显然在这一点上.我在做什么错了?
I am having trouble getting bindParam to work inside of a foreach loop. If I use bindParam outside of a loop or hardcode the values into the sql query everything works perfectly. According to this page it is suggested to use bindValue instead. However, when I use bindValue it says that the three variables used inside the bindValue are undefined. Which obviously they are at this point. What am I doing wrong?
<?php
$found_update = false;
$installed_groups = array(
array(
"group_id" => 14,
"version" => "1.0.7"
)
);
$sql = "select id from testing_set where group_id = :GROUP_ID
and (
substring_index(substring_index(version, '.', 2), '.', -1) > :INSTALLED_VERSION_NUM_1 OR
substring_index(substring_index(version, '.', 3), '.', -1) > :INSTALLED_VERSION_NUM_2
)
order by created desc limit 1";
try {
$dbh = new PDO("mysql:host=localhost; dbname=".DBNAME, DBUSER, DBPWD);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $dbh->prepare($sql);
$stmt->bindParam(":GROUP_ID", $installed_group['group_id'], PDO::PARAM_INT);
$stmt->bindParam(":INSTALLED_VERSION_NUM_1", $installed_version_parts[1], PDO::PARAM_INT);
$stmt->bindParam(":INSTALLED_VERSION_NUM_2", $installed_version_parts[2], PDO::PARAM_INT);
foreach ($installed_groups as $installed_group){
$installed_version_parts = explode('.', $installed_group['version']);
$stmt->execute();
$data = $stmt->fetch(PDO::FETCH_ASSOC);
if (!empty($data)){
$found_update = true;
break;
}
}
echo "Found: $found_update\n";
}
catch(PDOException $e) {
http_response_code(404);
die();
}
我的预期结果是在终端上显示"Found:1".现在,它应该为true时,其值为false.
My expected results are for it to display "Found: 1" to the terminal. The way it is now it has a value of false when it should be true.
解决方案:
事实证明这里有两个问题.我通过使用基本变量而不是我的bindParam中的数组来遵循IncredibleHat的回答.这有助于解决第一个问题,但另一个问题是我需要将一些数据类型转换为一个int型:
It turns out there were two issues going on here. I have followed IncredibleHat's answer by using basic variables rather than an array in my bindParam. This helped solve the first problem, but the other problem was that I needed to typecast some of data to an int:
$pt1 = (int)$installed_version_parts[1];
我以为PDO::PARAM_INT
在帮我做,但事实并非如此.
I had assumed that PDO::PARAM_INT
was doing that for me, but it was not.
推荐答案
尝试对$array['key']
这样的数组元素进行bindParam
会导致一些问题,因为它绑定了作为引用,但没有绑定.它,只是没有那样做.
Trying to bindParam
to an array element like $array['key']
causes a few issues because its bound as reference, but its not. Its, just not done that way.
所以三种方式:
$stmt = $dbh->prepare($sql);
// bind to variables that can be a reference
$stmt->bindParam(":GROUP_ID", $id, PDO::PARAM_INT);
$stmt->bindParam(":INSTALLED_VERSION_NUM_1", $pt1, PDO::PARAM_INT);
$stmt->bindParam(":INSTALLED_VERSION_NUM_2", $pt2, PDO::PARAM_INT);
foreach ($installed_groups as $installed_group){
$installed_version_parts = explode('.', $installed_group['version']);
// assign the referenced vars their new value before execute
$id = $installed_group['group_id'];
$pt1 = $installed_version_parts[1];
$pt2 = $installed_version_parts[2];
$stmt->execute();
}
或者:(效率较低)
$stmt = $dbh->prepare($sql);
foreach ($installed_groups as $installed_group){
$installed_version_parts = explode('.', $installed_group['version']);
// use bindValue (not bindParam) INSIDE the loop
// bindValue doesn't set them by reference, so any value expression works
$stmt->bindValue(":GROUP_ID", $installed_group['group_id'], PDO::PARAM_INT);
$stmt->bindValue(":INSTALLED_VERSION_NUM_1", $installed_version_parts[1], PDO::PARAM_INT);
$stmt->bindValue(":INSTALLED_VERSION_NUM_2", $installed_version_parts[2], PDO::PARAM_INT);
$stmt->execute();
}
或者:
$stmt = $dbh->prepare($sql);
foreach ($installed_groups as $installed_group){
$installed_version_parts = explode('.', $installed_group['version']);
// pass them on execute directly
$stmt->execute(array(':GROUP_ID'=>$installed_group['group_id'],
':INSTALLED_VERSION_NUM_1'=>$installed_version_parts[1],
':INSTALLED_VERSION_NUM_2'=>$installed_version_parts[2]));
}
这篇关于PDO bindParam无法循环工作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!