Use parameterized query with IN clause - CodeIgniter

Question

I have query with IN clause and I want to use it as parameterized query. Here is code

$order_ids = array(1, 2, 3);
$q = " SELECT * FROM orders WHERE order_store_id IN(?)";
$order_items = $this->db->query($q, array(implode(", ", $order_ids))  );

But it loads only Order with ID 1.

But if I write WHERE order_store_id IN(implode(", ", $order_ids)) it loads all Order perfectly.

I have also tried

$order_ids = array(1, 2, 3);
$q = " SELECT * FROM orders WHERE order_store_id IN(?)";
$in_val = implode(", ", $order_ids);
$order_items = $this->db->query($q, array($in_val )  );

But it also loads only Order ID 1

So how can I use parameterized query with IN clause?

Would the 'where_in()' function that as part of the 'active record' class be useful? Generates a WHERE field IN ('item', 'item') SQL query joined with AND if appropriate — Ryan Vincent
– Ryan Vincent, Commented Feb 18, 2015 at 16:52

mickmackusa · Accepted Answer · 2025-03-27 08:36:02Z

1

Do not implode your array.
Do not wrap your placeholder in parentheses -- CodeIgniter adds these automatically.
The first element of the second parameter of the query() call should be your flat array.

$order_ids = [1, 2, 3];
return $this->db
    ->query(
        'SELECT * FROM orders WHERE order_store_id IN ?',
        [$order_ids]
    )
    ->result();

Do not resort to the inefficient practice of executing a query withFIND_IN_SET().

Because your query is simply converted to (more elegant) active record syntax, I would encourage that.

$order_ids = [1, 2, 3];
return $this->db
    ->where_in('order_store_id', $order_ids)
    ->get('orders')
    ->result();

answered Mar 27 at 8:36

mickmackusa♦

49.3k13 gold badges98 silver badges165 bronze badges

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

Comments

Tpojka · Accepted Answer · 2015-02-17 16:08:50Z

0

Found this worked for someone:

$array = array("first", "second", "third");
$list = "'". implode("', '", $array) ."'";
echo $list; // Outputs: 'first', 'second', 'third';

You could test it.

answered Feb 17, 2015 at 16:08

Tpojka

7,0812 gold badges32 silver badges41 bronze badges

4 Comments

Tpojka Over a year ago

Can you check what is returned value if you use array(2,3,4)? Also, remove blank space from glue to remain ',' only.

Umair Ayub Over a year ago

Dear, I can easily get imploded string of array values even before your answer ... if you read my question, I asked that it is not working in parameterized query here $this->db->query($q, $xyz)

Tpojka Over a year ago

Just needed to know if is determined if that '1' is value from array or something like TRUE.

Tpojka Over a year ago

Use your first code with replaced array(2, 3, 4) and be sure it is array first element but no TRUE value in result. Dependently of server PHP version maybe you should initialize imploded string $string_from_array = implode(", ", $order_ids); first. Than, use it in code next line. Use back ticks for name of columns.

tunmyse · Accepted Answer · 2015-02-18 14:21:15Z

0

The query function treats the items passed to it accordingly, and in your case, the result of the exploded array is a string, so it is quoted, like this SELECT * FROM orders WHERE order_store_id IN('1, 2, 3'). When this happens, MySQL treats everything between the quote as one value and takes the first number, which is 1, every other thing is discarded. If you really want to go with CodeIgniter's query binding, use ',' as the glue in your implode statement, that way, all the items in the array gets quoted in the query SELECT * FROM orders WHERE order_store_id IN('1', '2', '3') or you could just implode the array into the IN clause without using query binding and not have to worry about the quotes.

answered Feb 18, 2015 at 14:21

tunmyse

765 bronze badges

Comments

Eddie · Accepted Answer · 2015-08-05 07:33:02Z

0

I used the FIND_IN_SET MySQL function instead to solve this problem.

For example:

$order_store_id_str = '1,2,3';

$query = "SELECT * FROM orders WHERE FIND_IN_SET(order_store_id, ?)";

$this->db->query(
    $query,
    array($order_store_id_str)
);

answered Aug 5, 2015 at 7:33

Eddie

1,0738 silver badges14 bronze badges

Comments

Ataboy Josef · Accepted Answer · 2015-02-17 12:35:25Z

-1

Prepared statements can be thought of as a kind of compiled template for the SQL that an application wants to run, that can be customized using variable parameters.

$order_ids = array(1, 2, 3);
$stmt = $dbh->prepare("SELECT * FROM orders WHERE order_store_id IN(?)");
$stmt->execute($order_ids);

If this can't help, refer details here.

answered Feb 17, 2015 at 12:35

Ataboy Josef

2,1113 gold badges24 silver badges28 bronze badges

1 Comment

Umair Ayub Over a year ago

I am using CodeIgniter BTW

Collectives™ on Stack Overflow

Use parameterized query with IN clause - CodeIgniter

5 Answers 5

Comments

4 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

4 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related