Difference Between Bindparam and Bindvalue

What is the difference between bindParam and bindValue?

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'

$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'

What is the differences and advantages between bindParam(), bindValue() and execute(array())

bindParam() binds the parameter by reference, so it will be evaluated at $stmt->execute(), which is unlike bindValue() which evaluates at the call of the function itself.

So as an example:

bindParam:

<?php

    try {

        $dbh = new PDO("mysql:host=localhost;dbname=test", "root", "");
        $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $stmt = $dbh->prepare("SELECT * FROM test WHERE number = ?");
        $stmt->bindParam(1, $xy, PDO::PARAM_INT);
        $xy = 123;  //See here variable is defined after it has been bind
        $stmt->execute();

        print_r($stmt->fetchAll());

    } catch(PDOException $e) {
        echo $e->getMessage();
    }

?>

works great!

bindValue:

<?php

    try {

        $dbh = new PDO("mysql:host=localhost;dbname=test", "root", "");
        $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $stmt = $dbh->prepare("SELECT * FROM test WHERE number = ?");
        $stmt->bindValue(1, $xy, PDO::PARAM_INT);
        $xy = 123;  //See here variable is defined after it has been bind
        $stmt->execute();

        print_r($stmt->fetchAll());

    } catch(PDOException $e) {
        echo $e->getMessage();
    }

?>

output:

Notice: Undefined variable: xy

Also a few other differences:

bindParam() also has the argument length which can(must) be used if you call a IN&OUT procedure to store the output back in the variable (Which also requires to append PDO::PARAM_INPUT_OUTPUT with an OR statement to the type argument)
With bindParam() & bindValue() you can specify the type of the value, which you can't do in the execute(), there everything is just a string (PDO::PARAM_STR)

PHP PDO with bindParam vs bindValue?

PDOStatement::bindParam() is a way to tell PDO: «when you execute this statement later, please read the value from this variable». That means that when you call $STH -> execute() the $value must exist and must keep the intended value.

Since you are using it this way:

foreach($fields as $dbfield => $field) {
    $value = isset($_POST[$field]) ? 1 : 0;
    $STH -> bindParam( ':' . $dbfield, $value, PDO::PARAM_INT, 1 );
}

... $value gets overwritten on each loop iteration and your values get lost. (Only the last one remains.)

You need to use PDOStatement::bindValue() that means: «store this value and use it later when you execute the statement». This way, the variable is no longer necessary.

PDO bindparam and bindvalue. Which is the diference?

You don't have to set the lenght for outputting, you can do this if your want.

This is what the manual says about the difference between bindValue() and bindParam()

Binds a PHP variable to a corresponding named or question mark placeholder in the SQL >statement that was used to prepare the statement. Unlike PDOStatement::bindValue(), the >variable is bound as a reference and will only be evaluated at the time that >PDOStatement::execute() is called.
Most parameters are input parameters, that is, parameters that are used in a read-only >fashion to build up the query. Some drivers 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.

I use always bindParam when I want to add a variable. But I guess you have to make your own decission for that.

Confusion between bindValue() and bindParam()?

There should be no difference in how values are escaped or not escaped. bindParam differs from bindValue in that it references the variable, binding the value only when you execute the statement. bindValue takes the value immediately. To illustrate:

$stmt = $db->prepare('SELECT * FROM `table` WHERE foo = :foo');

$foo = 'foo';
$stmt->bindValue(':foo', $foo);
$foo = 'bar';

$stmt->execute();

The above executes like SELECT * FROM table WHERE foo = 'foo';

$stmt = $db->prepare('SELECT * FROM `table` WHERE foo = :foo');

$foo = 'foo';
$stmt->bindParam(':foo', $foo);
$foo = 'bar';

$stmt->execute()

The above executes like SELECT * FROM table WHERE foo = 'bar'.

It's true that neither cares about _ or % as special characters, because generally speaking they aren't special characters as far as the syntax is concerned, and the database driver is not able to analyze the context to figure out whether you mean % to be a wildcard or the actual character "%" in the context of a LIKE query.

Both protect against SQL injection.

Should I use bindParam(), bindValue(), or execute() for PDO prepared statements

Security wise, there is not a single problem with sending parameters into execute(). So, your concern is superfluous. Given strings are treated safely AND we are sending all our parameters as strings, we can logically conclude that all parameters are treated safely (note that I don't blame your question. It's always better to ask than sorry. I am just making things straight).

The only possible issue with treating all parameters as strings is on the database server side. Yet most database servers allow loose typing in SQL, means any data literal will be recognized and treated properly, despite being sent as a string. There are only few edge cases when it won't work. Here is an excerpt from my PDO tutorial on the matter:

However, sometimes it's better to set the data type explicitly.
Possible cases are:

LIMIT clause in emulation mode or any other SQL clause that just cannot accept a string operand.

complex queries with non-trivial query plan that can be affected by a wrong operand type

peculiar column types, like BIGINT or BOOLEAN that require an operand of exact type to be bound (note that in order to bind a BIGINT
value with PDO::PARAM_INT you need a mysqlnd-based
installation).

As a conclusion, given you already turned emulation off, you can use the execute() method virtually all the time.

Can i use both bindValue and bindParam on a single prepared statement?

For the SQL statement there is no difference between bindValue() and bindParam(). The only difference is how--or rather, when--PHP reads the input variables. So you can use both together, although that may cause hard-to-find issues (regarding the result of the SQL operation) later.

So it's best to avoid confusion and stick to bindValue() unless you need the special behaviour of bindParam().

bindValue / bindParam vs array performance

Aside from readability of code, and how bindParam references values, are there are performance positives/negatives between the two

No.

would each $stmt->bindValue() here be another trip to the db

No.