0

I'm trying to implement a search function, and I came across this question. It worked brilliantly. If I search for, say 'go', I get any row that has that combination in it, whether it be 'gold', or 'bogo', or 'happygolucky'. But now, I want to implement something where I can type 'go the' in my search. With the above question, I can do that using '%go%the%', and it works. However, if the match for 'go' is in Name, and the match for 'the' is in Species, it still shows that result. I'd like to only get the result if the match is in the same column. My query looks like this:

select * 
from tbl_fish 
where concat(Name, Size, Color, Species, Description) 
LIKE '%afr%vio%';

Is ther a way to go about this, short of some regex voodoo magic?

Community
  • 1
  • 1
PiousVenom
  • 6,888
  • 11
  • 47
  • 86

1 Answers1

2

You'll just need to query the individual fields:

select * 
from tbl_fish 
where Name LIKE '%afr%vio%'
    OR Size LIKE '%afr%vio%'
    OR Color LIKE '%afr%vio%'
    OR Species LIKE '%afr%vio%'
    OR Description LIKE '%afr%vio%';

You could also look at the MySQL FullText Search feature

Richard
  • 29,854
  • 11
  • 77
  • 120
  • Yeah, I figured as such, but I was REALLY hoping not to have to do that. :( – PiousVenom Mar 04 '15 at 15:28
  • See if the mysql full text feature suits your requirements. – Richard Mar 04 '15 at 15:29
  • Why were you REALLY hoping not to do what you need to do to get the answer you want? Elegance is fine if you can have it, but if the best algorithm is Brute Force, then that's what you go with. – TommCatt Mar 05 '15 at 07:50