How to write a parameterized WHERE IN (?) condition using CodeIgniter's query() method?

Question

I wanto write a parameterized SELECT query in my CodeIgniter application.

I have an integer array with two values:

$prices = [23, 98];

I am trying to use that array in my IN condition, but the following attempt isn't working.

return $this->db->query(
    "select * from product where price IN (?)",
    array(implode(',',$prices)
)->result();

By checking $this->db->last_query(), this renders:

SELECT * FROM product WHERE price IN ('25,36')

Obviously, I don't want a single string value, but if I don't implode() the array in the parameter array, I get an error saying "operator does not exist".

John Conde · Accepted Answer · 2013-02-03 19:46:31Z

0

Try this (untested)

$query = $this->db->from('product')
                  ->where_in('price', implode(',',$prices))
                  ->get();

The CodeIgniter Active Record docs are very good so you should definitely read up on it. For instance you'll notice the select() method is unecessary since we want all items so * is assumed.

answered Feb 3, 2013 at 19:46

John Conde

220k100 gold badges464 silver badges504 bronze badges

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

2 Comments

user216084 Over a year ago

I believe there is no need to implode the array $prices or else the entire array after implode will be considered as a single string. Simply passing the array seems to the syntax, as seen in the example in documentation.

mickmackusa Over a year ago

Sorry John, this is my DV -- this answer is simply incorrect. This snippet will search for column values which match the solitary comma-separated string .

mickmackusa · Accepted Answer · 2024-09-03 21:13:47Z

When executing a WHERE IN query() call with a placeholder, CodeIgniter does not expect the parentheses after IN. CodeIgniter will automagically wrap the IN values in parentheses. If you add your own paratheses, your SQL will break because of the double parenthetical wrapping.

The second parameter expects an array where each element relates to each placeholder in order. Pass your $prices array (unchanged) as the first/lone element of the parameters array.

return $this->db->query('SELECT * FROM product WHERE price IN ?', [$prices])->result();

You must not implode your array -- this will render an invalid SQL string with IN followed by the string value with no parentheses.

If you had two placeholders, it would be written like this:

return $this->db->query(
    'SELECT * FROM product WHERE active = ? AND price IN ?',
    [$active, $prices]
)->result();

To enjoy standardized quoting/escaping, use CodeIgniter's query building methods.

return $this->db->where_in('price', $prices)->get('product')->result();

echo_Me · Accepted Answer · 2013-02-03 20:12:23Z

-1

try this

return  $this->db->query("select * from product where price IN ('". implode(',',$prices)->result()."' )");

answered Feb 3, 2013 at 20:12

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

1 Comment

mickmackusa Over a year ago

This unexplained answer is simply incorrect. It makes no sense to call ->result() INSIDE of the unfinished query.

Collectives™ on Stack Overflow

How to write a parameterized WHERE IN (?) condition using CodeIgniter's query() method?

3 Answers 3

2 Comments

You must not implode your array -- this will render an invalid SQL string with IN followed by the string value with no parentheses.

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

You must not implode your array -- this will render an invalid SQL string with IN followed by the string value with no parentheses.

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related