PDO bindParam not working in loop

Question

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

    }

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.

Solution:

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];

I had assumed that PDO::PARAM_INT was doing that for me, but it was not.

@IncredibleHat Yes, but binding should be done outside of the loop whereas variables do not exist until in the loop. It's a catch 22. — kojow7, Apr 05 '18 at 17:03
That looks like it should work, if the explode results in an array with at least 3 elements, but try a temp var such as `list($z, $one, $two) = explode(.....)` and use those in bind calls. Also `error_reporting(E_ALL); ini_set('display_errors', '1');` — AbraCadaver, Apr 05 '18 at 17:05
I think you should bind in the loop and execute inside the loop. With what you have, you should be getting an undefined variable as those are only available inside the loop — Rotimi, Apr 05 '18 at 17:09
The problem I've run into, is trying to bindParam (reference) to something like `$array['key']` ... it throws key undefined. And also it is an array item, not a variable. You could get away with just passing execute an array of the three items: `$stmt->execute(array($installed_group['group_id'],$installed_version_parts[1],$installed_version_parts[2]));` and do away with bindParam entirely. — IncredibleHat, Apr 05 '18 at 17:10
@AkintundeOlawale No. He would have to use `bindValue` inside the loop (and the execute MUST be in the loop). — IncredibleHat, Apr 05 '18 at 17:10

IncredibleHat · Accepted Answer · 2018-04-05T17:24:54.720

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.

So three ways:

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

Or: (less efficient)

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

Or:

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

Would I be correct that the second option would be less efficient because you have to re-bind each time and therefore either the 1st or 3rd option would be best? — kojow7, Apr 05 '18 at 17:23
Correct (for large operations). Its not noticed with a few queries though. — IncredibleHat, Apr 05 '18 at 17:24
Trying out the first option it only works if I cast the the $pt1 and $pt2 to an int: `$pt1 = (int)$installed_version_parts[1];` Why is this? Should not the `PDO::PARAM_INT` be taking care of that? — kojow7, Apr 05 '18 at 17:32
You would think it would ... but its enforcing that it should be an int, instead of translating it. Heres a blurb about casting: https://stackoverflow.com/a/19751442/2960971 (important to read the argument on that answer lol). — IncredibleHat, Apr 05 '18 at 17:35

PDO bindParam not working in loop

1 Answers1

Linked

Related