0

I saw this question so now I have an idea as to what the problem is.

Somehow I don't know the solution though.

I'm doing this query:

SELECT *
FROM my_table t
WHERE t.category_id = :category_id
AND t.group_id IN (:group_ids)

With the above query I'm binding these variables:

oci_bind_by_name($stmt, ":category_id", $category_id, OCI_B_INT);
oci_bind_by_name($stmt, ":group_ids", $group_ids);

Notice that for the group_id column I need to check whether the value is in the list provided like so:

$group_ids = implode(", " array_filter($list_of_groups)); // could be 0 or 12345 or 12345, 67890

Note

I didn't use OCI_B_INT when I bound the $group_ids to :group_ids because I'm using IN.

Additional Information

So somehow the stuff works when the value of $group_ids is just one ID but if I have multiple IDs separated by commas then the query throws the error.

Community
  • 1
  • 1
dokgu
  • 4,957
  • 3
  • 39
  • 77
  • Did you have a chance to spot such an erroneous scenario - and run the generated sql manually, to test the validity of the sql statement itself ? – Veverke Aug 09 '16 at 15:19
  • @Veverke I don't think there's a problem with the query itself it's just doing something like `t.group_id IN (1234, 5678)`. The link @LucM provided seems to point out that doing a bind for those comma separated values do not work. – dokgu Aug 09 '16 at 15:22
  • Can all ids in `$group_ids` be bound as one variable to the parameter `:group_ids`? – BeetleJuice Aug 09 '16 at 15:22
  • 1
    placeholders can only represent SINGLE values. passing in multiple CSV values to a single placeholder will never work. `where foo IN (?)` will execute as the equivalent of `where foo=?`, because the placeholder is treated as a monolithic single value, not a list of separate values. e.g. it'll be become `where foo in ('1,2,3')` and execute as `where foo='1,2,3'` (note the `'` quotes). – Marc B Aug 09 '16 at 15:23
  • @MarcB so what do I need to do here then? – dokgu Aug 09 '16 at 15:23
  • build your query dynamically, putting in a placeholder for every value, then bind them all at the same. time. e.g. array with 4 values will need `where foo in(?,?,?,?)` – Marc B Aug 09 '16 at 15:24
  • @BeetleJuice That's my question as well. I'm hoping that I won't have to define variables for each. With my case I have a maximum of 6 IDs to check it with but imagine when there's no limit. – dokgu Aug 09 '16 at 15:25
  • @MarcB is that really the best solution? I was hoping I wouldn't need to do something like that. – dokgu Aug 09 '16 at 15:27
  • nope. `in (...)` clauses are one place where placeholders are outright moronically stupidly ugly to use. – Marc B Aug 09 '16 at 15:28
  • @MarcB looks like I can use [oci_bind_array_by_name](http://php.net/manual/en/function.oci-bind-array-by-name.php) but will have to `explode` first. – dokgu Aug 09 '16 at 15:30
  • @PatrickGregorio if the IDs are numbers, you can cast them as such with `foreach($list_of_groups as &$g) $g=(int)$g` Then you're sure that the values are integers, so you can `implode` and insert them in the query without the need for parameters. – BeetleJuice Aug 09 '16 at 15:31
  • The **Example 1 - call from PHP using collections** section in [this answer](http://stackoverflow.com/a/18091842/4955425) seems very promising. `"SELECT * FROM myTable where value in (select column_value from table(:key_list))"`. That's what I would try if I were you. – sstan Aug 09 '16 at 15:40
  • @sstan Yeah it does. Although I went with MarcB's solution and I made it work. – dokgu Aug 09 '16 at 16:05

0 Answers0