MySQLi全文搜索多列类别 [英] MySQLi Full Text Search For Multiple Column Category

问题描述

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

搜索类别包括窗口，游戏，平板电脑和手机。

通过复选框选择搜索选择的类别/多个类别应该完成仅限选定的类别。

表格结构

  id类别标题日期... 
 1 windows windows7 d1 ... 
 2游戏windows游戏d2 ... 
 3平板电脑windows平板电脑d3 ... 
 4移动windows mobile d4 ... 
 5 windows windows8 d5 ... 
 6 windows windows vista d6 ... 
  

<用于搜索的复选框是

在

 < li> ;< label>< input name =alltype =checkboxchecked =checked/> All< / label> < /锂> 
< li>< label>< input name =windowstype =checkbox/> Windows操作系统< / label>< / li> 
< li>< label>< input name =mobiletype =checkbox/> Windows Mobile< / label>< / li> 
< li>< label>< input name =gamestype =checkbox/> Windows游戏< / label>< / li> 
< li>< label>< input name =tablettype =checkbox/> Windows Tablet< / label>< / li> b 
 
 
 
 $ b 
示例1： code>搜索'windows'应该返回结果为
 
 windows7 
 windows8 
 windows vista 
 
当只有复选框窗口被选中时
  

示例2：

 搜索'windows'应该返回结果为
 
 windows7 
 windows8 
 windows vista 
 windows游戏
 
当复选框窗口和游戏被检查。 
  

我做了一个查询，但它根本不起作用。

  $ 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。=或MATCH（category）AGAINST（'mobile'IN BOOLEAN MODE）; 
 $ query。=或MATCH（category）AGAINST（'tablet'IN BOOLEAN MODE）; 
 
 $ query。=AND MATCH（title）AGAINST（'+ $ keyword *'IN BOOLEAN MODE）ORDER by id desc，title desc LIMIT 10; 
  

请查看为什么它不起作用，并建议如何针对所选字段中的任何字段进行搜索查询。 / p>

谢谢。

解决方案

  SELECT * FROM表WHERE MATCH（field1，field2，field3，field4）
 AGAINST（'关键字'BOOLEAN模式）
  

似乎是您需要的。

但是，您的查询对SQL注入是开放的。

现在可以回答了。所以，这里有一个基于 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 =windowstype =checkbox/> 
  

I want to make mysqli full text search with or without multiple category based on checkbox selection.

Search is based on four category within same column.

Search category are windows, games, tablet, mobile.

When category/multiple category selected through checkbox selection search should be done for selected category only.

Table Structure

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

checkboxes for search are

Search in

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

Example1:

Search for 'windows' should return result as

windows7
windows8 
windows vista

When only checkbox windows is checked

Example2:

Search for 'windows' should return result as

windows7
windows8 
windows vista
windows games

When checkbox windows and games are checked.    

I have made a query but it is not working at all.

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

I have also tried like this but not working.

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

Please see why its not working and suggest how search query can be made for no of fields selected.

Thanks.

解决方案

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

seems what you need.

But your query is wide open to SQL injection.

Now it's possible to answer. so, here goes a solution based on safeMysql (as raw mysqli will take too much code)

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

Note that you have to call your checkbox fields as

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

这篇关于MySQLi全文搜索多列类别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！