how to search in many columns?

Question

In my database there are one table and 6 columns in this table like that

----------------------------------
|id|word|translation|forms|translation_form|tags|
|4 |a   |b          |d,s,h|t,y,p           |b,o |
|2 |p   |a          |d,f,k|t,y,o           |v,i |
|0 |n   |br         |d,f,a|t,y,n           |u,i |
|1 |o   |br         |d,f,b|t,y,a           |u,g |
|3 |t   |br         |d,f,b|t,y,p           |a,g |


----------------------------------

search word is "a"

I want to search first in (word) and then in the others columns

and sort them like so first id 4 than 2 then 0 then 1 and then 3

I used

SELECT * FROM dictionary WHERE 
MATCH(`word`) against('a*' in boolean MODE) OR
MATCH(`translation`) against('a*' in boolean MODE) OR
MATCH(`forms`) against('a*' in boolean MODE) OR
MATCH(`translation_forms`) against('a*' in boolean MODE) OR
MATCH(`tags`) against('a*' in boolean MODE)

I get the results but not in order like I described how to sort them

If you really need to stick with this schema, then you should rather consider [Full-Text Search](https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html) and use weights for ordering. — Peter Wolf, Aug 09 '19 at 14:02

score 1 · Answer 1 · answered Aug 09 '19 at 13:33

1

I believe the following query should give you the desired result, but as mentionned in the comments by @treyBake, this seems like an inefficient way to use your database (and as a result, the query below will also be quite slow if you have alot of rows in your table)

SELECT *
FROM table
ORDER BY
   IF(word LIKE '%a%', 16,0)
  +IF(translation LIKE '%a%', 8, 0)
  +IF(forms LIKE '%a%', 4, 0)
  +IF(translation_form LIKE '%a%', 2, 0)
  +IF(tags LIKE '%a%', 1, 0) DESC

answered Aug 09 '19 at 13:33

Pepper

587
4
12

1

Just a note. Instead of "Bit Ordering" you can just `ORDER BY` multiple pseudo-columns, as in `if(word LIKE '%a%', 1,0) desc, if(translation LIKE '%a%', 1, 0) desc, if(forms LIKE '%a%', 1, 0) desc, if(translation_form LIKE '%a%', 1, 0) desc, if(tags LIKE '%a%', 1, 0) desc`. +1 – The Impaler Aug 09 '19 at 13:38
Good point, that would work too, and is probably easier to understand :) – Pepper Aug 09 '19 at 13:41
From the question it seems that the requirement is to match whole words rather than substrings. – Peter Wolf Aug 09 '19 at 14:04
thank you, I edited my question. – cordn Aug 09 '19 at 21:52

score 1 · Answer 2 · answered Aug 09 '19 at 14:21

In MySQL, you can use find_in_set():

where find_in_set('a', word) desc,
      find_in_set('a', translation) desc,
      . . .

The desc sort is because find_in_set() returns 0 if nothing is found and you want that to go last.

You should definitely consider revisiting your data model. Storing list of things in columns is definitely not the SQL-ish way to store values. You should be using separate junction tables, one for each of the list columns.

thank you, I edited my question. – cordn Aug 09 '19 at 21:52 — cordn, Aug 09 '19 at 21:52

how to search in many columns?

2 Answers2