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