SQL in 'services' column I have an arrays values like e.g.: 2,19,72,5,46,45,38,40,36,27,29
For selecting this row from DB I'm using this query:
SELECT * FROM myTable WHERE `services` IN (2,19)
And it is working OK. But, when I do changes in 'IN' closure like below:
SELECT * FROM myTable WHERE `services` IN (19)
it always returns 0 rows. The query is searching only for the first value in column. Why this is happening and how can I change my query to work it correctly?
Thanks in advance.
Update your query like : If you search only single value...
SELECT * FROM myTable WHERE concat(',',`services`,',') like '%19%'
But if you want to search 19,2 or 2,40 or 40,19 it will not work. for that case you need to write query dynamically
Use below code:-
$recordsArr = [2,19,72,5,46,45,38,40,36,27,29]; // Your array
$records = implode(',', $recordsArr); // Your string
$sql="SELECT * FROM myTable WHERE services IN( $records )"; // Your query
Hope it will help you :-)
Your data is stored as TEXT, so you need to do this:
SELECT * FROM myTable WHERE `services` LIKE '%,2,%' OR `services` LIKE '2,%' OR `services` LIKE '%,2' OR services` LIKE '%,19,%' OR `services` LIKE '19,%' OR `services` LIKE '%,19'
This is very slow and will get complicated very fast. You should find another way to store your data.
SELECT * FROM myTable WHERE FIND_IN_SET('$array','services')