PDO bindParam无法循环工作 [英] PDO bindParam not working in loop

问题描述

我无法让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无法循环工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！