使用php和mysql搜索多个关键字(其中X喜欢) [英] search for multiple keywords with php and mysql (where X like)

问题描述

我有一个代码，可以使用ajax在数据库中动态搜索数据，但是一次只能搜索1个关键字.我想对其进行修改，以便可以搜索多个关键字.现在，如果在数据库中键入2个用空格分隔的关键字，则数据不会用空格分隔，将不会有任何结果. 如果在数据库中，则数据为:

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:

'playstation3'或'play cool station3'

'playstation3' or 'play cool station3'

然后我搜索:

游戏台

没有结果.我想知道是否可以修改我的代码，以便我可以搜索2个或多个用空格或另一个单词或DOT或下划线或(-)或(+)或(％)分隔的关键字或单词.或(其他任何东西).

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).

我知道我应该使用pdo或mysqli，但我仅将其用于测试！

I know that I should use pdo or mysqli but i'm using this for testing only!

$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>";

    }

推荐答案

要动态搜索所有关键字，可以使用explode函数分隔所有关键字；

To dynamically search all keywords, you can use the explode function to seperate all keywords;

$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);

注1::在查询中使用用户输入之前，请始终对其进行转义.

Note 1: Always escape user input before using it in your query.

注意2::不建议使用mysql_ *函数，请使用Mysqli或PDO作为替代方法

Note 2: mysql_* functions are deprecated, use Mysqli or PDO as an alternative

更新2018年-注意3:不要忘记检查$queried变量的长度并设置一个限制.否则，用户可以输入较大的字符串，并使数据库崩溃.

Update 2018 - Note 3: Don't forget to check the length of the $queried variable and set a limit. Otherwise the user can input a vary large string and crash your database.

这篇关于使用php和mysql搜索多个关键字(其中X喜欢)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！