CodeIgniter数据库使用带有IN子句的query（）查询 [英] CodeIgniter Database Query using query() with IN clause

问题描述

我的模型中有以下代码：

  $ sql =SELECT t1。*，t2。* FROM Table1 t1 INNER JOIN Table2 t2 ON t1.id = t2.id AND t2.title IN（？）AND t1.type =？ORDER BY t1.id; 
 $ q = $ this-> db-> query（$ sql，array（$ filter，$ type））;问题是这个查询工作正常，如果：
 
 
 
 
 
 
 
 
 $ b 
  $ filter ='a'; 
  
但不会返回任何值：
  $ filter ='a'，'b'，'c'，'d'; 
 
对于第二种情况，我可以看到CI正在转储$过滤器，如下所示：
  SELECT t1。*，t2。* FROM Table1 t1 INNER JOIN Table2 t2 ON t1.id = t2.id AND t2.title IN（' \'a\'，\'b\'，\'c\'，\'d\''）AND t1.type =？ ORDER BY t1.id 
  
 
 
解决方案
 CI在将它们发送到MySQL之前转义引号。你应该使用一个数组的过滤器，并构造这样的东西（未测试;））
  $ filter = a'，'b'，'c'）; 
 $ sql =SELECT t1。*，t2。* 
 FROM Table1 t1 
 INNER JOIN Table2 t2 
 ON t1.id = t2.id 
 AND t2。 title IN（。implode（'，'，array_fill（0，count（$ filter），'？'）））
 AND t1.type =？
 ORDER BY t1.id 
 //编辑：检查$ filter是不是一个数组（当它是一个单值字符串）
 $ filter = is_array（$ filter）？ $ filter：array（$ filter）; 
 $ q = $ this-> db-> query（$ sql，array_merge（$ filter，array（$ type））））; 
  
您也应该重写查询：
  $ sql =SELECT t1。*，t2。* 
 FROM Table1 t1 
 INNER JOIN Table2 t2 
 ON t1.id = t2。 id 
 WHERE 
 t2.title IN（。implode（'，'，array_fill（0，count（$ filter），'？'）））
 AND t1.type = ？
 ORDER BY t1.id; 
  
 
I have the following code in my model:
$sql = "SELECT t1.*, t2.* FROM Table1 t1 INNER JOIN Table2 t2 ON t1.id = t2.id AND t2.title IN (?) AND t1.type = ? ORDER BY t1.id";
$q = $this->db->query( $sql, array( $filter, $type ) );
The problem is that this query works fine if:
$filter = 'a';
But returns nothing if:
$filter = "'a','b','c','d'";
For the second case I can see that CI is escaping the $filter like this:
SELECT t1.*, t2.* FROM Table1 t1 INNER JOIN Table2 t2 ON t1.id = t2.id AND t2.title IN ('\'a\',\'b\',\'c\',\'d\'') AND t1.type = ? ORDER BY t1.id

解决方案
That is correct behavior. CI escapes quotes before sending them to MySQL. You should probably use an array for the filter and construct something like this (not tested ;) )
$filter = array('a','b','c');
$sql = "SELECT t1.*, t2.* 
     FROM Table1 t1 
     INNER JOIN Table2 t2 
         ON  t1.id = t2.id 
         AND t2.title IN (". implode(',', array_fill(0, count($filter), '?')).") 
         AND t1.type = ? 
     ORDER BY t1.id";
//edit: check if $filter is not an array ( when it is a single value string )
$filter = is_array( $filter ) ? $filter : array( $filter );
$q = $this->db->query( $sql, array_merge( $filter, array( $type ) ) );
You should also rewrite the query a bit:
$sql = "SELECT t1.*, t2.* 
     FROM Table1 t1 
     INNER JOIN Table2 t2 
         ON  t1.id = t2.id 
     WHERE
         t2.title IN (". implode(',', array_fill(0, count($filter), '?')).") 
         AND t1.type = ? 
     ORDER BY t1.id";


                        
这篇关于CodeIgniter数据库使用带有IN子句的query（）查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！