Mysql sortation on multiple LIKE's -

there lot of topics on sortation (like: order results occurrence) these 1 value.

i have search field people use keywords; said queries generated like:

1 word:

select *   `meta`  (`keywords`  '%bike%') 

2 words:

select *   `meta`  ( `keywords`  '%bike%' or  `keywords`  '%yellow%' ) 

etc...

what sort result on found keywords. how unknown amount of keywords like's

here general way sort number of keyword matches in mysql (using like):

select *  `meta`  order ((`keywords`  '%bike%') +           (`keywords`  '%yellow%') +           . . .          ) desc; 

if want handle flexible number of keywords, should use appropriate relational data structure. storing keywords in single field (probably comma-separated) not best approach. should have separate table 1 row per keyword.

edit:

to add in number of keywords found, expression can put in select statement:

select m.*,        ((`keywords`  '%bike%') +         (`keywords`  '%yellow%') +         . . .        ) numkeywordsfound `meta` m order numkeywordsfound desc; 

you can add having clause specify @ least 1 found:

select m.*,        ((`keywords`  '%bike%') +         (`keywords`  '%yellow%') +         . . .        ) numkeywordsfound `meta` m having numkeywordsfound > 1 order numkeywordsfound desc; 

if want find number of times keyword found in each expression:

 select m.*,         length(replace(keywords, 'bike', 'bike1')) - length(keywords) numbikes,         length(replace(keywords, 'yellow', 'yellow1')) - length(keywords) numyellows `meta` m