如何创建多个单词搜索？ SQL

We have made a search field where you can search for ingredients and find recipes. For now you can only type in 1 ingredient:

我们已经建立了一个搜索领域，您可以在其中搜索成分并找到食谱。现在你只能输入1种成分：

if (isset($_POST['search'])) {
$searchquery = $_POST['search'];


$query = mysql_query("SELECT * FROM opskrifter WHERE id IN 
(SELECT opskrifterid FROM ingredienser WHERE ing_name IN ('$searchquery'))") or die("search failed");

We want to be able to search for multiple ingredients in the same search field by seperating the ingredients with a "," or something like this.

我们希望能够通过用“，”或类似的东西分离成分，在同一搜索领域中搜索多种成分。

Is there a simple way to make that happen ?

是否有一种简单的方法可以实现这一目标？

EDIT: We tried to use explode like this without succes.

编辑：我们尝试使用像这样的爆炸没有成功。

$searchTerms = explode(' ', $searchquery);
$searchTermBits = array();
foreach ($searchTerms as $term) {
if (!empty($term)) {
    $searchTermBits[] = "ing_name '$term'";
}}

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

Thanks! :)

谢谢！ :)

3 个解决方案

#1

You could simply get the user to type in his values comma-separated, the the input would be almost in the right syntax for the query. You just have to add semicolons around the values because you search for a string in your table.

您可以简单地让用户输入以逗号分隔的值，输入将几乎使用正确的查询语法。您只需在值周围添加分号，因为您在表中搜索字符串。

You can use PHP's str_replace()-Function:

你可以使用PHP的str_replace（） - 函数：

$vals = $_POST['search'];
$valsFormatted = "'" . str_replace(",", "','", $vals) . "'";

In this code, you replace all the commas of the input with the comma plus semicolons before and behind them in orderto wrap all values of the input with semicolons. You also have to add one at the beginning and at the end of the string. Replace the first comma in the function above with the char you want your users to separate the values with.

在此代码中，您将输入的所有逗号替换为前面和后面的逗号加分号，或者用分号包装输入的所有值。您还必须在字符串的开头和结尾添加一个。将上述函数中的第一个逗号替换为您希望用户将值分隔的char。

After that, you can simply change your query to the following:

之后，您只需将查询更改为以下内容：

$query = "SELECT * FROM opskrifter WHERE id IN 
(SELECT opskrifterid FROM ingredienser WHERE ing_name IN ('$valsFormatted'))";

Please also be informed, that your code like this is vulnerable for SQL Injections! Check out this link to learn how to prevent this.

另请注意，您的代码很容易被SQL注入攻击！查看此链接以了解如何防止这种情况。

#2

A simple statement like this would work:

像这样的简单陈述可行：

$array = implode("','",explode($_POST['search'], ","));
$query = mysql_query("SELECT * FROM opskrifter WHERE id IN (SELECT opskrifterid FROM ingredienser WHERE ing_name IN ({$array}))") or die("search failed");

First explode your search, then implode it (might not even need to do so). After that make sure the array gets used as the 'in' operator as a string/array.

首先爆炸你的搜索，然后内爆（甚至可能不需要这样做）。之后，确保数组作为字符串/数组用作'in'运算符。

For more information about this, you could read this question: PHP/MySQL using an array in WHERE clause

有关此内容的更多信息，您可以阅读以下问题：PHP / MySQL在WHERE子句中使用数组

The working copy from my local machine was this;

我本地机器的工作副本是这样的;

$_POST['search'] = "0, 1, 2";

$array = implode ( "','", explode ( ",", $_POST['search'] ) );
$query = mysql_query("SELECT * FROM users WHERE id IN ('$array')") or die(mysql_error());
var_dump ( $array );
var_dump ( $query );
var_dump ( "SELECT * FROM users WHERE id IN ('$array')" );
var_dump ( mysql_fetch_array ( $query ) );

which actually did return users, so if we would take this example and change it to your code, it would be (the query, at least):

实际上它确实返回了用户，所以如果我们将这个例子改为你的代码，它将是（查询，至少）：

$query = mysql_query("SELECT * FROM opskrifter WHERE id IN (SELECT opskrifterid FROM ingredienser WHERE ing_name IN ('$array'))") or die(mysql_error());

Do take note of the changed $array variable too.

请注意已更改的$数组变量。

#3

First you need to convert the text coming from the search field to array with:

首先，您需要将来自搜索字段的文本转换为数组：

 $string = $_POST['search'];
 $array = explode( '"' , $string);

So if you put in the search: test"hello"hi

所以，如果你进入搜索：测试“你好”嗨

the array will be:

数组将是：

1 => test,
2 => hello,
3 => hi

After that, you need to use the SQL format:

之后，您需要使用SQL格式：

WHERE column_name IN (value1,value2,...)

So you need to change the array we have created to a string with this format:

因此，您需要将我们创建的数组更改为具有以下格式的字符串：

$string = implode(',',$array);

So the echo of $string will be:

所以$ string的回声将是：

test,hello,hi

and SQL will be :

和SQL将是：

WHERE column_name IN ($string)

#1