ci mysql in,CodeIgniter数据库使用带有IN子句的query（）查询

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";