0

I want to search using MATCH in mysql.

I have 1 table contain "name" and "category" fields. the "category" field contain book,books,books.

what i want is, when i search "book" or "books" in category field, it should give me 3 row.

can anyone help me with this ?

thanks

i need to clarified this question, actually i have a website which have search field. when user input something on it, my web should search in category field. the real problem is, sometimes user input "book", sometimes "books", sometime "car" ,sometimes "cars". these "s" word after the word make me headache, i know that user really want is to find all related with book or car, so, what should i do, should i strip every "s" letter ? or is there any better solution ?

Ari

ari
  • 43
  • 5
  • mysql supports no functions like that, I think it is better if you store the category in a seperate table an build a 1:N or N:M relation – user1027167 Feb 29 '12 at 09:52

4 Answers4

0
select *
from table
where category LIKE '%book%'
vdegenne
  • 12,272
  • 14
  • 80
  • 106
  • thx oddant :) i guess i put the wrong question. i need to clarified this question, actually i have a website which have search field. when user input something on it, my web should search in category field. the real problem is, sometimes user input "book", sometimes "books", sometime "car" ,sometimes "cars", etc. these "s" word after the word make me headache, i know that user really want is to find all related with book or car, so, what should i do, should i strip every "s" letter ? or is there any better solution ? – ari Mar 01 '12 at 04:11
  • @ari actually the request reads `select all columns from the table table where the word 'book' is found in the category field regardless what is before and after the word` therefore if the `s` is omitted the request returns the same results than with the `s` written – vdegenne Mar 01 '12 at 09:19
  • hmmm really need to check this. – ari Mar 09 '12 at 09:50
0

trim the user input to a acceptable length and try this query

  $userInput = substr($input, 0, 4);

  select * from table where category like "%$userInput%"
Naveen Kumar
  • 4,543
  • 1
  • 18
  • 36
  • thx Naveen:) i guess i put the wrong question. i need to clarified this question, actually i have a website which have search field. when user input something on it, my web should search in category field. the real problem is, sometimes user input "book", sometimes "books", sometime "car" ,sometimes "cars", etc. these "s" word after the word make me headache, i know that user really want is to find all related with book or car, so, what should i do, should i strip every "s" letter ? or is there any better solution ? – ari Mar 01 '12 at 04:12
  • trim the user string to a acceptable length then query based on that string – Naveen Kumar Mar 01 '12 at 04:22
  • thx naveen. i thought there's more generic way doing this. i would expect there's a function that automatically know that when user search books, it would also return book, so with other words like movies, movie, cars, car, etc – ari Mar 09 '12 at 09:50
0

If you are running the query from PHP, for example, you could prepare the query there and then use a simple regular expression:

<?php

$term = 'book';

if(substr($term,-1) == 's') { //if term ends in an s
  $term = substr($term,0,-1); //the word without the s
}

//TODO: escape $term to prevent SQL injection

$query = "
  SELECT * FROM table
  WHERE category REGEXP '{$term}s?' // s? matches zero or one 's' character
";
lamplightdev
  • 2,041
  • 1
  • 20
  • 24
  • thx oddant :) i guess i put the wrong question. i need to clarified this question, actually i have a website which have search field. when user input something on it, my web should search in category field. the real problem is, sometimes user input "book", sometimes "books", sometime "car" ,sometimes "cars", etc. or even people input 2 words like cars insurance or car insurance. these "s" word after the word make me headache, i know that user really want is to find all related with book or car, so, what should i do, should i strip every "s" letter ? or is there any better solution ? – ari Mar 01 '12 at 04:13
0

Searching with MATCH() requires a fulltext index on column category, which might be overkill.

If you really just want those two cases, you could write

select * from table where
category = 'book' or category = 'books'

With Oddant's answer you might also get results like 'probookcover' or whatever.

If you want it to be case insensitive you have multiple options.

select * from table where
lower(category) = 'book' or lower(category) = 'books'

or

select * from table where
category like 'book' or category like 'books'

Alternatively you could also do 

select * from table where
category like 'book%'

which gets you all columns which start with book, but you might also get 'bookcover'.

EDIT: Considering your comment:

Like I said, match() is overkill, therefore I would do it like this:

select * from table where
category = whatYourUserEnters OR category = substring(whatYourUserEnters, 1, length(whatYourUserEnters) - 1)
fancyPants
  • 50,732
  • 33
  • 89
  • 96
  • thx tombom :) i guess i put the wrong question. i need to clarified this question, actually i have a website which have search field. when user input something on it, my web should search in category field. the real problem is, sometimes user input "book", sometimes "books", sometime "car" ,sometimes "cars", etc. these "s" word after the word make me headache, i know that user really want is to find all related with book or car, so, what should i do, should i strip every "s" letter ? or is there any better solution ? – ari Mar 01 '12 at 04:12