Getting result for comma separated value with where condition

Question

I am having 2 columns both have values with comma keyword_whomtoteach and area_tutor when i am passing multiple values to $area and $keyword_whomtoteach i am not getting correct output i am using this query

     $toteach=1,5      $area=1,5
     $search  = "keyword_whomtoteach IN (".$toteach.")";
     $where = "area_tutor IN (".$area.")";

     $this->db->where($search);
     $this->db->or_where($where);
     $query=$this->db->get();
     return $result = $query->result_array();

here i am getting correct result but the problem is i need something like this if

id   keyword_whomtoteach             area_tutor
  1  1,2,3,4,5                         1,2
  2  2,3,5                             2,3
  3  5,6,7                             1,4
  4  1,5,6                             5,6

I want something like when i pass $toteach=1 and $area=1,5 at this point of time i should get both id 1 and 4 as both have keyword_whometoteach=1,5

Possible duplicate of [FIND\_IN\_SET() vs IN()](http://stackoverflow.com/questions/4155873/find-in-set-vs-in) — Matt Raines, Jun 06 '16 at 08:18
i have seen this example but i am not able to resove @matt raines — Sumit, Jun 06 '16 at 08:20
Why can't you normalize your data properly? It would eliminate the need to find complex and inefficient workrounds — Mark Baker, Jun 06 '16 at 08:24
Without sample data, expected results and actually received results or error message, any answer can only be a pure guesswork. — Shadow, Jun 06 '16 at 08:25
Now it would be very tough to normalize because in DB its full of comma related values is there a way to resolve this issue ? — Sumit, Jun 06 '16 at 08:26
You have two options for "Resolution of the issue".... option #1 is to normalize your database properly; option #2 is to attempt kludgy and inefficient workrounds (like FIND_IN_SET())..... but you'll never get good performance and your code will always be excessively complex and hard to maintain unless you use option #1 — Mark Baker, Jun 06 '16 at 08:31

score 0 · Answer 1 · edited May 23 '17 at 12:15

You should replace the column_name IN('1,5') syntax with FIND_IN_SET(1, column_name) AND FIND_IN_SET(5, column_name) or FIND_IN_SET(1, column_name) OR FIND_IN_SET(5, column_name) (depending on the desired result).

However, your schema is not a good way to store this data. It would be standard practice to create a second table and store the comma-separated list in this table. Read Is storing a delimited list in a database column really that bad? for more information on how and why.

You also have PHP syntax errors ($toteach=1,5 $area=1,5 should be $toteach='1,5'; $area='1,5';) and your MySQL query requires quotes around $toteach and $area.

i just gave demo data ok i will go through this link – Sumit Jun 06 '16 at 08:30 — Sumit, Jun 06 '16 at 08:30

Getting result for comma separated value with where condition

1 Answers1