blocks|key|237530|text|$sql+=+array('0');+//+Stop+errors+when+$words+is+empty

foreach($words+as+$word){
++++$sql[]+=+'name+LIKE+%25'.$word.'%25'
}

$sql+=+'SELECT+*+FROM+users+WHERE+'.implode("+OR+",+$sql);|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|237531|unstyled|237532|编辑:CakePHP代码：|237533|foreach($words+as+$word){
++++$sql[]+=+array('Model.name+LIKE'+=>+'%25'.$word.'%25');
}

$this->Model->find('all',+array(
++++'conditions'+=>+array(
++++++++'OR'+=>+$sql
++++)
));|237534|阅读这篇文章：http://book.cakephp.org/1.3/en/view/1030/Complex-Find-Conditions|offset|length|237535|entityMap|0|LINK|mutability|MUTABLE|url|http://book.cakephp.org/1.3/en/view/1030/Complex-Find-Conditions^0|0|0|0|0|7|1S|0|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|W|8|@]|9|@]|A|$]]|$1|F|3|G|5|E|7|X|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|Y|8|@]|9|@]|A|$B|C]]|$1|J|3|K|5|E|7|Z|8|@]|9|@$L|10|M|11|1|12]]|A|$]]|$1|N|3|-4|5|E|7|13|8|@]|9|@]|A|$]]]|O|$P|$5|Q|R|S|A|$T|U]]]]

<pre><code>$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);
</code></pre>

<hr>

Edit: code for CakePHP:

<pre><code>foreach($words as $word){
 $sql[] = array('Model.name LIKE' =&gt; '%'.$word.'%');
}

$this-&gt;Model-&gt;find('all', array(
 'conditions' =&gt; array(
 'OR' =&gt; $sql
 )
));
</code></pre>

Read up on this stuff: <a href="http://book.cakephp.org/1.3/en/view/1030/Complex-Find-Conditions">http://book.cakephp.org/1.3/en/view/1030/Complex-Find-Conditions</a>

blocks|key|240432|text|对于标准SQL，它将是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|240433|SELECT+*+FROM+users+WHERE+name+LIKE+'%25tom%25'+
+++++++++++++++++++++++OR+name+LIKE+'%25smith%25'+
+++++++++++++++++++++++OR+name+LIKE+'%25larry%25';|code-block|syntax|javascript|240434|由于您使用的是MySQL，因此可以使用RLIKE+(a.k.a.+REGEXP)|offset|length|style|CODE|240435|SELECT+*+FROM+users+WHERE+name+RLIKE+'tom%7Csmith%7Clarry';|240436|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/regexp.html^0|0|0|J|5|X|6|J|L|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@$I|Z|J|10|K|L]|$I|11|J|12|K|L]]|9|@$I|13|J|14|1|15]]|A|$]]|$1|M|3|N|5|D|7|16|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

In case of standard SQL, it would be: 

<pre><code>SELECT * FROM users WHERE name LIKE '%tom%' 
 OR name LIKE '%smith%' 
 OR name LIKE '%larry%';
</code></pre>

Since you're using MySQL you can use <a href="http://dev.mysql.com/doc/refman/5.1/en/regexp.html"><code>RLIKE</code> (a.k.a. <code>REGEXP</code>)</a>

<pre><code>SELECT * FROM users WHERE name RLIKE 'tom|smith|larry';
</code></pre>

blocks|key|3118131|text|你不能。它必须是一个链式的field+like+%25..%25+or+field+like+%25..%25+or+...。where+...+in子句只提取字符串匹配，不支持通配符。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3118132|entityMap^0|D|15|1J|C|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|M|8|@]|D|@]|E|$]]]|G|$]]

You can't. It'll have to be a chained <code>field like %..% or field like %..% or ...</code>. A <code>where ... in</code> clause only does extract string matches, with no support for wildcards.

blocks|key|237537|text|尝试使用REGEXP|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|237538|SELECT+*+FROM+users+where+fieldName+REGEXP+'Tom%7CSmith%7CLarry';|code-block|syntax|javascript|237539|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/regexp.html^0|4|6|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

try using <a href="http://dev.mysql.com/doc/refman/5.1/en/regexp.html" rel="nofollow">REGEXP</a>

<pre><code>SELECT * FROM users where fieldName REGEXP 'Tom|Smith|Larry';
</code></pre>

blocks|key|240438|text|我刚取了472084的代码。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|240439|$sql+=+array('0');+//+Stop+errors+when+$words+is+empty

foreach($words+as+$word){
++++$sql[]+=+'name+LIKE+%25'.$word.'%25'
}

$sql+=+'SELECT+*+FROM+users+WHERE+'.implode("+OR+",+$sql);|code-block|syntax|javascript|240440|对于我自己来说，我不得不修改它，因为它返回了一个错误的SQL。我把它张贴给那些想要读这篇文章的人。|240441|foreach($words+as+$word){
++++$sql[]+=+'name+LIKE+\'%25'.$word.'%25\'';
}

$sql+=+'SELECT+*+FROM+users+WHERE+'.implode("+OR+",+$sql);|240442|他们之间的区别是关于报价，我的mysql数据库说有问题！所以我不得不避免引用$sql[]+=+'name+LIKE+%25'.$word.'%25'的话，现在它可以完美地工作了。|offset|length|style|CODE|240443|entityMap^0|0|0|0|0|12|W|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|W|8|@$M|X|N|Y|O|P]]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

i just took the code of 472084. 

<pre><code>$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);
</code></pre>

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.

<pre><code>foreach($words as $word){
 $sql[] = 'name LIKE \'%'.$word.'%\'';
}

$sql = 'SELECT * FROM users WHERE '.implode(" OR ", $sql);
</code></pre>

The difference between them is about quote, my mysql DB said there is a problem ! so i had to escape quote from <code>$sql[] = 'name LIKE %'.$word.'%'</code>
and now it's work perfectly.

blocks|key|3118233|text|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3118234|区块引用|blockquote|3118235|3118236|3118237|/**+*内爆值的多维数组，当与"[]“块不同时对字符进行分组。*@参数数组返回要内爆的数组*@+$array+string内爆的数组*/+hoArray2SqlLike(+$array+){+if+(！is_array(+$array+))返回$array；|3118238|$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+);|code-block|syntax|javascript|3118239|}|3118240|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|-4|4|5|6|Q|7|@]|8|@]|9|$]]|$1|A|3|B|4|C|6|R|7|@]|8|@]|9|$]]|$1|D|3|-4|4|5|6|S|7|@]|8|@]|9|$]]|$1|E|3|-4|4|5|6|T|7|@]|8|@]|9|$]]|$1|F|3|G|4|5|6|U|7|@]|8|@]|9|$]]|$1|H|3|I|4|J|6|V|7|@]|8|@]|9|$K|L]]|$1|M|3|N|4|5|6|W|7|@]|8|@]|9|$]]|$1|O|3|-4|4|5|6|X|7|@]|8|@]|9|$]]]|P|$]]

<blockquote>
 Blockquote
</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;

<pre><code>$values = array();
$strings = array();

foreach ( $array as $value )
{
 foreach ( str_split( $value ) as $key =&gt; $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 );
</code></pre>

}

blocks|key|3118394|text|在这段代码中，我们不能在sql查询中得到双引号|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3118395|$sql+=+array('0');+//+Stop+errors+when+$words+is+empty

foreach($words+as+$word){
++++$sql[]+=+'name+LIKE+%25'.$word.'%25'
}

$sql+=+'SELECT+*+FROM+users+WHERE+'.implode("+OR+",+$sql);

SELECT+*+FROM+person+where+name+like+%25"Tom"%25+OR+name+like+%25"Smith"%25+OR+name+like+%25"Larry"%25;
//+error+in+double+quotes+and+%25+is+out+side+of+Double+quotes+.|code-block|syntax|javascript|3118396|或者您也可以使用逗号分隔值：-|3118397|$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+=+"'%25{$val}%25'";
++++++++$new_arr[]+=+'name+like+'.$arr;
++++++++}

++++++$new_arr+=+implode("+OR+",+$new_arr);
++++++echo+$sql.="+where+".$new_arr;
++++}
++++++++else+{$sql.="";}|3118398|Echo+sql查询如下：-|3118399|SELECT+*+FROM+person+where+name+like+'%25Tom%25'+OR+name+like+'%25Smith%25'+OR+name+like+'%25Larry%25';|3118400|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

In this code we can't get double quotes in sql query

<pre><code>$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 .
</code></pre>

Or you can also use comma separated value:-

<pre><code>$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.="";}
</code></pre>

Echo sql query like this:-

<pre><code>SELECT * FROM person where name like '%Tom%' OR name like '%Smith%' OR name like '%Larry%';
</code></pre>

blocks|key|235092|text|试试这个:只需多加一行代码:)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|235093|$words+=+array("Tom",+"Smith",+"Larry");+

$words+=+implode("%25'+OR+`name`+LIKE+'%25",+$words);
//+result+->+Tom%25+OR+`name`+LIKE+%25Smith%25+OR+`name`+LIKE+%25Larray

$menus+=+"SELECT+*+FROM+`users`+WHERE+`name`+LIKE+'%25"+.+$words+.+"%25'";
//+result+->+"...+WHERE+`name`+LIKE+%25Tom%25+OR+`name`+LIKE+%25Smith%25+OR+`name`+LIKE+%25Larray%25";|code-block|syntax|javascript|235094|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

try this:
With just one extra line of code :)
<pre><code>$words = array(&quot;Tom&quot;, &quot;Smith&quot;, &quot;Larry&quot;); 

$words = implode(&quot;%' OR `name` LIKE '%&quot;, $words);
// result -&gt; Tom% OR `name` LIKE %Smith% OR `name` LIKE %Larray

$menus = &quot;SELECT * FROM `users` WHERE `name` LIKE '%&quot; . $words . &quot;%'&quot;;
// result -&gt; &quot;... WHERE `name` LIKE %Tom% OR `name` LIKE %Smith% OR `name` LIKE %Larray%&quot;;
</code></pre>

blocks|key|3118440|text|许多SQL系统允许您将其作为字符值进行推送。CHAR(10)+=换行符。因此，有时，您可以用这种方式将单引号、双引号和其他东西推入sql字符串，而不是计算出所有的转义编码，而不会出现太多其他编码问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3118441|-Just给出一个提示。|3118442|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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. 

-Just a hint.

I know how to perform an SQL LIKE % query for a single value like so:

<pre><code>SELECT * FROM users WHERE name LIKE %tom%;
</code></pre>

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:

<pre><code>$words = array("Tom", "Smith", "Larry");
</code></pre>

How do I perform my SQL LIKE % to search for the words in my array like:

<pre><code>SELECT * FROM users WHERE name LIKE %[each_element_from_my_array]%
</code></pre>

WITHOUT putting the whole query inside a foreach loop or something

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.

Thanks

SQL LIKE % inside array

我知道如何对单个值执行SQL LIKE %查询，如下所示：SELECT * FROM users WHERE name LIKE %tom%;但是如果我的LIKE的搜索词来自一个数组，我该怎么做呢？例如，假设我们有一个这样的数组：$words = array("Tom", "Smith", "Larry");如何执行SQL LIKE%来搜索数组中的单词，如下所示：SELECT * FROM use

问数组中的SQL LIKE %
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问数组中的SQL LIKE %EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问数组中的SQL LIKE %
EN