We help IT Professionals succeed at work.

PHP PDO, question about bindValue() and bindParam()

elepil
elepil asked
on
From my experimentations, bindValue() seems to be able to take ANYTHING. For example, all the following work without issuing an error message:

$stmt->bindValue(:firstname, $a);
$stmt->bindValue(:firstname, 1);
$stmt->bindValue(:firstname, "1");
$stmt->bindValue(:firstname, "Jo" + "hn");
$stmt->bindValue("firstname, 100-99);

Open in new window


Now I know some of the above are nonsensical since even though the placeholder is :firstname, I'm passing all kinds of crud to it without getting any kind of MySQL error. I can even pass in numeric and string values, even string concatenations and numeric calculations, and the query would execute smoothly.

So what is bindParam() even for? Does anyone even use it?? If so, for what?

Thanks.
Comment
Watch Question

PHP/Phyton Developer
Commented:
Dear elepil!

As you said, bindValue(), takes anything as a parameter, while bindParam() can take only a variable (and it takes it by reference).

The main difference is, that with bindParam(), the variable gets evaluated when the execute() is called.
What this means in a dummy example:
$a = '12345';
$s = $dbh->prepare('SELECT name FROM numbers WHERE number = :a');
$s->bindParam(':a', $a); // use bindParam to bind the variable
$a = '54321';
$s->execute(); // executed with WHERE number = '54321'

Open in new window


While in bindValue()
$a = '12345';
$s = $dbh->prepare('SELECT name FROM numbers WHERE number = :a');
$s->bindValue(':a', $a); // use bindValue to bind the variable
$a = '54321';
$s->execute(); // executed with WHERE number = '12345'

Open in new window


The benefits of this doing "normal queries" are, well sparse - maybe someone else can point these for normal queries.

One would use bindParam when needed to manipulate the value of the variable before execute. Or the specialty in bindParam lies in the fact that it can receive a value from a stored procedure and use the updated one.

Here are a few more links and great examples as well:
StackOverflow
StackOverflow 2

Best regards, Jernej
Most Valuable Expert 2011
Top Expert 2016
Commented:
See the PDO example under "Create and Execute a SELECT Query" in this article.
http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

In a nutshell, parameter "binding" means that your PHP variable assumes a special importance in the PDO work -- it's a special relationship.  The values you put into the bound variable are used in the query at the "bound" location and no other variables in your PHP script are used this way.  IIRC you can bind PHP variables in the output side of PDO as well, but I don't have any examples of that -- I do not use PDO very much.  But by way of example, these are more-or-less equivalent:

$stmt->bindValue(:firstname, $a); // NOT SURE IF QUOTES ARE NEEDED HERE?
$stmt->bindParam(':firstname', $a);

They may be different, in that bindValue() makes a one-time binding which can be reset for each bindValue() statement, whereas bindParam() makes a life-of-the script binding between the placeholder and the PHP variable name.  Once bound, anything you put into the variable $a will be eligible for use in the query.  It might be worth testing that idea a bit -- I do not have any code example to prove or disprove the theory, but it's how I understand the inner workings.

The best examples of "binding" are probably found in AngularJS, where a client input can be bound to a scoped data element.  In AngularJS, this is a two-way binding; a change of the input is immediately reflected in the data model, and vice versa.  Binding did not exist when PHP was originally written and it has only come to the language through the extensions.

Author

Commented:
Very good explanations from both of you. Very good example from Jernej.

Thank you both!