CodeIgniter Query Binding Using IN Clause with String array or string list

Question

Below is an example which fails. I have tried several ways of getting $arrayOfIds below into the correct syntax for the id IN (?) with no luck. If I do not query bind, it works.

Note: We are not using Active Record.

// this is actually being passed in as argument
$arrayOfIds = array('A0000-000000000001','B0000-000000000001','C0000-000000000001');  

$params = array();
array_push($params,1); // for the status
array_push($params, "'" . implode("','",$arrayOfIds) . "'"); // for the id in 

$sql = "SELECT name FROM my_table WHERE status = ? AND id IN (?) ";

$query = $this->db->query($sql,$params);

Have you tried dumping your query using $this->db->last_query() after it's run to see what the SQL looks like? — Matthew Daly
– Matthew Daly, Commented Nov 3, 2013 at 22:17
well, I suppose it is just a typo from copying to here, but you are using $arrayOfIds above, then $companyEntityIds. Let's at least correct our post, then run $this->db->last_query() as Mathew Daly said — jmadsen
– jmadsen, Commented Nov 3, 2013 at 23:24
so, yo uedited your typo, but you didn't give us the useful info we need - what does your query now look like? — jmadsen
– jmadsen, Commented Nov 4, 2013 at 7:43
Quick follow up dumping the last_query showed the error. CI auto escapes bound query params. So was ending up with permutations like: IN ('\'A0000-000000000001,B0000-000000000001,C0000-000000000001'\'') -OR- ('\'A0000-000000000001\',\'B0000-000000000001\',\'C0000-000000000001\''). — tibc-dev
– tibc-dev, Commented Nov 4, 2013 at 17:40

Yan Berk · Accepted Answer · 2013-11-04 15:56:26Z

6

You need to build the params array differently and add as many question marks as the size of arrayOfIds.

EDIT: The question marks are generated dynamically according to the array size.

$arrayOfIds = array('A0000-000000000001','B0000-000000000001','C0000-000000000001');  

$params = array();
array_push($params, 1);

$params = array_merge($params, $arrayOfIds);
$in_string = str_replace(' ', ',', trim(str_repeat("? ", count($arrayOfIds))));  

$sql = "SELECT name FROM my_table WHERE status = ? AND id IN (".$in_string.")";

$query = $this->db->query($sql, $params);

edited Nov 4, 2013 at 15:56

answered Nov 4, 2013 at 1:14

Yan Berk

14.5k9 gold badges58 silver badges52 bronze badges

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

5 Comments

jmadsen Over a year ago

that's completely wrong. He is making his arrayOfIds into a single variable, and then trying to bind it. With your method, he would have to have a static number of array elements, which may not be true

Yan Berk Over a year ago

Using his method Codeigniter is escaping the ticks making the query unusable. Using my answer, you can build the IN (?,?,...) with a simple loop over the array size.

tibc-dev Over a year ago

This approach works very well and solved my problem. As noted in my comment above, CodeIgniter escapes each query bound item which is nice; however, leaves us this challenge when passing lists. Your approach of creating a bind list variable for my list is great along with the merge to my params array.

jmadsen Over a year ago

you can also just tell it not to add the backticks

Yan Berk Over a year ago

How can you do that and keep the query escaped?

Maxim Nikolaev · Accepted Answer · 2017-01-05 14:40:26Z

1

$arrayOfIds = array('A0000-000000000001','B0000-000000000001','C0000-000000000001');  

$status = 1;

$sql = "SELECT name FROM my_table WHERE status = ? AND id IN ?";

$query = $this->db->query($sql, array($status, $arrayOfIds));

answered Jan 5, 2017 at 14:40

Maxim Nikolaev

112 bronze badges

1 Comment

JJJ Over a year ago

Instead of just dumping some code, you should explain what this code does, how it's different from the original code and why it solves the problem.

Collectives™ on Stack Overflow

CodeIgniter Query Binding Using IN Clause with String array or string list

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related