How to make a codeigniter query check if field contains?

Posted on 2013-12-30
Medium Priority
Last Modified: 2013-12-30
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:
or perhaps
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->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

Question by:tjyoung
  • 5
  • 5
LVL 111

Expert Comment

by:Ray Paseur
ID: 39745980
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%

Author Comment

ID: 39745988
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.
LVL 111

Expert Comment

by:Ray Paseur
ID: 39745996
Apparently there is also an or_like() method.  Some examples here:
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.


Author Comment

ID: 39746002
Hi, yes I saw that but thought it was only useful for testing different columns. columnA like $trim or columnB like $trim

Author Comment

ID: 39746044
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.
LVL 111

Expert Comment

by:Ray Paseur
ID: 39746062
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.

Author Comment

ID: 39746078
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?
LVL 111

Accepted Solution

Ray Paseur earned 2000 total points
ID: 39746089
Yes, that's the general idea, exactly.  you may also find the Levenshtein() distance to be useful.

Author Closing Comment

ID: 39746106
The direction is excellent. Thanks John for your input. Very cool.
LVL 111

Expert Comment

by:Ray Paseur
ID: 39746122
Thanks for the points and thanks for using EE!

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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.

Join & Write a Comment

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

586 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question