CodeIgniter Forums
Help needed with search query - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Help needed with search query (/thread-51410.html)

Pages: 1 2


Help needed with search query - El Forum - 05-02-2012

[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





Help needed with search query - El Forum - 05-02-2012

[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');



Help needed with search query - El Forum - 05-02-2012

[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?




Help needed with search query - El Forum - 05-02-2012

[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 . "'";



Help needed with search query - El Forum - 05-02-2012

[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?



Help needed with search query - El Forum - 05-02-2012

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


Help needed with search query - El Forum - 05-02-2012

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


Help needed with search query - El Forum - 05-02-2012

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




Help needed with search query - El Forum - 05-02-2012

[eluser]Bus Pants[/eluser]


You don't know jack about this Swapmeet Louie



Help needed with search query - El Forum - 05-03-2012

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