Hello. A question. The database has a table tovar . There is a cell name in this table. There are recorded the names of the goods. I want to do a search for products.

The fact is that the name of the product can be kind of coal, black or black coal, raw .

And if you make a selection from the database by product name, when you enter the word raw coal - you will not find anything.

What query can I select from the database with the coincidence of at least one word in the name cell of the tovar table?

I would be very grateful for any useful information.

  • if head-on, then name like '%сырой%' or name like '%уголь%' or use 'full-text search' (in Google and drive in) - Mike
  • @Mike will not do that. Suppose that the words for the query go in the variable $ abc = "raw coal"; - iKey
  • And you line into words break explode or preg_sprit and create a query based on them. - Mike

1 answer 1

If a single word matches, then

 $str = "слово для поиска"; // осторожно! необходима экранизация $query = "SELECT * FROM tovar WHERE tovar.name LIKE '%$str%'";

If for several, in this case

 $a = 'сырой уголь купить'; // поисковая строка $vars = explode(' ', $a); // получаем массив из слов $combine = array_map(function($elem) { // добавляем оператор LIKE return "tovar.name LIKE '%$elem%'"; }, $vars); $query = "SELECT * FROM tovar WHERE " . implode(' OR ', $combine); // генерируем запрос var_dump($query);

Result

 SELECT * FROM tovar WHERE tovar.name LIKE '%сырой%' OR tovar.name LIKE '%уголь%' OR tovar.name LIKE '%купить%'

What problems can be:

  • It will take a long time to look in the InnoDB tables.
  • If there are a lot of common words in the titles, then it will not find what is necessary.
  • There is no screen adaptation, it is necessary to filter the incoming line in order not to get a SQL-инъекцию
  • If the string is empty, there will be an error in the SQL query.

For such purposes, you must use search engines like ElasticSearch and SphinxSearch

  • and if two or more words match, this query fits? - iKey
  • @Denis, you can check this query in MySql, if the word is уголь , it will find all the records where уголь will meet in name - Stanislav Grotto