How to make a codeigniter query check if field contains?

Hi,
I'm running a query (shown below) where I'm checking for a few parameters, one of which is the $trim of a vehicle and getting the average price of the vehicles. The $trim  may contain for example:
LT
or perhaps
LT /1SA
or
LT Limited
etc. etc

I'm trying to get the query to return all the records that simply contain LT in their $trim field.
Doesn't seem to be working. Any idea where I may be going wrong? If I test using simply 'LT' as the $trim value, works as expected. Once there is anything else for a trim value... nada
$this->db->select_avg('price');
	$this->db->from('retail');
	$this->db->where('retail_year', $year);
	$this->db->where('retail_make', $make);
	$this->db->where('retail_model', $model);
	$this->db->where('mileage <=', ($mileage + 20000));
	$this->db->where('mileage >=', ($mileage - 20000));
	$this->db->like('trim', $trim, 'both'); 
	$query = $this->db->get();

Open in new window

LVL 1
tjyoungAsked:
Who is Participating?
 
Ray PaseurConnect With a Mentor Commented:
Yes, that's the general idea, exactly.  you may also find the Levenshtein() distance to be useful.
0
 
Ray PaseurCommented:
This is not specific to CodeIgniter, and Codeigniter may be standing in the way of your ability to write the query effectively.  The MySQL WHERE LIKE clause can use the percent sign for wild-card matches.  So the query you want might say something like this:

...WHERE trim LIKE %LT%
0
 
tjyoungAuthor Commented:
I was thinking 'both' referred to wildcard on either side. I'm reviewing the documentation to check that now. Thanks for the input. If all else fails, I'll switch to LIKE %LT% just wanted to keep it all 'codeigniter' if possible.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Ray PaseurCommented:
Apparently there is also an or_like() method.  Some examples here:
http://ellislab.com/forums/viewthread/123755/#615783
0
 
tjyoungAuthor Commented:
Hi, yes I saw that but thought it was only useful for testing different columns. columnA like $trim or columnB like $trim
0
 
tjyoungAuthor Commented:
I thought about doing this:
$trim = explode(' ', $trim);
$trim = $trim[0];

prior to the query. But kind of hooped if the trim isn't the first word and that it doesn't contain more than one word like: XLT Supercab

Catches alot of the 'SE with only 100,000km!!!!' type of crap people put in their trim fields.

I was hoping there would be some way to say: "if the record contains any of these words' than its a match. Perhaps I can 'explode' the entire trim and check each word somehow? I'm already checking the year, make and model, so that has to match perfectly which would narrow it down significantly.

If I was to check if my trim field contains any of the words in $trim... than I'd have a match.

That way if $trim was:
LT /1SA with 1000km!!

And my record has at least LT in it, its a match.

Though I'd be off  if the record was:
LS /1SA with 1000km!!

I don't think there is a perfect way since there is the 'human element' and god only knows what different people put in the trim field.

Any thoughts on something like:
Explode the $trim
If $trim contains only 1 word and I have a perfect match then return it

If $trim contains more than 1 word, but I have a match on 2 or more words and the first match is at $trim[0] than its a match.

Grasping at straws here but its a consistent problem in the industry and would be great if I could somehow narrow it down in most cases.

any thoughts, ideas etc. are much appreciated.
0
 
Ray PaseurCommented:
You may want to use more than one query.  For example, you could look for an exact match on all words, then if that fails look for a near match on some words, etc.  

You might also try some data normalization, for example, strip out punctuation with a regular expression that removed everything but letters, numbers and spaces.

Another strategy is to use soundex() and/or metaphone() values for "fuzzy logic" matching with natural language words.  I think this would imply additional fields in the SQL tables.
0
 
tjyoungAuthor Commented:
thats very interesting. Never heard of either of those (though I'm a bit of a newbie). Need to ponder a bit to get my head wrapped around that.

Is this the idea?
When you receive the trim field into the db, you have an additional column for the result:

metaphone($trim, 5);

Which may standardize the usual gibberish. Then compare your query with that new column using the same metaphone applied to the incoming $trim.

Is that the general idea?
0
 
tjyoungAuthor Commented:
The direction is excellent. Thanks John for your input. Very cool.
0
 
Ray PaseurCommented:
Thanks for the points and thanks for using EE!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.