php SQL LIKE % 数组内
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9416062/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL LIKE % inside array
提问by user765368
I know how to perform an SQL LIKE % query for a single value like so:
我知道如何对单个值执行 SQL LIKE % 查询，如下所示：
SELECT * FROM users WHERE name LIKE %tom%;
but how do I do this if the search terms for my LIKE comes from an array? For example, let's say we have an array like this:
但是如果我的 LIKE 的搜索词来自一个数组，我该怎么做？例如，假设我们有一个这样的数组：
$words = array("Tom", "Smith", "Larry");
How do I perform my SQL LIKE % to search for the words in my array like:
我如何执行我的 SQL LIKE % 来搜索我的数组中的单词，例如：
SELECT * FROM users WHERE name LIKE %[each_element_from_my_array]%
WITHOUT putting the whole query inside a foreach loop or something
无需将整个查询放入 foreach 循环或其他内容
EDIT: I forgot to mention that I'm doing this in cakePHP inside the conditions of the cakePHP find('all') method, so that complicates things a little bit.
编辑：我忘了提到我在 cakePHP find('all') 方法的条件下在 cakePHP 中执行此操作，因此这使事情变得有点复杂。
Thanks
谢谢
回答by 472084
$sql = array('0'); // Stop errors when $words is empty
foreach($words as $word){
$sql[] = 'name LIKE %'.$word.'%'
}
$sql = 'SELECT * FROM users WHERE '.implode(" OR ", $sql);
Edit: code for CakePHP:
编辑：CakePHP 的代码：
foreach($words as $word){
$sql[] = array('Model.name LIKE' => '%'.$word.'%');
}
$this->Model->find('all', array(
'conditions' => array(
'OR' => $sql
)
));
Read up on this stuff: http://book.cakephp.org/1.3/en/view/1030/Complex-Find-Conditions
阅读这些东西：http: //book.cakephp.org/1.3/en/view/1030/Complex-Find-Conditions
回答by vartec
In case of standard SQL, it would be:
在标准 SQL 的情况下，它将是：
SELECT * FROM users WHERE name LIKE '%tom%'
OR name LIKE '%smith%'
OR name LIKE '%larry%';
Since you're using MySQL you can use RLIKE(a.k.a. REGEXP)
由于您使用的是 MySQL，因此您可以使用RLIKE（又名REGEXP）
SELECT * FROM users WHERE name RLIKE 'tom|smith|larry';
回答by Marc B
You can't. It'll have to be a chained field like %..% or field like %..% or .... A where ... inclause only does extract string matches, with no support for wildcards.
你不能。它必须是一个链式field like %..% or field like %..% or .... 一个where ... in条款只做提取字符串匹配，与通配符的支持。
回答by John Woo
回答by user3114471
i just took the code of 472084.
我只是拿了472084的代码。
$sql = array('0'); // Stop errors when $words is empty
foreach($words as $word){
$sql[] = 'name LIKE %'.$word.'%'
}
$sql = 'SELECT * FROM users WHERE '.implode(" OR ", $sql);
For my self, i had to modify it because it's returned me an error SQL. I Post it for people who gonna read the thread.
对于我自己，我不得不修改它，因为它返回了一个错误 SQL。我将它发布给要阅读线程的人。
foreach($words as $word){
$sql[] = 'name LIKE \'%'.$word.'%\'';
}
$sql = 'SELECT * FROM users WHERE '.implode(" OR ", $sql);
The difference between them is about quote, my mysql DB said there is a problem ! so i had to escape quote from $sql[] = 'name LIKE %'.$word.'%'and now it's work perfectly.
他们之间的区别是关于quote，我的mysql DB说有问题！所以我不得不逃避引用$sql[] = 'name LIKE %'.$word.'%'，现在它完美地工作。
回答by André Mendon?a
Blockquote
块引用
/** * Implode a multidimensional array of values, grouping characters when different with "[]" block. * @param array $array The array to implode * @return string The imploded array */ function hoArray2SqlLike( $array ) { if ( ! is_array( $array ) ) return $array;
/** * 内爆值的多维数组，当与“[]”块不同时将字符分组。* @param array $array 要内爆的数组 * @return string 内爆的数组 */ function hoArray2SqlLike( $array ) { if ( !is_array( $array ) ) return $array;
$values = array();
$strings = array();
foreach ( $array as $value )
{
foreach ( str_split( $value ) as $key => $char )
{
if ( ! is_array( $values[ $key ] ) )
{
if ( isset( $values[ $key ] ) )
{
if ( $values[ $key ] != $char )
{
$values[ $key ] = array( $values[ $key ] );
$values[ $key ][] = $char;
}
}
else
$values[ $key ] = $char;
}
elseif ( ! array_search( $char , $values[ $key ] ) )
$values[ $key ][] = $char;
}
}
foreach ( $values as $value )
{
if ( is_array( $value ) )
$value = '['.implode( '', $value ).']';
$strings[] = $value;
}
return implode( '', $strings );
}
}
回答by shashikant pandit
In this code we can't get double quotes in sql query
在这段代码中，我们无法在 sql 查询中获得双引号
$sql = array('0'); // Stop errors when $words is empty
foreach($words as $word){
$sql[] = 'name LIKE %'.$word.'%'
}
$sql = 'SELECT * FROM users WHERE '.implode(" OR ", $sql);
SELECT * FROM person where name like %"Tom"% OR name like %"Smith"% OR name like %"Larry"%;
// error in double quotes and % is out side of Double quotes .
Or you can also use comma separated value:-
或者您也可以使用逗号分隔值：-
$name = array(Tom, Smith, Larry);
$sql="SELECT * FROM person";
extract($_POST);
if ($name!=NULL){
$exp_arr= array_map('trim', explode(",",$name));
echo var_export($exp_arr);
//die;
foreach($exp_arr as $val){
$arr = "'%{$val}%'";
$new_arr[] = 'name like '.$arr;
}
$new_arr = implode(" OR ", $new_arr);
echo $sql.=" where ".$new_arr;
}
else {$sql.="";}
Echo sql query like this:-
像这样回显 sql 查询：-
SELECT * FROM person where name like '%Tom%' OR name like '%Smith%' OR name like '%Larry%';
回答by SQL_Old_Guy
Many SQL systems let you push it as a character value. CHAR(10) = Linefeed. So, occasionally, instead of working out all the escape coding, you can push single quotes, double quotes and other things into a sql string this way, without too many other coding issues.
许多 SQL 系统允许您将其作为字符值推送。CHAR(10) = 换行。因此，偶尔，您可以通过这种方式将单引号、双引号和其他内容推送到 sql 字符串中，而不是计算出所有的转义编码，而不会出现太多其他编码问题。
-Just a hint.
- 只是一个提示。
