php - MySQL 查询中的多个 LIKE 比较？ - MySQL, PHP

Question

我有一个搜索表单字段，我想将其与表格中的多个列进行比较，例如:

+----------+-------+---------+------+------------+
| name     | owner | species | sex  | birth      |
+----------+-------+---------+------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 |
| Bowser   | Diane | dog     | m    | 1989-08-31 |
| Whistler | Gwen  | cat     | m    | 1997-12-09 |
+----------+-------+---------+------+------------+

我所做的查询有多个 LIKE 比较，但它似乎只找到前两列的数据行。

第一个比较是“name”，然后是“owner”，然后是“species”，然后是“sex” .

如果您输入的字符串与前两列匹配，它只会查找行，但是如果您输入的字符串与最后一列之一匹配，它不会返回任何内容。

这里是查询:

SELECT * FROM contact 
WHERE name LIKE \"%$search%\" 
OR owner LIKE \"%$search%\"
OR species LIKE \"%$search%\" 
OR sex LIKE \"%$search%\" 
GROUP BY name, owner,species, sex

我唯一的猜测是 MySQL 可能会限制您可以使用多少个“OR”？

如果用户输入的字符串与任何列数据匹配，我的查询应该从表中返回一行。

这是我的 PHP 代码

include("database.class.php");

    //search string assigned from POST field
    $search = $_POST['contact_search'];

    $db = new database();

    $db->select('quote_system');

    $result = $db->query("SELECT * FROM contact WHERE name LIKE \"%$search%\" OR owner LIKE \"%$search%\" OR species LIKE \"%$search%\" OR sex LIKE \"%$search%\" GROUP BY name, second_name, owner, species, sex");

    print_r($result);

    while ($row = $db->fetch_assoc($result))
    {
        echo $row['name'];
        echo $row['owner'];
        echo $row['species'];
        echo $row['sex'];
        echo "<br/>";

    }

如果这里有任何混淆，这里有一些例子来说明问题:

User enters string "claws" --> string matches data in "name" column --> prints row that column is in

User enters string "m" --> string only matches data in "sex" column --> nothing shows!

感谢您的宝贵时间!

Answer 1

您的问题

$result = $db->query("
  SELECT * FROM contact 
  WHERE first_name LIKE \"%$search%\" 
  OR second_name LIKE \"%$search%\" 
  OR company_name LIKE \"%$search%\" 
  OR email LIKE \"%$search%\" 
  GROUP BY first_name, second_name, company_name, email
");

似乎属于另一个表。另外，为什么要 GROUP BY？也许，您的意思是 ORDER BY？