Solved

How to make a codeigniter query check if field contains?

Posted on 2013-12-30
10
1,170 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
  • 5
  • 5
10 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
Apparently there is also an or_like() method.  Some examples here:
http://ellislab.com/forums/viewthread/123755/#615783
0
 
LVL 1

Author Comment

by:tjyoung
Comment Utility
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
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
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
Comment Utility
The direction is excellent. Thanks John for your input. Very cool.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Thanks for the points and thanks for using EE!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
htacces issue 2 37
How can I do this in Pyhton? 12 66
regex expression 9 15
writting html or javascript in PHP 8 9
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now