63

I often see code using bindParam or bindValue with PDO. Is simply passing arguments to execute frowned upon for any reason?

I understand that bindParam actually binds to the variables and that you can set the type of parameter being bound with both bind methods, but what if you are only inserting strings?

$query = "SELECT col1 FROM t1 WHERE col2 = :col2 AND col3 = :col3 AND col4 = :col4";
$pdo->bindValue(':col2', 'col2');
$pdo->bindValue(':col3', 'col3');
$pdo->bindValue(':col4', 'col4');

I often see the above, but personally I prefer:

$pdo->execute(array(':col2' => 'col2', ':col3' => 'col3', ':col4' => 'col4'));

It is not as verbose and visually it makes more sense to me to have the inputs "going in" to the query together. However, I hardly ever see it used.

Is there a reason to prefer the bind methods over passing parameters to execute when you don't have to take advantage of the special behaviors of the former?

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
Explosion Pills
  • 188,624
  • 52
  • 326
  • 405
  • 1
    I think it's down to a matter of preference - I tend to prefer using `bindValue` and `bindParam` because I think the resulting code is easier to read. – andrewsi Sep 12 '12 at 16:16
  • 2
    It is indeed down to preference. If you don't require the ability fo force types (when you're only using strings), passing an array to execute() is quick and handy. – Michael Berkowski Sep 12 '12 at 16:17

3 Answers3

72

You might find bindParam used when you just want to bind a variable reference to a parameter in the query, but perhaps still need to do some manipulations on it and only want the value of the variable calculated at time of query execution. It also allows you to do more complex things like bind a parameter to a stored procedure call and have the returned value updated into the bound variable.

For more, see the bindParam documentation, bindValue documentation and execute documentation.

For example

$col1 = 'some_value';
$pdo->bindParam(':col1', $col1);
$col1 = 'some_other_value';
$pdo->execute(); // would use 'some_other_value' for ':col1' parameter

bindValue and passing an array to execute behave in much the same way as the parameter value is fixed at that point and SQL executed accordingly.

Following the same example above, but using bindValue

$col1 = 'some_value';
$pdo->bindValue(':col1', $col1);
$col1 = 'some_other_value';
$pdo->execute(); // would use 'some_value' for ':col1' parameter

When passing values directly in execute all values are treated as strings (even if integer value is provided). So if you need to enforce data types, you should always use bindValue or bindParam.

I think you might see bind* used more than execute(array) as many consider it to be better coding practice to explicitly define data types in parameter declarations.

Anthony
  • 2,014
  • 2
  • 19
  • 29
Mike Brant
  • 70,514
  • 10
  • 99
  • 103
  • Hi, Mike. Out of interest could you elaborate on this? It's always interested me how passing variables to `bindParam()` works, as sometimes I get "Cannot pass variable by reference" errors, and not actually sure what that means why I think about it. – Martin Bean Sep 12 '12 at 16:29
  • @MartinBean I have updated my answer to include an example of how `bindParam` would evaluate value of bound variable at query execution time. – Mike Brant Sep 12 '12 at 16:34
  • How is using `execute` not explicit in parameter declarations? – Explosion Pills Sep 12 '12 at 16:36
  • 1
    @ExplosionPills I meant explicit regarding data type. Sorry for confusion, I have updated my answer. – Mike Brant Sep 12 '12 at 16:38
  • 3
    bindParam passes a variable by reference, which means the value itself is evaluated only when it's used (the query execution). This allows you to, for example, query in a loop, simply changing the variables, without binding over and over again. – Madara's Ghost Sep 12 '12 at 16:41
  • 1
    Explicit data types being good practice is a good answer .. in theory .. however I rarely see that either. I think I'll stick to `execute` most of the time, personally. – Explosion Pills Sep 12 '12 at 17:05
  • 1
    MySQL accepts where number > '6', so when should we really need to convert parameter as an integer? – Chan Jul 27 '20 at 00:03
10

By passing the parameters along with the $pdo->execute() method, all values in the array with be passed, as PDO::PARAM_STR to the statement with the $pdo->bindParam() function.

The main difference that I can see now, is that with the $pdo->bindParam() function, you can define the data type passed along, using the PDO::PARAM_* constants as described in the PHP.net manual

Jens Kooij
  • 379
  • 1
  • 8
4

Simple, the value of bindParam may change but the value of bindValue can not change. Example:

$someVal=10;
$someVal2=20;
/* In bindParam, the value argument is not bound and 
will be changed if we change its value before execute.
*/
$ref->bindParam(':someCol',$someVal);
$someVal=$someVal2;
$ref->execute();
//someCol=20
/* In bindValue, the value argument is bound and 
never changed if we change its value before execute.
*/
$ref->bindValue(':someCol',$someVal);
// here assignment is referral (&$someVal)
$someVal=$someVal2;
$ref->execute();
//someCol=10
Teerath Kumar
  • 488
  • 5
  • 15
  • 5
    I think the OP was asking the difference between execute and bindParam not bindParam and bindValue but i just started using PDO and didnt know the difference – tsukimi Oct 09 '15 at 01:35