How to do a search in MYSQL considering plural and singular

Question

I have a recipes site with a simple search system. But there are recipes that are plural and recipes in singular, for example:

chocolate biscuit and chocolate biscuit

When someone looks for a chocolate biscuit, the select only looks for what is a chocolate biscuit, is there any way I can make it for them Can I bring the chocolate biscuits? (plural)

My select is like this:

SELECT titulo, foto
FROM receitas
WHERE (titulo LIKE '%{$_GET['termo']}%' OR descricao LIKE '%{$_GET['termo']}%')

And the search is a simple input:

<form method="get">
  <input name="termo">
</form>

Answer 1

Whoa, well come on.

The way I would do it is a little bit laborious, but in theory it would work, but you would have to structure a little better with PHP.

I would start treating the term that was typed by the user, removing some characters from the end (ai varies from what you need, 2,3,4 characters...)

//Exemplo iniciando no caracter 0 e retirando o 1 CHAR da string (final do termo):
$filtro = substr($_GET['termo'], 0, 1);

Of course, the example above, you would have to improve a little by separating the words also by the spaces (I estimate that the users of your site search for CHOCOLATE COOKIE, then in this case it would be 3 variables at least to separate the terms, and then you could concatenate in a variable the strings already treated and use it as a search parameter in your SQL.

After adjusting, you would start the query

-- Buscando por elementos que contenham a palavra dentro da variavel $filtro
SELECT titulo, foto
FROM receitas
WHERE (titulo LIKE '%$filtro%' OR descricao LIKE '%$filtro%')

Or if you prefer, you can search for terms that contain words that start with a specific character

-- Buscando dados que comecem com qualquer caracter e finalizem com o $filtro
    SELECT titulo, foto
    FROM receitas
    WHERE (titulo LIKE '_$filtro%' OR descricao LIKE '_$filtro%')

Or, check if the search INITIAL term contains the filtered characters and any other random characters that may exist in your database:

-- Buscando dados que comecem com $filtro e terminam com qualquer caracter
        SELECT titulo, foto
        FROM receitas
        WHERE (titulo LIKE '$filtro_' OR descricao LIKE '$filtro_')

I believe that option 3 is the most viable for your case, but if it is not yet what you need, you can consult these materials that I used as a basis: Source: https://www.devmedia.com.br/usando-os-operadores-like-in-e-between-no-oracle/24687 Source: https://www.php.net/manual/en/function.substr.php

Something important you can do is this. If the user has typed 3 or + words, do the search in your SQL with more parameters passing all possible strings to refine the search with all words contained in the term.

I hope to help, share with us the result!

Answer 2

The ideal in this case is to use the FULLTEXT search method instead of LIKE.

Vala point out that this will not fetch the broken words, IE, he continues to search for words but what will define whether it will be displayed or not the result is the set, the sentence in the case. In the example you used, as the two records contain the word 'chocolate' they will be displayed, regardless of biscuit being in the singular or plural.

To use this feature you need to make changes to the database structure and add the fulltext indexes in the columns you want to perform the search, nothing too complicated. I’ll give you an example.

I will create a table just to test the result: Note that I have already created fulltext indexes in the columns where I want to perform searches

CREATE TABLE receitas
(
    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    titulo VARCHAR(200),
    corpo TEXT,
    FULLTEXT (titulo, corpo)

);

Now I’ll add some similar singular and plural fields:

INSERT INTO receitas (`titulo`, `corpo`) VALUES ('biscoito de chocolate', 'descricao da receita');
INSERT INTO receitas (`titulo`, `corpo`) VALUES ('biscoitos de chocolate', 'descricao da receita');
INSERT INTO receitas (`titulo`, `corpo`) VALUES ('cookies de chocolate', 'descricao da receita');
INSERT INTO receitas (`titulo`, `corpo`) VALUES ('cookie de banana', 'descricao da receita');

Query search. I put a variable to simulate your:

set @pesquisa := 'biscoito de chocolate';

SELECT titulo, corpo FROM receitas 
    WHERE MATCH(titulo) AGAINST (@pesquisa in natural language mode);

This search will return the two cookies, both singular and plural, but will also return the 'chocolate cookies'. MATCH returns a relevant calculation of your search, which is very cool for you to sort the results for example. I’ll give you an illustrative example just so you understand how it works:

SELECT titulo, corpo,  MATCH(titulo, corpo) AGAINST (@pesquisa in natural language mode) AS score
FROM receitas
    WHERE MATCH(titulo) AGAINST (@pesquisa in natural language mode)
        ORDER BY score DESC;

But even more interesting is when creating tables of this type already create a column with keywords, which the user fills when entering the record in the database, so it would put the words in the singular in the plural and add any keyword that is relevant to the search. Then just direct the search to that column of Keywords. This would create a much more powerful search system for your system.