search comma separated values in mysql using php

Question

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.

Note that searching this type of table will be very slow if the table is large. comma-separated values aren't indexed in any way that makes searching for items efficient. — Barmar
– Barmar, Commented May 1, 2016 at 11:44

Mark Baker · Accepted Answer · 2016-05-01 10:07:30Z

1

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))

edited May 1, 2016 at 10:07

answered May 1, 2016 at 10:05

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

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

4 Comments

Hara Prasad Over a year ago

in dynamic way like $a= "2,3,4"

Mark Baker Over a year ago

You'd have to split $a into an array of individual values, and build your query for each value in that array

Hara Prasad Over a year ago

I am getting values from form post sir.

Mark Baker Over a year ago

Then you build your query by iterating over the relevant values in $_POST... I've shown you what the query should look like.... I've also recommended that you normalise your database properly

Collectives™ on Stack Overflow

search comma separated values in mysql using php

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related