Search with LIKE or MATCH.. AGAINST in two columns

Question

I have a classified system where I do a search with PHP and Mysql using LIKE in two camps:

campo1 like '' or campo2 like ''

The problem is when I’m looking for something like, "Green car" it doesn’t display anything because "car" is contained in field 1 and "green" in field 2, or even car comes with a word in the middle before "Green".

Could someone give me a tip on how to improve this search?

I tried to use:

SELECT description, title , MATCH ( title, description ) AGAINST ( '$busca' ) AS Score    
FROM qtc_ads WHERE MATCH ( title, description ) AGAINST ( '$busca' )
ORDER BY Score DESC LIMIT 50

However Mysql informs me that the fields do not support this type of search and I do not know if this is the best way.

SQL that I currently use to search on my system:

SELECT * FROM `qtc_ads` AS `qtc_ad` WHERE `status` = 1 AND `title` LIKE '%l200%' (1)

Answer 1

A somewhat laborious possibility, but with a more complete result, is to use PHP to divide your search in separate words, and generate the WHERE clause for you:

<?php

   $pesquisa = 'carro verde amassado ';

   // Aqui você pode juntar vários campos no concat.
   $campo = 'CONCAT( title, " ", description, " ", author)';
   // Ou usar um só, mas nesse caso talvez compense um LIKE tradicional
   // $campo = 'title';

   $palavras = explode( ' ', $pesquisa ); // dividindo as palavras pelo espaço
   $palavras = array_filter($palavras); // eliminando ítens vazios

   $where = '';
   $cola = 'WHERE ';

   foreach ($palavras as $palavra) {
      $palavra = trim($palavra); //Removendo espaços em branco
      $palavra = mysql_real_escape_string($palavra); //Precisa da conexao com o banco!
      $where .= $cola.campo.' LIKE "%'.$palavra.'%" ';
      $cola = 'AND ';
   }

   echo htmlentities( $where );
?>

And the result will be:

WHERE
   CONCAT( title, " ", description, " ", author) LIKE "%carro%" AND
   CONCAT( title, " ", description, " ", author) LIKE "%verde%" AND
   CONCAT( title, " ", description, " ", author) LIKE "%amassado%"

(line breaks added for easy reading)

This way the search will find all these results:

O carro amassado era verde
A carroça tinha abacates verdes amassados
Verde carro, amassado tu és

Note that although some lines do not have exact results, it is better to have more things than the user does not find what they need. Keep in mind, however, that the price you pay for complexity is slower research. LIKE and indexes do not work well together.

Answer 2

My opinion, is to have only one LIKE in your query, and you can still maintain two fields in your system, where a field would be the object ("Car", "Bike", ..., ...) and in the other field would be a feature ("Green", "Old", ..., ...), then when consulting, vc would concatenate these two fields to put in your query.

Ex: value_um = object; value_two = characteristic; value_query = value_one + value_two LIKE field "' + query value_query + "'%";

Or leave it as it is and use the percentage (%) in the like on both sides, that you will surely find something either in field one or field two.

hope q help.