The difference between the bindParam and bindValue methods of the PDOStatement class in php pdo

The specific description of the two methods in the PDOStatement class is as follows

bool PDOStatement::bindParam ( mixed $parameter , mixed &$variable [, int $data_type = PDO::PARAM_STR [, int $length [, mixed $driver_options ]]] )<pre name="code" class="php">bool PDOStatement::bindValue ( mixed $parameter , mixed $value [, int $data_type = PDO::PARAM_STR ] )

Difference 1: bindParam is to bind a parameter to the specified variable name, and bindValue is to bind a value to a parameter

<pre name="code" class="php">$db = new PDO('mysql:host=localhost;dbname=dbtest;charset=utf8','user','pass');
$st = $db->prepare('select * from tabletest where id = ?');
$id = 1;
$st->bindParam(1,$id,PDO::PARAM_INT);
//$st->bindValue(1,$id,PDO::PARAM_INT);

in the above code , whether it is bindParam or bindValue, it can be executed normally, but if it is replaced with the following code

$db = new PDO('mysql:host=localhost;dbname=dbtest;charset=utf8','user','pass');
$st = $db->prepare('select * from tabletest where id = ?');
$st->bindParam(1,1,PDO::PARAM_INT);
//$st->bindValue(1,1,PDO::PARAM_INT);

bindParam will report the following error, but bindValue can be executed normally

Fatal error: Cannot pass parameter 2 by reference

summary: the second parameter of bindParam has and can only be a variable name, not a specific value. bindValue can bind a variable name, and You can bind a value

Difference 2: Unlike PDOStatement::bindValue(), variables in PDOStatement::bindParam() are bound as references and are only called in PDOStatement::execute() The value is taken only when

$db = new PDO('mysql:host=localhost;dbname=dbtest;charset=utf8','user','pass');
$st = $db->prepare('select * from tabletest where id = ?');
$id = 1;
$st->bindParam(1,$id,PDO::PARAM_INT);
$id = 2;
$st->execute();
$rs = $st->fetchAll();
print_r($rs);

first assigns $id a value of 1, bindParam binds the variable, before execute, changes $id to 2, and then performs the execution operation. The result set obtained at this time is when id=2 The query result is not the query result when the id is 1. This is the explanation of the variable as a reference. Before execute, we can replace this variable, and the variable value substituted when executing the execute operation is the last time the variable was changed. value.

$db = new PDO('mysql:host=localhost;dbname=dbtest;charset=utf8','user','pass');
$st = $db->prepare('select * from tabletest where id = ?');
$id = 1;
$st->bindValue(1,$id,PDO::PARAM_INT);
$id = 2;
$st->execute();
$rs = $st->fetchAll();
print_r($rs);

But bindValue is different. After using bindValue to bind a variable, even if the value of the variable is changed before execute is executed, the result will not change. For example, in the above example, even if we change $id to 2, the final execution result will still output the result when $id =1, because bindValue is not bound to a reference to a variable and will not change as the variable changes.

Although both can complete the binding of sql parameters, there are still differences between the two. In practical applications, we should choose the one that suits us. Here is an example of improper use of bindParam

Suppose there is a data table with shaping There are two fields, id and string name, and there is an array of data $params = array(1,'Zhang San') ready to be inserted using preprocessing. The specific code is as follows

$db = new PDO('mysql:host=localhost;dbname=dbtest;charset=utf8','user','pass');
$st = $db->prepare('insert into tabletest(id,name) values(?,?)');
$params = array(1,'张三');
foreach($params as $k => $v){
    $index = $k + 1;
    $st->bindParam($index,$v);
}
$st->execute();

The sql statement to be executed under normal circumstances should be

insert into tabletest(id,name) values(1,'张三');

In fact, the actual executed sql statement is

insert into tabletest(id,name) values('男','男');

The reason is that the variables in bindParam are bound as references, so the value inserted in each field becomes the value of the last field, and at this time we use bindValue will not have this problem. Another point that needs to be explained in this example is that if you use a question mark placeholder combined with an index array, you need to pay special attention to the parameter identifier of bindValue (the first parameter of this method). The index array starts from 0 by default, and bindValue The parameter identifier starts with 1. If you directly insert the 0 subscript of the index array, the program will report an error. You must pay attention when using it.

The above introduces the difference between the bindParam and bindValue methods of the PDOStatement class in php pdo, including the relevant content. I hope it will be helpful to friends who are interested in PHP tutorials.