Yii2 anFilterWhere with comma string

Question

Suppose I have the data in table like this

ID     student_name     score
1      ABC              1,3
2      DEF              11,2
3      GHI              5,2,13

score : is the string

This is what I have tried

$query->andFilterWhere(['like', 'score', $this->score]);

what I want to do is when I search for "1" by "score" it should return the first record only,but it return all record. when I search for "2" it should return 2 rows(2 & 3)

Please help!!!

Can i ask why are you saving the data like this? All scores as a single string separated by comma ?! D: — Clyff, Dec 07 '15 at 13:37
Problem is, if you look for "1", you'll find all 3 entries. That's because "11" contains "1". You should alter your database structure. — Beowulfenator, Dec 08 '15 at 21:15

score 1 · Accepted Answer · answered Dec 10 '15 at 16:02

Change your logic so that while saving score, it saves like ,1,3, (Note the comma at the beginning and end)
Update score fields with comma at the end so update query will look like Update table set score = concat(',', score, ',')
$query->andFilterWhere(['like', 'score', ',' . $this->score . ',']);

Yii2 anFilterWhere with comma string

1 Answers1