php sql like多个值,PHP SQL SELECT，其中包含多個單詞的搜索項

I have a select where like query for a seach form which is as follows:

我有一個選擇,如查詢搜索表單,如下所示:

$bucketsearch = sanitizeone($_POST["bucketsearch"], "plain");

$bucketsearch = strip_word_html($bucketsearch);

?>

if(isset($_POST['search'])){

$result=MYSQL_QUERY( "SELECT * FROM buckets where bucketname like '%$bucketsearch%' order by bucketname");

}else{

$result=MYSQL_QUERY( "SELECT * FROM buckets order by bucketname");

}

My problem is that if someone searches for instance for "apple and pear" i do not get any results that contain any of the words, i can only make it return results (well 1 result) with all the words in it.

我的問題是,如果有人搜索“蘋果和梨”的實例,我沒有得到任何包含任何單詞的結果,我只能讓它返回結果(1個結果)及其中的所有單詞。

Can anyone help me make this search a bit more versitle?? Thanks in advance.

任何人都可以幫我把這個搜索更多一些嗎?提前致謝。

6 个解决方案

#1

30

So you want an AND search using each of the words entered, rather than the exact string? Howabout something like this:

那么你想要使用輸入的每個單詞進行AND搜索,而不是確切的字符串?怎么樣這樣:

$searchTerms = explode(' ', $bucketsearch);

$searchTermBits = array();

foreach ($searchTerms as $term) {

$term = trim($term);

if (!empty($term)) {

$searchTermBits[] = "bucketname LIKE '%$term%'";

}

}

...

$result = mysql_query("SELECT * FROM buckets WHERE ".implode(' AND ', $searchTermBits).");

this will give you a query like:

這會給你一個像這樣的查詢:

SELECT * FROM buckets WHERE bucketname LIKE '%apple%' AND bucketname LIKE '%and%' AND bucketname LIKE '%pear%'

change the AND to an OR if you want to match any of the search terms rather than all. Further improvements could involve defining some stop words like 'and' to give better results.

如果要匹配任何搜索詞而不是全部,請將AND更改為OR。進一步的改進可能涉及定義一些停止詞,如'和',以提供更好的結果。

#2

7

I think that the best solution would be to use Regular Expressions. It's cleanest and probably the most effective. Regular Expressions are supported in all commonly used DB engines.

我認為最好的解決方案是使用正則表達式。它是最干凈的,可能是最有效的。所有常用的數據庫引擎都支持正則表達式。

In MySQL there is RLIKE operator so your query would be something like:

在MySQL中有RLIKE運算符,所以你的查詢將是這樣的:

SELECT * FROM buckets WHERE bucketname RLIKE "(?=.*apple)(?=.*and)(?=.*pear)"

Did't tested it, hope that my expression is right for MySQL regexp "dialect".

沒有測試過,希望我的表達式適合MySQL正則表達式“方言”。

有關MySql regexp支持的更多信息:http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp

#3

1

For your simple case here, you could replace your "and" with a "%" (but I'm guessing you're looking for a more comprehensive answer. (This would also be order specific, as apple would have to come before pear.)

對於你這里的簡單案例,你可以用“%”替換你的“和”(但我猜你正在尋找一個更全面的答案。)這也是具體的訂單,因為蘋果必須在梨之前來。)

#4

1

Er. Not the best solution I'd think but you can break up the words into an array and loop them out into multiple LIKES. Do some replaces to yank out ANDs, ORs etc and then run an explode.

呃。不是我想的最好的解決方案,但你可以把這些單詞分解成一個數組並將它們循環到多個LIKES中。做一些替換,以取出AND,OR等,然后運行爆炸。

Then just loop.

然后循環。

$sql = SELECT * from Buckets where";

$ sql = SELECT *來自Buckets,其中“;

Loop the array, $sql .= " bucketname LIKE '%" . $arrayEl[i] . "% OR'. Just make sure on the last iteration to not include the last OR or append a last line of 0=1 etc.

循環數組,$ sql。=“bucketname LIKE'%”。 $ arrayEl [i]。 “%OR'。只需確保在最后一次迭代中不包括最后一個OR或追加最后一行0 = 1等。

Not elegant, not efficient but in this case it'll work. You'd honestly be better off running a full text search if its a text field.

不優雅,效率不高,但在這種情況下它會起作用。如果它是一個文本字段,你真的會更好地運行全文搜索。

#5

1

I wanted something with basic SQL for my multi word search in MySQL database. So I come up with the following.

我希望在MySQL數據庫中使用基本SQL進行多字搜索。所以我想出了以下內容。

$terms=explode(" ",$search);

$count=count($terms);

$sql="SELECT * FROM product WHERE";

for($i=0;$i

{

if($i!=$count-1)

$sql = $sql.

" (pname LIKE '%$terms[$i]%' OR category LIKE '%$terms[$i]%' OR

sub_category LIKE '%$terms[$i]%' OR

description LIKE '%$terms[$i]%') AND ";

else

$sql = $sql.

"(pname LIKE '%$terms[$i]%' OR category LIKE '%$terms[$i]%' OR

sub_category LIKE '%$terms[$i]%' OR

description LIKE '%$terms[$i]%')";

}

#6

0

You can split "apple and pear" into 2 strings; "apple" and "pear". Maybe, then you can do WHERE bucketname LIKE '%apple%' OR bucketname LIKE '%pear%'. I don't know if this helps.

你可以將“apple and pear”分成2個字符串; “蘋果”和“梨”。也許,那么你可以做WHERE bucketname LIKE'%apple%'或bucketname LIKE'%pear%'。我不知道這是否有幫助。