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.
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.
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?
In this case what is the difference between using LIKE '%Ford Mustang%' and LIKE 'Ford Mustang%'
