How to do a search on the site by keywords (query in the database)?

Question

PHP

I have a table in a database with products.
Each product has a keywords field By which it can be searched, for example "beer, amstel, amstel"
I make a query WHERE keywords LIKE '%searsh%'
Where search is the user's request.
It works fine if the user enters "beer", "amstel", and for example, if you enter "amslel beer", then naturally it will not find it.
How to do it better?
So far, 2 ideas came to mind, do a lot of LIKE depending on how many spaces are in the search string
Or take the entire database table and parse using PHP already

Answer 1

another VALASPED? look for mysql fulltext search - and you will be happy.
By the way
1. used to work only in Maisam. something around 5.5-5.6 was also screwed on inno.
2. by default, the minimum length of the string, on which it seems to be looking for 4 characters ("beer" is found, "bat" is no longer there). Edit
[mysqld]
ft_min_word_len=3,
after which the index needs to be rebuilt.

Answer 2

Essentially, do a lot of likes.
I advise you to use Sphinx or Elastic. Especially if you have goods - in the online store, a convenient search is very important.
If you fail to install the first time or you do not have VDS - indexden.com . This search is powered by LinkedIn + service is free (up to 15,000 entries in the index).
0. Register
1. Learn how to make requests via REST Api (if you don’t know how yet)
- editing and deleting the index (it’s convenient to do it through Postman if you have chrome)
- adding it separately, here you can write a script and even a gui to it, but I I just added new products to the database, displayed them with a php cycle, copied and pasted them into the terminal (lots and lots of curl commands turned out)
- requests to the index from the site, search scripts ( example for php )
2. Suffer, set up, google. There's a lot on the toaster too.
3. Slowly study the installation and configuration of sphinxsearch, buy a dedicated server, and when the value of the index entries approaches 15 thousand, transfer it to your server.