MySQLi Full Text Search For Multiple Column Category

Question

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.

what's the point in all these `MATCH field AGAINST 'field1'`? Looks quite strange to me — Your Common Sense, Jun 25 '13 at 10:33
@YourCommonSense there some columns in table and search should be done in title only based on the row if contains selected fields. — , Jun 25 '13 at 10:35
Do you get an error message? Please consider posting a link to a fiddle or some sample data and your table schema. Questions lacking such information are hard to answer and are frequently down-voted. I assume that field1,field2 etc are other fields in the same table. If this is so, unquote them and the query should work ie field=field1 OR field=field2 OR .... — Robert Seddon-Smith, Jun 25 '13 at 10:57
@RobertSeddon-Smith yes field1,field2 etc are other fields in the same table same column, there is no error but query result are not relevant. — , Jun 25 '13 at 10:59
@RobertSeddon-Smith please see the table structure i have updated the question. — , Jun 25 '13 at 11:06
@RobertSeddon-Smith please see the updated question, see if you can help. — , Jun 25 '13 at 11:59

Your Common Sense · Accepted Answer · 2013-06-25T12:23:22.130

1

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

edited Jun 25 '13 at 12:23

answered Jun 25 '13 at 11:13

Your Common Sense

156,878
40
214
345

Your query matches fields against keyword as if they are different columns but there is one field column with many fields to match against, fields should be matched against field only and title against keyword. can you help me with that. – Jun 25 '13 at 11:18
"field" and "column" are synonyms. So, I have no idea what you're talking about. Matching one field against another makes no sense. – Your Common Sense Jun 25 '13 at 11:21
What is so hard to understand sir in matching different values in a column for a particular keyword that match in another column. – Jun 25 '13 at 11:24
fields is just a word I am using to express values of column field. – Jun 25 '13 at 11:25
To clarify more I give you an example, Suppose keyword is `k` that is to be searched in `title` for `rows` containing `field1 or field2` only. – Jun 25 '13 at 11:28
Can you add an example to your question? Not some quick sketch like "f2, f2, f3" but a complete real life example with real data and desired result? – Your Common Sense Jun 25 '13 at 11:34
make selected categories into IN() statement. But your query is still wide opened to injection – Your Common Sense Jun 25 '13 at 12:11
Thank you so much it works like expected, You made my day sir. Also can you up vote my question. – Jun 25 '13 at 12:23
About injection can you tell me more, I am not fully aware of injection. – Jun 25 '13 at 12:26
On injections you can read [here](http://stackoverflow.com/q/60174/285587). To prevent it, every value should be into query via placeholder, like in the code I posted. – Your Common Sense Jun 25 '13 at 12:44

MySQLi Full Text Search For Multiple Column Category

1 Answers1

Linked