I have an oracle sql statement.
table data like this:
FORM_NO | PART_NO | CHUTTER_ID
1 | ABC | 1,3,9,12,15
I have PHP variable
$get_q = "3";
Now I want to query:
$q = oci_parse($c1, "SELECT * FROM tb_test WHERE CHUTTER_ID IN '$get_q'");
oci_execute($q);
The result of query is empty.
Anyone know, how to solve this?
You could write the test like this:
"SELECT * FROM tb_test WHERE ',' || CHUTTER_ID || ',' LIKE '%,$get_q,%'"
This appends commas to the value of CUTTER_ID so that it becomes something like:
,1,3,9,12,15,
Then it checks that the above matches this pattern:
%,3,%
where a % represents 0 to any number of characters. The additional commas are needed to make sure also 1 and 15 would be found with this method.
Your query is invalid because the IN operator requires the right argument to be a bracketed list, like so (3), without quotes. But even if you would do that, you would get this expression:
CUTTER_ID IN (3)
which is like saying:
'1,3,9,12,15' IN (3)
But that does not make sense. You'd want to test the opposite:
3 in (1,3,9,12,15)
But alas, the database field CUTTER_ID is not suitable for such a construct. Luckily, the alternative with LIKE can work.
Please note that if the value '3' in your example is derived from user input, then your code is vulnerable to SQL injection. To avoid this, bind the value via oci_bind_by_name, like this:
$q = oci_parse($c1, "SELECT * FROM tb_test
WHERE ',' || CHUTTER_ID || ',' LIKE '%,' || :p1 || ',%'");
oci_bind_by_name($stid, ':p1', $get_q);
oci_execute($q);
Finally, a list of values in one database field is not a sign of a good database model. This is an example of a database that is not normalised. It does not even meet the first normalisation form.
If you have control over the database design, please consider changing it as suggested in the article linked above. It will improve the speed of your queries.
