match Comma Separated value store in database php

问题:

i have the project requirement where student can apply to the degree if there any subject matches with subject added by institute. here is the table structure

institute table                                 student table
----------------------------------            -----------------------------------
id | degree |       subject_required           id  |  subject_known
1  |   MS   |      maths,electronics,CAD        1    craft,drama 
2     BSC        chemistry,biology              2     maths
3     arts       craft,drama,dancing            3     cad,electronics

this is code i have written where institute added degree and subject_required

public function add_degree($institute_id){
$data=array(
'id' => $institute_id,
'degree' => $this->input->post('degree'),
'subject_required'=>$this->input->post('subject_required'),
);
return $this->db->insert('institute',$data);
}

and this code written when student 1st register himself

public function student_register(){
$data=array(
'id' => $institute_id,
'subject_known'=>$this->input->post('subject_known'),
);
return $this->db->insert('student',$data);
}

how do i match this subject requirement where if any one subject matches then student can apply and this subjects are separed by column

public function match_subject($id){
$sql='SELECT subject_required   FROM institute  WHERE id ='.$id;
$query=$this->db->query($sql);
foreach ($query->result() as $row)
     {
     $subject_required = $row->subject_required;
         $subject_required=$this->explode(array(',',' '),$subject_required);

     }

}

but i m not able to match the subject requirement.

回答1:

try with MySQL Regex

select * from institute where subject_required REGEXP '[[:<:]]chemistry[[:>:]]'

i hope this will help you