MySQLi text mode search for multiple columns

Question

I want to do a full-text mysqli search with or without multiple categories based on a checkbox selection.

The search is based on four categories within the same column.

The search category is windows, games, tablets, mobile devices.

If the category / multiple category selected by selecting the check box selection should only be performed for the selected category.

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

search boxes

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> 

Example 1:

 Search for 'windows' should return result as windows7 windows8 windows vista When only checkbox windows is checked 

Example 2:

 Search for 'windows' should return result as windows7 windows8 windows vista windows games When checkbox windows and games are checked. 

I made a request, but it does not work 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 also tried this, but did not work.

 $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 it does not work, and suggest how you can perform a search query without the selected fields.

Thanks.

Answer 1

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

It seems that you need to.

But your query is wide open for SQL injection.

Now you can answer. so here comes a solution based on safeMysql (since raw mysqli will take up 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 must call your fields in the field

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