哪里 field1 + field2 + field3 LIKE “%my_own_string%"? [英] Where field1 + field2 + field3 LIKE "%my_own_string%"?

问题描述

我有一个字符串，比如 Elton 1999，还有一个包含 4 个字段的 MySql 表:

table(id, name, surname, year)

我必须检查，将这些字段作为单个字段"加入时，它是否包含我的字符串中的所有单词.

我的意思是:

<前>1 Elton John 1999 -> 必须返回记录2 Elton Peter 1999 -> 必须返回记录3 巴黎埃尔顿 1999 -> 必须返回记录4 Elto John 1999 -> 不得返回记录5 Elton Pierre 2000 -> 不得返回记录

我可以直接用 MySql 来做，还是我需要先获取所有记录然后在服务器端解析它们?(在 PHP 上，就我而言)

附言如果我的字符串是 1999 Elton，则必须开始相同的结果，所以我的单词顺序无关紧要...

解决方案

SELECT * FROM myTbl WHERE CONCAT(name, surname, year) LIKE '%ELTON%1999'

或

SELECT * FROM myTbl WHERE CONCAT_WS(' ', name, surname, year) LIKE '%ELTON%1999'

在每个字段之间添加一个空格.Thx majic bunnie.

更新:好吧，如果您想以任意顺序搜索具有值字符串的任何列上的可能匹配项，那么您可以按空格展开字符串并单独搜索每个单词.看看这是否有效

$query = "SELECT * FROM myTbl";if(!empty($searchString)){$query .= " WHERE ";$arr = expand(' ', $searchString);foreach($arr as $word)$fields[] = "CONCAT_WS(' ', name, surname, year) LIKE %$word%";$query .= implode(" AND ", $fields);}

PS - 我最近从@yahyaE 学到了这种查询构建技术 这里 :)

I have a string, like Elton 1999, and a MySql table with 4 fields :

table(id, name, surname, year)

I have to check if, joining these fields as "single field", it contains all the words of my string.

I mean :

1 Elton John 1999 -> must return the record
2 Elton Peter 1999 -> must return the record
3 Paris Elton 1999 -> must return the record
4 Elto John 1999 -> must not return the record
5 Elton Pierre 2000 -> must not return the record

Can I do it directly with MySql or I need first to get all the records and than parse them on server side? (on PHP, in my case)

P.S. The same result must begin if my string is 1999 Elton, so the order of my words doesnt matter...

解决方案

SELECT * FROM myTbl WHERE CONCAT(name, surname, year) LIKE '%ELTON%1999'

or

SELECT * FROM myTbl WHERE CONCAT_WS(' ', name, surname, year) LIKE '%ELTON%1999'

to add a space between each of the fields. Thx majic bunnie.

Update: Well, if you want to search for possible matches on any column with a string of values in any order, then you could explode your string by space and search each word individually. See if this works

$query = "SELECT * FROM myTbl";
if(!empty($searchString)){
  $query .= " WHERE ";
  $arr = explode(' ', $searchString);
  foreach($arr as $word)
    $fields[] = "CONCAT_WS(' ', name, surname, year) LIKE %$word%";
  $query .= implode(" AND ", $fields);
}

PS - I recently learned this query building technique from @yahyaE here :)

这篇关于哪里 field1 + field2 + field3 LIKE “%my_own_string%"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！