[SOLVED] Select single found result with MATCH AGAINST

Select single found result with MATCH AGAINST

I am trying to get a single result from a table row

I'm using this query

"SELECT * FROM $tableName WHERE MATCH(nombre, mat1, mat2, mat3, mat4, mat5, mat6, mat7) 
AGAINST ('$busqueda' IN BOOLEAN MODE) 
ORDER BY nombre";

This obviously selects all the fields of the row if $busqueda is found in any of the fields.

Is there a way to select only the field where the search term is found?

Solution

The MATCH(nombre, mat1, mat2, mat3, mat4, mat5, mat6, mat7) acts as a single index field, so when some text matches against it, it does not have a facility to tell you which bit.

You can still match against the full spread for speed of matching like this, and use individual MATCH() calls against each field in the the field list (after the SELECT in place of *) checking the relevance for a certain level in order to detect which field was matching.

SELECT
   IF(MATCH(nombre) AGAINST ('search phrase' IN BOOLEAN MODE) > 0.9, nombre, NULL) AS nombre,
   IF(MATCH(mat1) AGAINST ('search phrase' IN BOOLEAN MODE) > 0.9, mat1, NULL) AS mat1
   /* etc */
FROM myTable
WHERE MATCH(nombre, mat1, mat2, mat3, mat4, mat5, mat6, mat7) 
      AGAINST ('search phrase' IN BOOLEAN MODE) 
ORDER BY nombre

NULLs will be returned for fields that do not match, note that you may well want FULLTEXT indexes on the individual fields too. It is not an absolute requirement if using BOOLEAN MODE if I remember correctly.