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?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 2

Best regards, Jernej

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
See the PDO example under "Create and Execute a SELECT Query" in this article.

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.
elepilAuthor Commented:
Very good explanations from both of you. Very good example from Jernej.

Thank you both!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.