sql - query to get most matched likes first in mysql -

i have table like:

user :

uid | course_id | subjects --------------------------- 1   | 1         | html,php 2   | 1         | java,html,sql 3   | 1         | java 4   | 1         | fashion,html,php,sql,java 

i want run query can return liked subjects in query , second , on...

for example :

select * user subjects '%java%' or '%php%' or '%html%'; 

this query return data this:

uid | course_id | subjects --------------------------- 2   | 1         | java,html,sql 3   | 1         | java 4   | 1         | fashion,html,php,sql,java 

but want output :

    uid | course_id | subjects     ---------------------------     4   | 1         | fashion,html,php,sql,java     2   | 1         | java,html,sql     1   | 1         | html,php     3   | 1         | java 

so matched subjects 1st 2nd matched subjects , on.... there modification in query can type of sorted output.

never, never, never store multiple values in 1 column!

like see give headaches. normalize user table. can select normally.

it should this

uid | course_id | subjects --------------------------- 1   | 1         | html 1   | 1         | php 2   | 1         | java 2   | 1         | html 2   | 1         | sql 3   | 1         | java ... 

or better introduce new table subjects , make mapping table called course_subjects

subject id | name ------------ 1  | html 2  | sql 3  | java ...  course_subjects uid | course_id | subject_id --------------------------- 1   | 1         | 1 1   | 1         | 2 ...