Solved

How to make a codeigniter query check if field contains?

Posted on 2013-12-30
10
1,300 Views
Last Modified: 2013-12-30
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

0
Comment
Question by:tjyoung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 110

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%
0
 
LVL 1

Author Comment

by:tjyoung
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.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39745996
Apparently there is also an or_like() method.  Some examples here:
http://ellislab.com/forums/viewthread/123755/#615783
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 1

Author Comment

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

Author Comment

by:tjyoung
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.
0
 
LVL 110

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.
0
 
LVL 1

Author Comment

by:tjyoung
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?
0
 
LVL 110

Accepted Solution

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

Author Closing Comment

by:tjyoung
ID: 39746106
The direction is excellent. Thanks John for your input. Very cool.
0
 
LVL 110

Expert Comment

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

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

726 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