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
– kojow7, Commented Apr 5, 2018 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
– AbraCadaver, Commented Apr 5, 2018 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
– Rotimi, Commented Apr 5, 2018 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
– IncredibleHat, Commented Apr 5, 2018 at 17:10

IncredibleHat · Accepted Answer · 2018-04-05 17:24:54Z

1

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

edited Apr 5, 2018 at 17:24

answered Apr 5, 2018 at 17:20

IncredibleHat

4,1024 gold badges18 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

kojow7 Over a year ago

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?

IncredibleHat Over a year ago

Correct (for large operations). Its not noticed with a few queries though.

kojow7 Over a year ago

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?

IncredibleHat Over a year ago

You would think it would ... but its enforcing that it should be an int, instead of translating it. Heres a blurb about casting: stackoverflow.com/a/19751442/2960971 (important to read the argument on that answer lol).

Collectives™ on Stack Overflow

PDO bindParam not working in loop

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related