Concat() in mysql query and full text

Question

The syntax is killing me! the next query is having problems:

$cadbusca="SELECT * , MATCH (nombre, apellido, email, about, place, CONCAT(nombre,' ',apellido)) AGAINST ('$busqueda') AS Score FROM user WHERE MATCH (nombre, apellido, email, about, place, CONCAT(nombre,' ',apellido)) AGAINST ('$busqueda') ORDER BY Score DESC";

Error:

Query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(nombre,' ',apellido)) AGAINST ('dan stern') AS Score FROM user WHERE MATCH (nom' at line 1.

"dan stern" are the words searched...

Answer 1

To perform a fulltext search using MATCH/AGAINST, you must have a full-text index on the fields you want to do the search on. See match/against doc.

Having said that, you're trying to include a dynamic entity, CONCAT(nombre,' ',apellido), in your MATCH, which is not allowed -- you can't have an index on a dynamic field like that.

From the Full-Text Restrictions doc:

The MATCH() column list must match exactly the column list in some FULLTEXT index definition for the table, unless this MATCH() is IN BOOLEAN MODE. Boolean-mode searches can be done on nonindexed columns, although they are likely to be slow.