Know the difference between bindParam() and bindValue() in PDO

February 08, 2014 | Last tested: May 2015 | 1064 views | Comments

The main difference is that when using bindParam(), the variable is bound as a reference and will only be evaluated at the time that execute() is called.

For example.

Reference: TABLE name is "members".

Please mouse over here to view the image.

Use bindParam(), initially bind the value Andrew to a variable named $firstName, and then later change the value to Dan.

$dbh = new PDO('mysql:host=HOST;dbname=DB_NAME','DB_USER','DB_PASSWORD'); //setup PDO connection here

$firstName = 'Andrew'; // will be ignored
$sth = $dbh->prepare("SELECT * FROM members WHERE firstName = ?");
$sth->bindParam(1,$firstName,PDO::PARAM_STR);
$firstName = 'Dan';
$sth->execute();
$result = $sth->fetch();

if($result){
	echo '<pre>';
		print_r($result);
	echo '</pre>';
}
else{
	echo 'record not found...';
}

Result:

Array
(
    [id] => 8
    [0] => 8
    [firstName] => Dan
    [1] => Dan
    [lastName] => Chen
    [2] => Chen
    [age] => 26
    [3] => 26
    [sex] => M
    [4] => M
    [location] => Washington
    [5] => Washington
)

 

Using bindValue(), declare the value of the variable $firstName to Andrew at the beginning, and change it to Dan before calling the execute() function.

$dbh = new PDO('mysql:host=HOST;dbname=DB_NAME','DB_USER','DB_PASSWORD'); //setup PDO connection here

$firstName = 'Andrew';
$sth = $dbh->prepare("SELECT * FROM members WHERE firstName = ?");
$sth->bindValue(1,$firstName,PDO::PARAM_STR);
$firstName = 'Dan'; // will be ignored
$sth->execute();
$result = $sth->fetch();

if($result){
	echo '<pre>';
		print_r($result);
	echo '</pre>';
}
else{
	echo 'record not found...';
}

Result:

Array
(
    [id] => 2
    [0] => 2
    [firstName] => Andrew
    [1] => Andrew
    [lastName] => Kimball
    [2] => Kimball
    [age] => 25
    [3] => 25
    [sex] => M
    [4] => M
    [location] => Texas
    [5] => Texas
)