Pdo Bindparam Vs. Execute

PDO bindParam vs. execute

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.

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.

PDO execute($params) vs bindParam

I've discovered that this query failed because I was attempting to assign a paramater to a LIMIT clause value. When passing parameters to the execute function, PDO wraps ALL parameters in quotes, and the LIMIT clause can't handle quoted parameters.

The solution (until I find something better, which I'm still hoping for) is old-school string concatenation on the query to generate the complete LIMIT clause before adding the rest of my parameters.

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: Diffetence between execute($parameters) and bindParam() ...and more

bindParam() binds by reference. It means that if you first execute your prepared statement, and then change the value of the variable and then execute again then the query will be run with the updated value.

execute() with arguments: you just supply the arguments but nothing more happens.

By default all placeholders are wrapped with quotes. If you need it without quotes you can use PDO::PARAM_INT to explicitly tell PDO you want it as an integer.

The return of execute() is a statement handle. If your query executes successfully you will get a statement handle back. Use rowCount() to check if you got any rows back.

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.

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'

or

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

---

Related Topics