ok I have to search the table for some keywords and display the result in
best way(best matching first)
right now I am doing it this way
select Ind, ueberschrift,
(
(if(LOCATE('key1', LOWER(ueberschrift))>0,3,0))+
(if(LOCATE('key1', LOWER(einlitung))>0,2,0))+
(if(LOCATE('key1', LOWER(kurzzf))>0,4,0))+
(if(LOCATE('key1', LOWER(lauftext))>0,2,0))+
(if(LOCATE('key2', LOWER(ueberschrift))>0,3,0))+
(if(LOCATE('key2', LOWER(einlitung))>0,2,0))+
(if(LOCATE('key2', LOWER(kurzzf))>0,4,0))+
(if(LOCATE('key2', LOWER(lauftext))>0,2,0))+
0) as score
from meldung group by ind order by score desc
Ind is ID and the rest are text fields
now I have two problems first of all I am getting the results that have score 0
because (where score>0 is not working) I may not use field score ?
second I would like to do it with like '%key%' instead of locate I think would be faster,
but I am not shore,
because like is not case sensitive
any god suggestions?
thanks
