How to Use bindParam to bind the parameter-SegmentFault when the where condition is used in the joint query of two tables in yii2.0

The Code is as follows: {code...} the code is as follows:

public function test(){   $a = array(1,2,3,4,5);    $uid = implode(',', $a);    $sql = "SELECT A.uid,B.id,B.state FROM A        LEFT JOIN B ON A.id=B.id        WHERE A.uid in ($uid)";    $cmd =  Self::getDb()->createCommand($sql);    $res =  $cmd->queryAll();    /*var_dump($res);    array (size=3)        0 =>             array (size=6)              'uid' => string '1' (length=2)              'id' => string '1100' (length=5)              'state' => string '2' (length=1)        1 =>             array (size=6)              'uid' => string '2' (length=2)              'id' => string '1200' (length=5)              'state' => string '2' (length=1)        2 =>             array (size=6)              'uid' => string '3' (length=2)              'id' => string '1300' (length=5)              'state' => string '2' (length=1)*/

// ------------------------------- Bind parameter method ----------------------------------------- $ sql2 = "select. uid, B. id, B. state from a left join B ON. id = B. id where. uid in (: uid) "; $ cmd2 = Self: getDb ()-> createCommand ($ sql2); $ cmd2-> bindParam (": uid ", $ uid ); $ res2 = $ cmd2-> queryAll ();/* var_dump ($ res2); array (size = 3) 0 => array (size = 6) 'uid' => string '1' (length = 2) 'id' => string '000000' (length = 5) 'State' = > String '2' (length = 1) */} If $ SQL returns three pieces of data, $ sql2 returns only one piece of data (tested, change the position of the element in $ a and find that the data in $ res2 is always the data corresponding to the first element with data in $ ). After testing and understanding, we found that bindParam will add a single quotation mark outside the parameter when replacing the placeholder, so that the in condition will become a whole, rather than five values. Question: 1. Even as a whole, why do we still get a piece of data. 2. How to correct this question 3. in YII2, if you want to use placeholders and where conditions use in, how should we use them?

Reply content:

The Code is as follows:

public function test(){   $a = array(1,2,3,4,5);    $uid = implode(',', $a);    $sql = "SELECT A.uid,B.id,B.state FROM A        LEFT JOIN B ON A.id=B.id        WHERE A.uid in ($uid)";    $cmd =  Self::getDb()->createCommand($sql);    $res =  $cmd->queryAll();    /*var_dump($res);    array (size=3)        0 =>             array (size=6)              'uid' => string '1' (length=2)              'id' => string '1100' (length=5)              'state' => string '2' (length=1)        1 =>             array (size=6)              'uid' => string '2' (length=2)              'id' => string '1200' (length=5)              'state' => string '2' (length=1)        2 =>             array (size=6)              'uid' => string '3' (length=2)              'id' => string '1300' (length=5)              'state' => string '2' (length=1)*/

// ------------------------------- Bind parameter method ----------------------------------------- $ sql2 = "select. uid, B. id, B. state from a left join B ON. id = B. id where. uid in (: uid) "; $ cmd2 = Self: getDb ()-> createCommand ($ sql2); $ cmd2-> bindParam (": uid ", $ uid ); $ res2 = $ cmd2-> queryAll ();/* var_dump ($ res2); array (size = 3) 0 => array (size = 6) 'uid' => string '1' (length = 2) 'id' => string '000000' (length = 5) 'State' = > String '2' (length = 1) */} If $ SQL returns three pieces of data, $ sql2 returns only one piece of data (tested, change the position of the element in $ a and find that the data in $ res2 is always the data corresponding to the first element with data in $ ). After testing and understanding, we found that bindParam will add a single quotation mark outside the parameter when replacing the placeholder, so that the in condition will become a whole, rather than five values. Question: 1. Even as a whole, why do we still get a piece of data. 2. How to correct this question 3. in YII2, if you want to use placeholders and where conditions use in, how should we use them?

• 2 hours ago
• Comment
• Reply

Please log on first and then comment

Sort by default

One answer

The answer is helpful to people and has reference value.0The answer is not helpful. It is a wrong answer.

Please use the AR direct operation provided by Yii2, please refer to http://www.yiiframework.com/doc-2.0/guide-db-active-record.html