[eluser]Bus Pants[/eluser]
So I planned on using PHP/MySQL FULLTEXT searching for this but it turns out that many of the popular searches on this site will net less than three results, and FULLTEXT will return zero/null if there are less than three matches...
I need to allow multi-word searches and compare against two different columns. So say someone searches for "Honda Prelude 2.5L" I need to compare each word to the
model column and compare each word to the
engine column, where the
model column is the car model like Civic, Prelude, CRV etc. and the
engine column is a varchar (128) string like "3S-FE 2.5L DOHC 4-Cylinder"...
I also need to only return results where the column
status = 1.
My problem is that if they search for "2.5L" and the
engine match I am looking for is an entire string of words like above, there is obviously no exact match... So I tried splitting the words into a comma separated list and using the where_in() clauses...
I started with this:
Code:
$terms = str_replace(" ", ",", $search_term);
$this->db->select('engines.*, models.model as model_name, makes.make as make_name');
$this->db->from('engines');
$this->db->where("(engines.status = 1)");
$this->db->where_in('models.model', $terms);
$this->db->or_where_in('engines.engine', $terms);
$this->db->join('models', 'models.id = engines.model');
$this->db->join('makes', 'makes.id = engines.make');
$this->db->order_by('makes.make, models.model, engines.first_year', 'ASC');
If I search for say "Prelude" I get hits because it is matching on the
models column... but if I type in a word that I am expecting in the
engine column, like "DOHC" I get de nada...
So I am thinking it has to do with parenthesis, which I have no idea how to introduce into this query... any ideas?
The RAW query is:
Code:
SELECT `engines`.*, `models`.`model` as model_name, `makes`.`make` as make_name
FROM (`engines`)
JOIN `models` ON `models`.`id` = `engines`.`model`
JOIN `makes` ON `makes`.`id` = `engines`.`make`
WHERE (engines.status = 1) AND `models`.`model` IN ('DOHC') OR `engines`.`engine` IN ('DOHC')
ORDER BY `makes`.`make`, `models`.`model`, `engines`.`first_year` ASC
