why the sentence fails with PDO

Question

why the sentence fails with PDO

Navigation

#1 by (2 votes)

0

I am doing a sentence with PDO in PHP and an error occurs when assigning the values.

$request = 
[
   'lang'  =>  ['value'=>'es','type'=>PDO::PARAM_STR, 'length'=>2],
   'page'  =>  ['value'=>'home','type'=>PDO::PARAM_STR, 'length'=>null]
];

foreach ($request as $key => $value) 
{   
   $_value = $request[$key]['value'];
   $_type  = $request[$key]['type'];
   $_len   = $request[$key]['length'];


   // BindParam
   if($_len == null)
      $beforeQuery ->bindParam( $key, $_value ,$_type );
   else
      $beforeQuery ->bindParam( $key, $_value, $_type, $_len );

   // BindValue            
   $beforeQuery ->bindValue( ':'.$key, $_value ,$_type );


 }

Sentence

SELECT * FROM page WHERE lang_id=:lang AND page=:page

Debug

With BindParam I get this result that is not the correct one but,

SELECT * FROM interface WHERE lang='home' AND page='home'

with BindValue if he gives me the sentence correctly.

SELECT * FROM interface WHERE lang='es' AND page='home'

php pdo

asked by dGame 09.08.2018 в 10:04

source

1 answer

I lose 2 records when opening an ofstream C ++ Method that receives datetime nulleable, change format to MM / dd / yyyy

score 2 · Accepted Answer

When you use bindParam the variable that you pass as the second argument is a reference. Then you are running two iterations. In the first you say

$key = 'lang';
$_value = 'es';
$_type = PDO::PARAM_STR;
$beforeQuery ->bindParam( $key, $_value ,$_type );

In the second:

$key = 'page';
$_value = 'home';
$_type = PDO::PARAM_STR;
$beforeQuery ->bindParam( $key, $_value ,$_type );

For PDO, at the moment of execution, and only then, it evaluates the value of the second parameter (the variable $ _value), and at that height, the value of $ _value is the last one defined within the loop , that is, home .

The correct thing would be to do, in each iteration:

$beforeQuery ->bindValue( ':'.$key, $_value ,$_type );

But it should also result if you pass the value by reference ( &$value ):

foreach ($request as $key => &$value) {  
     $beforeQuery ->bindParam( ':'.$key, $value['value'] ,$value['type'] );
}

What utility could then have bindParam if in this example the cleanest would be to use bindValue ?

The answer is that it would make sense to use it for $statement->execute(...)

$_value='cualquier cosa';

$beforeQuery->bindParam('id', $_value, PDO::PARAM_STR);

foreach($request as $key => $value) {
   $_value = $value['valor'];
   $result = $beforeQuery->execute();
   .. haces algo con result ...
}

And that works because it is the execute that evaluates the variable, while the bindParam does not evaluate but sets a link lazy .