SQL如果值与多列相似，请选择该列

Question

I need to create a autocomplete search with ajax. The suggestions should only contain the 10 most entered results. The search query has to check multiple columns if the value is like my variable.

But my problem is to create the query and the php logic for that.

1. Is there any plugin or something simular for that?
2. How can I select a column if the value in it is like my variable?
3. I need to create a count query, which counts (in all columns) "how often is here the full word (splitted by spaces)" <- which is like the found one (to get the relevance)

At the end I need to sort the found entries by their relevance to provide the 10 most relevant entries.

(The real query checks for more columns than just 2, but for dummy reasons are 2 okay)

The query which selects the rows where the value is like...

select * from 
(
    (select department from entries where department like '%myVariable%') 
OR 
    (select grade from entries where grade like '%myVariable%')
)

I think you know what I mean. Does anyone have any hints, suggestions, examples or useful links for me?

Thanks in advance!

Best regards,
FriaN

Answer 1

Why not use union all here?

select department from entries where department like '%myVariable%'
union all
select grade from entries where grade like '%myVariable%'

Then this should order the results for you:

select department, count(*) cnt from (
select department from entries where department like '%myVariable%'
union all
select grade from entries where grade like '%myVariable%')a
group by department
order by count(*) desc