使用 php 和 mysql 搜索多个关键字（其中 X 喜欢）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15538479/
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
search for multiple keywords with php and mysql (where X like)
提问by user1932820
I have a code that dynamically search for data in the database using ajax but I can search for only 1 keyword in a time. I would like to modify it so I can search for multiple keywords. Now, if I type 2 keywords separated by a space and in the database, the data is not separated by a space, there will be no result. If in the database the data is:
我有一个使用 ajax 动态搜索数据库中数据的代码，但我一次只能搜索 1 个关键字。我想修改它以便我可以搜索多个关键字。现在，如果我输入2个用空格隔开的关键字，并且在数据库中，数据没有用空格隔开，则不会有结果。如果数据库中的数据是：
'playstation3' or 'play cool station3'
'playstation3' 或 'play cool station3'
and I search for:
我搜索：
play station
游戏台
there would be no results. I would like to know if it possible to modify my code so I can search 2 or more keywords or words separated by a space or another word or a DOT or an underscore or a (-) or a (+) or a (%) or (anything else lol).
不会有结果。我想知道是否可以修改我的代码，以便我可以搜索 2 个或更多关键字或单词，这些关键字或单词由空格或另一个单词、点或下划线或 (-) 或 (+) 或 (%) 分隔或（其他任何东西，哈哈）。
I know that I should use pdo or mysqli but i'm using this for testing only!
我知道我应该使用 pdo 或 mysqli 但我仅将其用于测试！
$queried = $_POST['query'];
$search = mysql_query("SELECT * FROM links WHERE name LIKE '%$queried%'");
while($searche = mysql_fetch_array($search)){
echo "".$searche['link']."</br>".$searche['name']."</br>".$searche['size']."</br>".$searche['category']."<hr></br></br>";
}
回答by Ibu
To dynamically search all keywords, you can use the explode function to seperate all keywords;
动态搜索所有关键词，可以使用explode功能将所有关键词分开；
$queried = mysql_real_escape_string($_POST['query']); // always escape
$keys = explode(" ",$queried);
$sql = "SELECT * FROM links WHERE name LIKE '%$queried%' ";
foreach($keys as $k){
$sql .= " OR name LIKE '%$k%' ";
}
$result = mysql_query($sql);
Note 1:Always escape user input before using it in your query.
注意 1：在查询中使用用户输入之前，请始终对其进行转义。
Note 2:mysql_* functions are deprecated, use Mysqli or PDO as an alternative
注 2：mysql_* 函数已弃用，使用 Mysqli 或 PDO 作为替代
Update 2018 - Note 3:Don't forget to check the length of the $queriedvariable and set a limit. Otherwise the user can input a vary large string and crash your database.
2018 年更新 - 注意 3：不要忘记检查$queried变量的长度并设置限制。否则，用户可能会输入一个不同的大字符串并使您的数据库崩溃。
回答by Alex Maina
This is a MySQL PDOversion of @Hanky Panky's answer
这是@Hanky Panky 答案的MySQL PDO版本
$queried="abc,test, testing";
$values=explode(',',$queried);
$sql="SELECT * FROM citations_new WHERE";
$i=0;
foreach($values as $v){
$v=trim($v);
if($i==0) {
$sql.=" name LIKE '%$v%'";
}
else {
$sql.=" OR name LIKE '%$v%'";
}
$i++;
}
$sth = $this->db->prepare($sql);
$sth->execute();
$rows = $sth->setFetchMode(PDO::FETCH_ASSOC);
while($row = $sth->fetch()){
//enter code here
}
回答by Hanky Panky
Don't use mysql_*functions even for testing anymore. They are no easier than mysqli, just in case you think easy to test here then move.
mysql_*甚至不再使用函数进行测试。它们并不比 容易mysqli，以防万一您认为在此处易于测试然后移动。
However, you could split your input on a ,and try this
但是，您可以将输入拆分为 a,并尝试此操作
<?php
$queried="abc,test, testing";
$values=explode(',',$queried);
$sql="SELECT * FROM links WHERE";
$i=0;
foreach($values as $v)
{
$v=trim($v);
if($i==0)
{
$sql.=" name LIKE '%$v%'";
}
else
{
$sql.=" OR name LIKE '%$v%'";
}
$i++;
}
$search = mysql_query($sql);
while($searche = mysql_fetch_array($search))
{
echo "".$searche['link']."</br>".$searche['name']."</br>".$searche['size']."</br>".$searche['category']."<hr></br></br>";
}
?>
回答by Nathan
Just add an OR clause.
只需添加一个 OR 子句。
E.g.
例如
SELECT * FROM links WHERE name LIKE '%$keyword1%' OR name LIKE '%$keyword2%'
But I stronglyrecommend you using a parametrized query or other safe library to avoid Sql Injection Attacks
但我强烈建议您使用参数化查询或其他安全库来避免Sql 注入攻击
