CodeIgniter Database Query using query() with IN clause [duplicate]

Question

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: db->query( '...AND t2.title IN (?,?,?,?)...', $filter ); Tell me if you need a complete piece of code. — Patrick Savalle
– Patrick Savalle, Commented Aug 8, 2012 at 12:59

M. Ahmad Zafar · Accepted Answer · 2012-08-08 14:55:58Z

2

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

edited Aug 8, 2012 at 14:55

M. Ahmad Zafar

4,9594 gold badges35 silver badges45 bronze badges

answered Aug 8, 2012 at 13:06

Patrick Savalle

4,4964 gold badges24 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

M. Ahmad Zafar Over a year ago

Thanks @Patrick it worked with a slight modification which I have added to your answer. $filter = is_array( $filter ) ? $filter : array( $filter );

Collectives™ on Stack Overflow

CodeIgniter Database Query using query() with IN clause [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related