I already managed to bring the Categories in which at least found (1) a word, regardless of the order (in a MyiSAM table with Fulltex index) ...... Now the problem is knowing the total of coincidences that had in each record or the one that had the most words. That in this particular case of the photo is the last ...
The SQL is:
SELECT id,nombre FROM prod_categoria2 where match(nombre) against ('altavoces Bose ultima 5 generacion latinoamerica colombia' in boolean mode)
The query shows you by default the record with the most occurrences of first.
So by setting a LIMIT 1 you'll get only the record that had the most occurrences.
Now, knowing how many occurrences there was of each impossible thing is not, but difficult yes.
In the example (query 2) a ranking of the number of occurrences in each row is shown. On that basis, a calculation would have to be made.
The documentation explains more or less how to do with a single word and is somewhat complicated. It will be much more complicated in the case of several search criteria. I suggest you review the section: Relevancy Rankings for InnoDB Boolean Mode Search . Another difficulty is that calculations with the MyISAM engine appear to be less secure.
Here is the test code.
Query 1 shows the record that had the most occurrences.
Query 2 shows a calculated occurrence score.
I hope it serves you.
Código: Ver Demo
CREATE TABLE test_20170805 (
id SERIAL,
dato VARCHAR(70)
);
ALTER TABLE test_20170805 ADD FULLTEXT(dato);
INSERT INTO test_20170805 (dato)
VALUES
('Altavoces'),
('Cables de Altavoces'),
('Altavoces Colombia'),
('Bose Carlos Doris'),
('Generaciones Altavoces'),
('Última Bose Colombia')
;
-- Obtener la que tiene más ocurrencias usando LIMIT 1; --
SELECT id, dato FROM test_20170805
WHERE MATCH (dato)
AGAINST ('altavoces Bose ultima 5 generacion latinoamerica colombia' IN BOOLEAN MODE)
LIMIT 1;
-- Obtener un ranking ordenado de mayores a menores ocurrencias --
SELECT id, dato,
MATCH (dato) AGAINST ('altavoces Bose ultima 5 generacion latinoamerica colombia' IN BOOLEAN MODE) AS score
FROM test_20170805 ORDER BY score DESC;
Resultado
--Consulta 1
id dato
6 Última Bose Colombia
--Consulta 2
id dato score
6 Última Bose Colombia 1,06080877780914
3 Altavoces Colombia 0,258652836084366
4 Bose Carlos Doris 0,227644696831703
1 Altavoces 0,0310081318020821
2 Cables de Altavoces 0,0310081318020821
5 Generaciones Altavoces 0,0310081318020821