438

What is the difference between PDOStatement::bindParam() and PDOStatement::bindValue()?

Improve this question
0

7 Answers 7

Reset to default
728

From the manual entry for PDOStatement::bindParam:

[With bindParam] Unlike PDOStatement::bindValue(), the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called.

So, for example:

$sex = 'male'; $s = $dbh->prepare('SELECT name FROM students WHERE sex = :sex'); $s->bindParam(':sex', $sex); // use bindParam to bind the variable $sex = 'female'; $s->execute(); // executed with WHERE sex = 'female'

or 

$sex = 'male'; $s = $dbh->prepare('SELECT name FROM students WHERE sex = :sex'); $s->bindValue(':sex', $sex); // use bindValue to bind the variable's value $sex = 'female'; $s->execute(); // executed with WHERE sex = 'male'
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Brilliant, thanks! Question -- why might you want to use one over the other? Like when would it be useful or necessary to have the bind parameter evaluated only at execute() time?
@Coldblackice If you were executing the query multiple times with different data. With bindValue you'd need to re-bind the data each time. With bindParam you'd just need to update the variable. The main reason for using bindValue would be static data, e.g. literal strings or numbers.
For example, you want to use bindValue with function return values: $stmt->bindValue(':status', strtolower($status), PDO::PARAM_STR);
wanted to upvote, but because it is 666, I will leave it
233

Here are some I can think about :

  • With bindParam, you can only pass variables ; not values
  • with bindValue, you can pass both (values, obviously, and variables)
  • bindParam works only with variables because it allows parameters to be given as input/output, by "reference" (and a value is not a valid "reference" in PHP) : it is useful with drivers that (quoting the manual) :

support the invocation of stored procedures that return data as output parameters, and some also as input/output parameters that both send in data and are updated to receive it.

With some DB engines, stored procedures can have parameters that can be used for both input (giving a value from PHP to the procedure) and ouput (returning a value from the stored proc to PHP) ; to bind those parameters, you've got to use bindParam, and not bindValue.

Improve this answer

3 Comments

@PascalMartin Just what I wanted to know, can you bind values with bindParam. Cheers.
I still have no clue what that exactly means, what exactly are the variables and what are the values. I use bindParam to bind a value to a placeholder and with bindValue I can do the same! - in my example at least...
@Richard Variables: $stmt->bindParam(':id', $id); Values: $stmt->bindValue(':id', 1);. Note if we replace $id with 1 in bindParam an error will be emited.
231

The answer is in the documentation for bindParam:

Unlike PDOStatement::bindValue(), the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called.

And execute

call PDOStatement::bindParam() to bind PHP variables to the parameter markers: bound variables pass their value as input and receive the output value, if any, of their associated parameter markers

Example:

$value = 'foo'; $s = $dbh->prepare('SELECT name FROM bar WHERE baz = :baz'); $s->bindParam(':baz', $value); // use bindParam to bind the variable $value = 'foobarbaz'; $s->execute(); // executed with WHERE baz = 'foobarbaz'

or

$value = 'foo'; $s = $dbh->prepare('SELECT name FROM bar WHERE baz = :baz'); $s->bindValue(':baz', $value); // use bindValue to bind the variable's value $value = 'foobarbaz'; $s->execute(); // executed with WHERE baz = 'foo'
Improve this answer

1 Comment

I feel the need to point out that the examples are taken from @lonesomeday's answer, and not the other way around, since there are no credits given in the answer.
35

From Prepared statements and stored procedures

Use bindParam to insert multiple rows with one time binding:

<?php $stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)"); $stmt->bindParam(1, $name); $stmt->bindParam(2, $value); // insert one row $name = 'one'; $value = 1; $stmt->execute(); // insert another row with different values $name = 'two'; $value = 2; $stmt->execute();
Improve this answer

Comments

31

For the most common purpose, you should use bindValue.

bindParam has two tricky or unexpected behaviors:

  • bindParam(':foo', 4, PDO::PARAM_INT) does not work, as it requires passing a variable (as reference).
  • bindParam(':foo', $value, PDO::PARAM_INT) will change $value to string after running execute(). This, of course, can lead to subtle bugs that might be difficult to catch.

Source: http://php.net/manual/en/pdostatement.bindparam.php#94711

Improve this answer

Comments

6

You don't have to struggle any longer, when there exists a way lilke this:

$stmt = $pdo->prepare("SELECT * FROM someTable WHERE col = :val"); $stmt->execute([":val" => $bind]);
Improve this answer

Comments

5

The simplest way to put this into perspective for memorization by behavior (in terms of PHP):

  • bindParam: reference
  • bindValue: variable
Improve this answer

1 Comment

bindValue takes a value.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.