在Codeigniter，MySQL中没有用LIKE搜索我想要的东西

I have the followings examples in be_user_profiles.subject. These are subject ids which each teacher teaches.

1// English
1,2 // English and Math etc
1,2,14
2,4,114
12,24,34
15, 23

I want to select where be_user_profiles.subject has 1. When I use the following, it outputs all which has 1 in it. So it will outputs all. I tried HAVING but it picks up only exact matches. So it shows only the first one. How can I pick up data which has the be_user_profiles.subject?

    $this->db->select('*');
    $this->db->from('be_user_profiles');
    $this->db->join('be_users', 'be_users.id = be_user_profiles.user_id');
    $this->db->where('be_users.group', $teachergrp);
    $this->db->like('be_user_profiles.subject', $subjectid);
    //$this->db->having("be_user_profiles.subject = $subjectid");// this picks up only exact match 
    $query = $this->db->get();

Thank you in advance.

be_user_profiles table

row1: 1,2,14

row2: 2,4,114

row3: 12,24,34

row4: 15, 23

To get data with exact match use this query

$this->db->query("
      SELECT * FROM `be_user_profiles` 
      WHERE subject LIKE '1'

      UNION

      SELECT * FROM `be_user_profiles` 
      WHERE subject LIKE '1,%'

      UNION

      SELECT * FROM `be_user_profiles` 
      WHERE subject LIKE '%,1,%'

      UNION

      SELECT * FROM `be_user_profiles` 
      WHERE subject LIKE '%,1'

   ");

The both clause that you put into the like query means to add % widcard in front and after of the string to search, so it returns 1 as long as 12, 21, 121 etc. If you remove it it will search only for exact match.

You could add this like clause and add commas to it and i think that it will work. Try to add this instead of the like you have now:

$this->db->like("," . "be_users.group" . "," , "," . $subjectid. "," , both);

I think you can use a regex pattern here.

$pattern = "(^|.*,)1(,.*|$)";
...
...
$this->db->select('*');
....etc
$this->db->where("be_user_profiles.subject REGEXP $pattern");

This regex pattern assumes that there are no spaces in the comma string.

However, as @halfer said in the comments you really, really should split this out into a "teachersubject" table with teacherid and subjectid columns otherwise it will bite you in the backside very, very soon. [Been there, done that :-) ]

eg Imagine trying to expand the above into searching for a teacher that teaches ((maths or physics) and English). Nightmare!