Help needed with search query

[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

[eluser]CroNiX[/eluser]
You'll need to combine all of your wheres into a single where() as this version of CI doesn't have the functionality to break up the wheres into conditionals (the next version does).

Code:

$terms = str_replace(" ", ",", $search_term);

$this->db
  ->select('engines.*, models.model as model_name, makes.make as make_name')
  ->from('engines')
  ->where("(engines.status = 1) AND `models`.`model` IN ('DOHC') OR `engines`.`engine` IN ('DOHC')")
  ->join('models', 'models.id = engines.model')
  ->join('makes', 'makes.id = engines.make')
  ->order_by('makes.make, models.model, engines.first_year', 'ASC')
  ->get('engines');

[eluser]Bus Pants[/eluser]
I tried that but if I enter more than one word in the search box I get no results...

This code:

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) AND `models`.`model` IN ('$terms') OR `engines`.`engine` IN ('$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');

Produces this, and no results, but there should be seven matches:

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,Camry') OR `engines`.`engine` IN ('DOHC,Camry')
ORDER BY `makes`.`make`, `models`.`model`, `engines`.`first_year` ASC

Where did I go wrong?

[eluser]CroNiX[/eluser]
Your IN needs to be like:

Code:

IN('DOHC','Camry')//like an actual array of strings

not

Code:

IN('DOHC,Camry')

Each term needs quotes around it separated by a comma

Try:

Code:

$terms = explode(' ', $search_term);
$terms = implode("','", $terms);
$terms = "'" . $terms . "'";

[eluser]Bus Pants[/eluser]
Yeah good catch... so it only appears to be working against the models.model column...

If I type "Camry" in the search box I get 7 results... if I type "Camry DOHC" or just "DOHC" where the "DOHC" part would be found in the engines.engine column, I get no results... since it is an OR between them, all 7 of the Camry results should still show up, right?

The CI Code

Code:

$terms = explode(' ', trim($search_term));
$terms = implode("','", $terms);
$terms = "'" . $terms . "'";

$this->db->select('engines.*, models.model as model_name, makes.make as make_name');
$this->db->from('engines');

$this->db->where("(`engines`.`status` = 1) AND (`models`.`model` IN ($terms)
OR `engines`.`engine` IN ($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');

RAW Query I get when I search for "Camry" (shows 7 results)

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 ('Camry') OR `engines`.`engine` IN ('Camry'))

ORDER BY `makes`.`make`, `models`.`model`, `engines`.`first_year` ASC

RAW Query I get when i search for "Camry DOHC" (shows no results)

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 ('Camry','DOHC')
OR `engines`.`engine` IN ('Camry','DOHC'))

ORDER BY `makes`.`make`, `models`.`model`, `engines`.`first_year` ASC

RAW Query I get when i search for "DOHC" (shows no results)

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

This doesn't make sense... why is it ignoring the query of the second column?

[eluser]Bus Pants[/eluser]
I tried playing with different parenthesis configurations and nothing changed.

[eluser]CroNiX[/eluser]
Try left joins?

[eluser]Bus Pants[/eluser]
They're all in the same table, so it makes no sense that the OR doesn't just work...

[eluser]Bus Pants[/eluser]


You don't know jack about this Swapmeet Louie

[eluser]Bus Pants[/eluser]
Any other ideas why?