I have a following query in my CodeIgniter, which I'm trying to bind via parameter.
$q = "SELECT * FROM my_table WHERE id IN (?)"
$ids = "1,2,3,4";
$this->db->query($q, array($ids));
Above is not working, because the query binding treats $ids as string. How can I parameterize my query, but still be able to do "IN" operation?
EDIT
sorry, I have to use "raw SQL query". Above query is just a part of a much larger + complex query, which I can't use ActiveRecord for. Also I'm using Postgres.
Instead of string put it in array
$q = "SELECT * FROM my_table WHERE id IN (?)"
$ids = array(1,2,3,4);
$this->db->query($q, $ids);
You can achieve same thing like this without binding
Alternative
$ids = array(1,2,3,4);
$this->db->where_in('id',$ids);
$this->db->get('my_table');
Try this with where_in
$ids = array(1,2,3,4);
$this->db->select('*');
$this->db->from('my_Table');
$this->db->where_in('id',$ids);
$this->db->query($q, explode(',', $ids));
or
$this->db->where_in('id', explode(',', $ids))->get('my_table');
Active Records documentation: http://ellislab.com/codeigniter/user-guide/database/active_record.html#select
Use FIND_IN_SET like this
select * from table where FIND_IN_SET('$ids', id);
Try this code:
$ids = "('1','2','3','4')";
$q = "SELECT * FROM my_table WHERE id IN ".$ids;
$this->db->query($q);
you should use the
$ids = array('1','2','3','4');
$this->db->where_in('id', $ids);
The where_in is used in codignitor.
point is
$ids = "1,2,3,4";
$a = array($ids);
//$a[0] = "1,2,3,4";
//not $a[0]=1;$a[1]=2;$a[2]=3;$a[3]=4;
want to keep your style
$ids = "1,2,3,4";
$a = explode(",",$ids);
$q = "SELECT * FROM my_table WHERE id IN ?"
$ids = "1,2,3,4";
$this->db->query($q, explode(",", $ids));
FIND_IN_SET()? Just a workaround, if no suitable answers supplied.$this->db->query($q, explode(",",$ids));