There is a table with a list of customers. There are about 22 fields.

When displaying a list there are filters by them ... And one of the filters is a search field. Enter the text and look for.

Now I do it like this:

$search = mysql_real_escape_string($_POST['search']); if($search !=""){$query_text="AND (`org_name` LIKE '%$search%' OR `sfera` LIKE '%$search%' OR `d_fio` LIKE '%$search%' OR `d_phone` LIKE '%$search%' OR `d_email` LIKE '%$search%' OR `d_birth` LIKE '%$search%' OR `k_fio` LIKE '%$search%' OR `k_phone` LIKE '%$search%' OR `k_email` LIKE '%$search%' OR `k_dolzhnost` LIKE '%$search%' OR `k_birth` LIKE '%$search%' OR `inn` LIKE '%$search%' OR `kpp` LIKE '%$search%' OR `ogrn` LIKE '%$search%' OR `ur_adress` LIKE '%$search%' OR `rasch_schet` LIKE '%$search%' OR `bank_name` LIKE '%$search%' OR `bank_adress` LIKE '%$search%' OR `koresp_schet` LIKE '%$search%' OR `bik` LIKE '%$search%' OR `fax` LIKE '%$search%')";} mysql_query("SELECT * FROM clients WHERE id!='' $query_text");

But you yourself probably understand how it is not right (Maybe you can somehow optimize it?

  • concat_ws(',', org_name, sfera, d_fio, d_phone, d_email, d_birth, k_fio, ....) like '%$search%' - instead of ellipsis, other fields. If the comma is found in the pattern, put another separator. - alexlz

1 answer 1

In your case, it is wiser to use full-text search, then the query will look like this:

 SELECT * FROM clients WHERE MATCH(org_name,sfera,d_fio,d_phone**все поля через запятую**) AGAINST ('$search')

Instruction here: Full-text search in MySQL

  • This is only if the MyISAM table. InnoDB tables do not support FULLTEXT indexes. - KiTE
  • @KiTE for full-text search a bunch of conditions in addition to the type of tables, and therefore inserted a link. - FLK
  • The link describes examples of using MATCH and AGAINST. And MATCH and AGAINST are applicable only to FULLTEXT. Alternatively, use the plugin [sphinx] [1] [1]: ru.wikipedia.org/wiki/… - KiTE
  • @KiTE and about the fact that fields must have a FULLTEXT index is also written, as well as about restrictions, but agree that it is worth it, do not concatenate the same fields separated by commas. and for optimization, full-text search, in my opinion the best option and faster and more convenient. - FLK
  • I am not saying what is good and what is bad; what it costs to use, and what it does not. I say that if a person has a table in InnoDB, he cannot use the example from the answer. - KiTE