SQL在同一字段中搜索多个值

I'm building a simple search algorithm and I want to break my string with spaces, and search my db on it, like so:

我正在构建一个简单的搜索算法，我想用空格打破我的字符串，并在其上搜索我的数据库，如下所示：

$search = "Sony TV with FullHD support";  
$search = explode( ' ', $search );

SELECT name FROM Products WHERE name LIKE %$search[1]% AND name LIKE %$search[2]% LIMIT 6

Is this possible?

这可能吗？

Thanks in advance

提前致谢

3 个解决方案

#1

Yes, you can use SQL IN operator to search multiple absolute values:

是的，您可以使用SQL IN运算符来搜索多个绝对值：

SELECT name FROM products WHERE name IN ( 'Value1', 'Value2', ... );

If you want to use LIKE you will need to use OR instead:

如果你想使用LIKE，你需要使用OR代替：

SELECT name FROM products WHERE name LIKE '%Value1' OR name LIKE '%Value2';

Using AND (as you tried) requires ALL conditions to be true, using OR requires at least one to be true.

使用AND（正如您所尝试的）要求所有条件都为真，使用OR要求至少有一个为真。

#2

Try this

尝试这个

Using UNION

使用UNION

$sql = '';
$count = 0;
foreach($search as $text)
{
  if($count > 0)
     $sql = $sql."UNION Select name From myTable WHERE Name LIKE '%$text%'";
  else
     $sql = $sql."Select name From myTable WHERE Name LIKE '%$text%'";

  $count++;
}

Using WHERE IN

使用WHERE IN

$comma_separated = "('" . implode("','", $search) . "')";  // ('1','2','3')
$sql = "Select name From myTable WHERE name IN ".$comma_separated ;

#3

This has been partially answered here: MySQL Like multiple values

这已在此部分回答：MySQL喜欢多个值

I advise against

我建议反对

$search = explode( ' ', $search );

$ search = explode（''，$ search）;

and input them directly into the SQL query as this makes prone to SQL inject via the search bar. You will have to escape the characters first in case they try something funny like: "--; DROP TABLE name;

并将它们直接输入到SQL查询中，因为这会使搜索栏中的SQL注入变得容易。你必须首先逃避角色，以防他们尝试一些有趣的事情：“ - ; DROP TABLE name;

$search = str_replace('"', "''", search );

$ search = str_replace（'“'，''''，search）;

But even that is not completely safe. You must try to use SQL prepared statements to be safer. Using the regular expression is much easier to build a function to prepare and create what you want.

但即使这样也不完全安全。您必须尝试使用SQL预处理语句更安全。使用正则表达式更容易构建一个函数来准备和创建你想要的东西。

function makeSQL_search_pattern($search) {
    search_pattern = false;
    //escape the special regex chars
    $search = str_replace('"', "''", $search);
    $search = str_replace('^', "\\^", $search);
    $search = str_replace('$', "\\$", $search);
    $search = str_replace('.', "\\.", $search);
    $search = str_replace('[', "\\[", $search);
    $search = str_replace(']', "\\]", $search);
    $search = str_replace('|', "\\|", $search);
    $search = str_replace('*', "\\*", $search);
    $search = str_replace('+', "\\+", $search);
    $search = str_replace('{', "\\{", $search);
    $search = str_replace('}', "\\}", $search);
    $search = explode(" ", $search);
    for ($i = 0; $i < count($search); $i++) {
        if ($i > 0 && $i < count($search) ) {
           $search_pattern .= "|";
        }
        $search_pattern .= $search[$i];
    }
    return search_pattern;
}

$search_pattern = makeSQL_search_pattern($search);
$sql_query = "SELECT name FROM Products WHERE name REGEXP :search LIMIT 6"
$stmt = pdo->prepare($sql_query);
$stmt->bindParam(":search", $search_pattern, PDO::PARAM_STR);
$stmt->execute();

I have not tested this code, but this is what I would do in your case. I hope this helps.

我没有测试过这段代码，但这就是我在你的情况下会做的。我希望这有帮助。

#1