CODEWITHSUNDEEP
phpmysql
Hara Prasad
Hara Prasad

Reputation: 724

search comma separated values in mysql using php

I am having a bit of a problem running a select query on a database. Some of the data is held as a list of comma separated values, an example:

Table: example_tbl

| Id | standardid | subjectid  |
| 1  | 1,2,3    |  8,10,3  |
| 2  | 7,6,12   |  18,19,2 |
| 3  | 10,11,12 |  4,3,7   |

And an example of the kind of thing I am trying to run:

select * from table where standardid in (7,10) and subjectid in (2,3,4)
select * from table  where FIND_IN_SET(7,10,standardid)  and FIND_IN_SET(2,3,4,subjectid)

Thanks in advance for anything you can tell me.

Upvotes: 0

Views: 812

Answers (1)

Mark Baker
Mark Baker

Reputation: 212412

comma separated values in a database are inherently problematic and inefficient, and it is far, far better to normalise your database design; but if you check the syntax for FIND_IN_SET() it looks for a single value in the set, not matches several values in the set.

To use it for multiple values, you need to use the function several times:

select * from table
 where (FIND_IN_SET(7,standardid)
     OR FIND_IN_SET(10,standardid))
   and (FIND_IN_SET(2,subjectid)
     OR FIND_IN_SET(3,subjectid)
     OR FIND_IN_SET(4,subjectid))

Upvotes: 1

Related Questions