• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

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

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.
0
elepil
Asked:
elepil
2 Solutions
 
Jernej NavotnikPHP/Phyton DeveloperCommented:
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
0
 
Ray PaseurCommented:
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.
0
 
elepilAuthor Commented:
Very good explanations from both of you. Very good example from Jernej.

Thank you both!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now