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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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%
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.
Ray PaseurCommented:
Apparently there is also an or_like() method.  Some examples here:
http://ellislab.com/forums/viewthread/123755/#615783
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

tjyoungAuthor Commented:
Hi, yes I saw that but thought it was only useful for testing different columns. columnA like $trim or columnB like $trim
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.
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.
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?
Ray PaseurCommented:
Yes, that's the general idea, exactly.  you may also find the Levenshtein() distance to be useful.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tjyoungAuthor Commented:
The direction is excellent. Thanks John for your input. Very cool.
Ray PaseurCommented:
Thanks for the points and thanks for using EE!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.