8

I'm having some trouble searching for any similar match in two fields. For example I have a table with the values:

CAR MAKE   CAR MODEL
Ford       Mustang (Shelby)
Toyota     Corolla
Seat       Leon

etc etc.

I want to be able to get the result "Ford, Mustang (Shelby)" by searching for any of the following combinations:

  • Ford

  • Mustang

  • Shelby

  • Ford Mustang

    or any other combination.

Is this possible? I've had a good search but it's hard to find the search terms to describe what I mean.

Community
  • 1
  • 1
penpen
  • 935
  • 3
  • 12
  • 22

2 Answers2

9

Split your terms on whitespace and then, for each term, build a little bit of SQL like this:

car_make like '%x%' or car_model like '%x%'

Then join all of those with or to get your WHERE clause. So for "Shelby Ford", you'd end up with SQL like this:

select stuff
from cars
where car_make like '%Shelby%'
   or car_model like '%Shelby%'
   or car_make like '%Ford%'
   or car_model like '%Ford%'

If you need anything more complicated then investigate MySQL's full-text search capabilities.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • It's looking to be a bit more complicated than that but I think you're right about the full-text search, I hadn't looked into that. Thank you for taking the time to help me. – penpen Aug 20 '11 at 18:42
2

Give this a try:

SELECT Car_Make, Car_Model, CONCAT_WS(' ', Car_Make, Car_Model) as Make_Model 
FROM cars 
WHERE CONCAT_WS(' ', Car_Make, Car_Model) LIKE '%Ford Mustang%'

Not sure of the exact syntax since I'm not at home but something similar should work.

See also: Using mysql concat() in WHERE clause?

Community
  • 1
  • 1
Banjoe
  • 1,768
  • 12
  • 13
  • Thanks, gave it a quick try but not getting anywhere. Think I'll take a closer look when I get a chance – penpen Aug 20 '11 at 18:41