Getting most relevant results when searching from multiple tables (php, mysql)

Question

I am searching from 3 tables currently (will search in more after sorting this out). This query brings all the results in the order of the tables listed in query. Whereas I want to get the most relevant search results first.

(Select name, url, text, 'behandlinger_scat' AS `table` from  behandlinger_scat where name LIKE '%KEYWORD%' OR text LIKE '%KEYWORD%')
UNION
(Select name, url, text, 'hudsykdommer_scat' AS `table` from  hudsykdommer_scat where name LIKE '%KEYWORD%' OR text LIKE '%KEYWORD%')
UNION
(Select name, url, text, 'om_oss' AS `table` from  om_oss where name LIKE '%KEYWORD%' OR text LIKE '%KEYWORD%')

Any help would be appreciated.

You'll need to figure out what makes a result more relevant and assign a score, then sort by relevance — Loopo, Mar 13 '14 at 07:04
possible duplicate of [Order SQL by strongest LIKE?](http://stackoverflow.com/questions/11144394/order-sql-by-strongest-like) — benomatis, Mar 13 '14 at 07:05

Shay Elkayam · Accepted Answer · 2014-03-13T08:23:26.483

3

You can use a method to order by the points you dynamically give the results, as in this example (you will need to alias your tables so SQL will understand what column you're referring to):

            ORDER BY 
            CASE WHEN name LIKE table.keywords THEN 100 ELSE 0 END +
            CASE WHEN name  LIKE table2.keywords THEN 10 ELSE 0 END +
            CASE WHEN text  LIKE table2.keyword THEN 1 ELSE 0 END
            DESC

This is merely an example, but the concept is the following:
You decide how many "points" each "match" will receive (e.g name matches keyword is 100 points, text matches it - a little less) then, each row "accumulates" points with correlation to its matches, and the row with the most points shows first.

edited Mar 13 '14 at 08:23

answered Mar 13 '14 at 07:23

Shay Elkayam

4,128
1
22
19

Thanks let me try this. – Syntax Error Mar 13 '14 at 07:32
OK! don't forget two important things: 1. THE "DESC" on the end (to order by the most relevant to the less relevant) 2. give different aliases to each table (change the "as table" you have now to "as table1,2,3". OK? – Shay Elkayam Mar 13 '14 at 07:35
but actually I can't give different aliases to the tables because I am checking table names and building URL on the basis of table name. Code is: ` $anc = 'http://www.example.no/'; if($table=='behandlinger_scat') $anc .= 'behandlinger'; elseif($table=='hudsykdommer_scat') $anc .= 'hudsykdommer'; elseif($table=='om_oss') $anc .= 'omoss'; ` – Syntax Error Mar 13 '14 at 07:44
I'm updating my answer for you with an example – Shay Elkayam Mar 13 '14 at 07:51
@OMI look at the full example. change it for your needs. – Shay Elkayam Mar 13 '14 at 07:55
Thanks shay let me try this. – Syntax Error Mar 13 '14 at 07:59
CASE WHEN '%KEYWORD%' LIKE table3.text THEN 10 ELSE 0 END + this is the last line. should we add "+" in the end ?? because in previous example you did added + in the last line. – Syntax Error Mar 13 '14 at 08:02
**Table 'behandlinger_scat' from one of the SELECTs cannot be used in global ORDER clause** MySQL error statement while executing the query :( – Syntax Error Mar 13 '14 at 08:06
I'll look into it for you – Shay Elkayam Mar 13 '14 at 08:10
found the problem. cant use table name in the super global order clause coz the mysql cant see the table names as the table names are in sub select :) – Syntax Error Mar 13 '14 at 08:13
made it work and the results are better than before now. thanks shay for the effort. I will make some more tweaks to make it more efficient in my way. thanks again for your time. – Syntax Error Mar 13 '14 at 08:14

Getting most relevant results when searching from multiple tables (php, mysql)

1 Answers1