php - MySQLi 全文搜索多列类别

Question

我想根据复选框选择进行带或不带多个类别的 mysqli 全文搜索。

搜索基于同一列中的四个类别。

搜索类别是 windows、游戏、平板电脑、手机。

当通过复选框选择搜索选择类别/多个类别时，应仅针对所选类别进行搜索。

表结构

id  category   title                date ...
1   windows    windows7             d1  ...
2   games      windows games        d2  ...
3   tablet     windows tablet       d3  ...
4   mobile     windows mobile       d4  ...
5   windows    windows8             d5  ...
6   windows    windows vista        d6  ...

搜索复选框是

搜索范围

  <li><label><input name="all" type="checkbox" checked="checked" />All</label></li>
  <li><label><input name="windows" type="checkbox" />Windows OS</label></li>
  <li><label><input name="mobile" type="checkbox" />Windows Mobile</label></li>
  <li><label><input name="games" type="checkbox" />Windows Games</label></li>
  <li><label><input name="tablet" type="checkbox" />Windows Tablet</label></li>

示例 1:

Search for 'windows' should return result as

windows7
windows8 
windows vista

When only checkbox windows is checked

例子2:

Search for 'windows' should return result as

windows7
windows8 
windows vista
windows games

When checkbox windows and games are checked.    

我已经进行了查询，但它根本不起作用。

$query = "SELECT * FROM $table WHERE ";
$query .= "category='' ";
$query .= "OR category='windows' ";
$query .= "OR category='games' ";
$query .= "OR category='mobile' ";
$query .= "OR category='tablet' ";

$query .= " AND MATCH (title) AGAINST ('+$keyword*' IN BOOLEAN MODE) ORDER by id desc, title desc LIMIT 10";

我也这样试过，但是不行。

$query = "SELECT * FROM $table WHERE ";
$query .= "MATCH (category) AGAINST ('' IN BOOLEAN MODE) ";
$query .= "OR MATCH (category) AGAINST ('windows' IN BOOLEAN MODE) ";
$query .= "OR MATCH (category) AGAINST ('games' IN BOOLEAN MODE) ";
$query .= "OR MATCH (category) AGAINST ('mobile' IN BOOLEAN MODE) ";
$query .= "OR MATCH (category) AGAINST ('tablet' IN BOOLEAN MODE) ";

$query .= " AND MATCH (title) AGAINST ('+$keyword*' IN BOOLEAN MODE) ORDER by id desc, title desc LIMIT 10";

请查看为什么它不起作用，并建议如何针对未选择的字段进行搜索查询。

谢谢。

Answer 1

SELECT * FROM table WHERE MATCH (field1, field2, field3, field4) 
                          AGAINST ('keyword' IN BOOLEAN MODE)

似乎是你需要的。

但是您的查询对 SQL 注入(inject)是开放的。

现在可以回答了。所以，这里有一个基于 safeMysql 的解决方案(因为原始 mysqli 会占用太多代码)

$sql  = "SELECT * FROM ?n WHERE category IN(?a) 
          AND MATCH (title) AGAINST (?s IN BOOLEAN MODE) ORDER by id desc LIMIT 10";
$data = $db->($sql,$table,$_GET['category'], $_GET['keyword']);

请注意，您必须将复选框字段称为

<input name="category[]" value="windows" type="checkbox" />