PDO Bindparam and BindValue

PDOStatement::bindParam() — Binds a parameter to the specified variable name

Binds a PHP variable to a corresponding named or question mark placeholder in the SQL statement that was used to prepare the statement. Unlike bindvalue() the variable is bound as a reference and will only be evaluated at the time that execute() is called.

Whereas ,

PDOStatement::bindValue()Binds a value to a parameter

Binds a value to a corresponding named or question mark placeholder in the SQL statement that was used to prepare the statement.

PDO::bindparam()
PDO::bindvalue()

PDOStatement::bindParam — Binds a parameter to the specified variable name

PDOStatement::bindValue — Binds a value to a parameter


Binds a PHP variable to a corresponding named or question mark placeholder in the SQL statement that was used to prepare the statement.

 Binds a value to a corresponding named or question mark placeholder in the SQL statement that was used to prepare the statement.

bindParam() is passed to PDO by reference

Thus with bindParam() you can't do something like :-

$stmt->bindValue(":something", "String Passed");

As, you need to pass by reference,  you can't do this :-

<?php

foreach ($params as $key => $val) {
    $sth->bindParam($key, $val);
}

?>

You need to like below , you can do this  :-

<?php

foreach ($params as $key => &$val) { // Notice & just before $val

    $sth->bindParam($key, $val);

}

?>

 bindValue() is not passed by reference.

Thus with bindValue() you can do something like 

$stmt->bindValue(":something", "String Passed");

You can do this :-

<?php

foreach ($params as $key => $val) {
    $sth->bindParam($key, $val);
}

?>

In bindparam() you can't use a placeholder twice Example :-

"select * from mails where sender=:me or recipient=:me"

In bindvalue() you can use a placeholder twice Example :-

"select * from mails where sender=:me or recipient=:me"

 

bindValue() escapes quotes it does not escape "%" and "_", so be careful when using LIKE. A malicious parameter full of %%% can dump your entire database if you don't escape the parameter yourself. PDO does not provide any other escape method to handle it.


Example #1 Execute a prepared statement with named placeholders

<?php

    $db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');

    /* Execute a prepared statement by binding PHP variables */

    $calories = 150;
    $colour = 'red';

    $stmt = $db->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');

    $stmt->bindParam(':calories', $calories, PDO::PARAM_INT);

    $stmt->bindParam(':colour', $colour, PDO::PARAM_STR, 12);

    $stmt->execute();

?>

Example #1 Execute a prepared statement with named placeholders

<?php

    $db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');

    /* Execute a prepared statement by binding PHP variables */

    $calories = 150;
    $colour = 'red';

    $stmt = $db->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');

    $stmt->bindValue(':calories', $calories, PDO::PARAM_INT);

    $stmt->bindValue(':colour', $colour, PDO::PARAM_STR);

    $stmt->execute();

?>
 

Example #2 Execute a prepared statement with question mark placeholders

<?php

    $db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');

    /* Execute a prepared statement by binding PHP variables */

    $calories = 150;
    $colour = 'red';

    $stmt = $db->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < ? AND colour = ?');

    $stmt->bindParam(1, $calories, PDO::PARAM_INT);

    $stmt->bindParam(2, $colour, PDO::PARAM_STR, 12);

    $stmt->execute();

?>
 

Example #2 Execute a prepared statement with question mark placeholders

<?php

    $db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');

    /* Execute a prepared statement by binding PHP variables */

    $calories = 150;
    $colour = 'red';

    $stmt = $db->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < ? AND colour = ?');

    $stmt->bindValue(1, $calories, PDO::PARAM_INT);

    $stmt->bindValue(2, $colour, PDO::PARAM_STR);

    $stmt->execute();

?>

Loading ...

Related Results :

  1. PDO Bindparam and BindValue
Note :
  • Related Posts are generally User Blog posts.
  • or Other tutorials from other networks of w3clan.com.
  • Any registered user can create related posts based on search term tags.

About the Author