CODEWITHSUNDEEP
phpsqlpdo
Vladoski
Vladoski

Reputation: 327

PDOStatement::bindParam doesn't work after the first call

I have a little problem writing a simple class that uses PDO to connect to the db.
The problem is that the PDOStatement::bindParam doesn't work after the first call in the cycle, I mean if I have two parameters to give to the PDOStatement the query doesn't return the rignt result, instead if i give only one parameter it gives the right result.

Here's the code:

public function query($sql, $params = NULL) {
        // Opens the PDO connection
        $this->open();

        $this->stmt = $this->pdo->prepare($sql);

        if (isset($params)) {
            foreach ($params as $key => $value) {
                // This doesn't work after the second cicle.
                $this->stmt->bindParam(':' . $key, $value);
            }
        }

        $result = NULL;
        if (!$this->stmt->execute()) {
            $result = false;
        } else {
            $result = $this->stmt->fetchAll();
        }

        // Closes the PDO connection
        $this->close();

        return $result;
}

And here's the PDOStatement::debugDumpParams: 

SQL: [114]
SELECT 1
FROM   users
WHERE  EXISTS
       (
              SELECT *
              FROM   users
              WHERE  username = :username
              AND    password = :password) limit 1
PARAMS: 2
KEY:NAME: [9] :username paramno=0 NAME=[9] ":username" is_param=1 param_type=2
KEY:NAME: [9] :password paramno=1 NAME=[9] ":password" is_param=1 param_type=2

Thanks for helping!

Upvotes: 0

Views: 60

Answers (1)

Dormilich
Dormilich

Reputation: 927

TL;DR always use bindValue() unless you want to use the special behaviour of bindParam().

foreach ($params as $key => $value) {
    // This doesn't work after the second cicle.
    $this->stmt->bindParam(':' . $key, $value);
}

The reason this doesn't work as intended is the misconception of what PDO means with bindParam(). It doesn't mean "bind the SQL parameter" but "bind the value (i.e. the parameter for bindParam()) as referenced variable". Thus when execute() is called, it will use the value for $value (which is the variable bound to all SQL parameters) at execution time, not at the time bindParam() was called.

Cf. http://php.net/manual/en/pdostatement.bindparam.php where this behaviour is explained.

The solution is to use bindValue() instead of bindParam().

Upvotes: 1

Related Questions