Passing comma separated list from controller to model

Question

I am using Codeigniter and trying to pass a comma separated list from my controller to the model to use it in a where_not_in query. I have the following code to create the comma separated list.

$mnames = Array();
foreach($facet_group['manufacturer_name'] as $u) $mnames[] = $u['value'];
$manufacturer_list = implode(",",$mnames);

which produces the following: Alcatel,Apple,Huawei,LG,Microsoft,Motorola,Nokia,Samsung,Sony. My query is this:

$this->db->select('name');
$this->db->from('wl_manufacturers');
$this->db->where('active', 1);
$this->db->where('supplier_type', 'product');
$this->db->where_not_in('name', $str);

$str being the comma separated list

the output from profiler is:

SELECT `name`
FROM `wl_manufacturers`
WHERE `active` = 1
AND `supplier_type` = 'product'
AND `name` NOT IN('Alcatel,Apple,Huawei,LG,Microsoft,Motorola,Nokia,Samsung,Sony')

This doesn't seem to work because there is no single quotes around each item in the list. However if I create the list manually like this: $array = array('Alcatel','Apple','Huawei','LG','Microsoft','Motorola','Nokia','Samsung','Sony'). It works and the output from the profiler is:

SELECT `name`
FROM `wl_manufacturers`
WHERE `active` = 1
AND `supplier_type` = 'product'
AND `name` NOT IN('Alcatel', 'Apple', 'Huawei', 'LG', 'Microsoft', 'Motorola', 
'Nokia', 'Samsung', 'Sony')

I have tried adding single quotes to the comma separated list items before passing it to the model:

$str = "'" . implode ( "', '", $mnames ) . "'";

but the the profiler output is this:

SELECT `name`
FROM `wl_manufacturers`
WHERE `active` = 1
AND `supplier_type` = 'product'
AND `name` NOT IN('\'Alcatel\', \'Apple\', \'Huawei\', \'LG\', \'Microsoft\', 
\'Motorola\', \'Nokia\', \'Samsung\', \'Sony\'')

Again not working because backslashes have been added. So the question is how can I create a comma separated list, pass it through to my model and get the query to work correctly?

Answer 1

you need to explode your comma separated string ($str) to create an array

$arr=explode (',',$str)

then you can use it in your query like

$this->db->where_not_in('name', $arr);

see explode: and CI's where_not_in()