Right now I'm just using a simple
WHERE name LIKE '%$ser%'
But I'm running into an issue - say the search is Testing 123 and the "name" is Testing, it's not coming back with any results. Know any way to fix it? Am I doing something wrong?
-
1Can you explain what you are trying to do and why you expect searching for 'testing 123' to give a positive match on the name 'testing'? And when don't you want it to match?Mark Byers– Mark Byers2010-06-24 20:08:18 +00:00Commented Jun 24, 2010 at 20:08
Add a comment
|
6 Answers
If you want to search for 'Testing' or '123' use OR:
WHERE (name LIKE '%Testing%' OR name LIKE '%123%')
Note however that this will be very slow as no index can be used and it may return some results you didn't want (like "4123"). Depending on your needs, using a full text search or an external database indexing product like Lucene might be a better option.
Comments
That's how LIKE works - it returns rows that completely contain the search string, and, if you use "%" optionally contain something else.
If you want to see if the field is contained in a string, you can do it this way:
SELECT * FROM `Table` WHERE "Testing 123" LIKE CONCAT("%",`name`,"%")
4 Comments
OMG Ponies
Don't use the CONCAT means - that, like wildcarding the left side of a LIKE, will ensure an index can not be used.
Scott Saunders
After reading Mr. Byers' answer, I think I misunderstood this question. My query will find all names that are completely contained within the search term. This is NOT what you want to do if your goal is to find names that partially match one part of the search term OR another part.
Mark Byers
Maybe... but it could equally be that you have understood the question correctly. I have reread the question and I still can't tell what is meant. I think either of our answers could be a correct interpretation. I'm hoping that Belgin Fish will clarify things.
Scott Saunders
@Mark Byers: Looks like he's marked you correct. Good work interpreting the question - and answering it.
As Scott mentioned, you cannot check to see if the search contains the column value, it works the other way round.
so if $ser = "testing" and table has a row name = testing 123 it will return
For what you're trying to do you'll need to tokenize the search query into terms and perform an OR search with each of them or better still check out mysql full text search for a much better approach
Comments
After the variable $ser is replaced, the query is:
WHERE name LIKE '%Testing 123%'
You should build the query separating by words:
WHERE name LIKE '%$word[1]%$word[2]%'
3 Comments
Andy
I'm pretty sure that yields the same result.
OMG Ponies
Only diff is the string concatenation in PHP - doesn't answer the question
Facundo Ch.
It's not the same result, due to the % between the words. And it is more exact and efficient than using OR with two likes.
not efficient (as your example) but working as you want:
WHERE name LIKE '%$ser%' OR '$ser' LIKE CONCAT('%', name, '%')
Comments
As mentioned by Mark and others, a full text search method may be better if possible.
However, you can split the search string on word boundary and use OR logic—but check for the whole string first, then offer the option to widen the search:
NOTE: Input sanitization and preparation not shown.
1. Query with:
$sql_where = "WHERE name LIKE '%$ser%'";
2. If zero results are returned, ask user if they would like to query each word individually.
3. If user requests an 'each word' search, query with:
$sql_where = get_sql_where($ser);
(Working) Example Code Below:
$ser = 'Testing 123';
$msg = '';
function get_sql_where($ser){
global $msg;
$sql_where = '';
$sql_where_or = '';
$ser = preg_replace("/[[:blank:]]+/"," ", trim($ser)); //replace consecutive spaces with single space
$search_words = explode(" ", $ser);
if($search_words[0] == ''){
$msg = 'Search quested was blank.';
}else{
$msg = 'Search results for any of the following words:' . implode(', ', $search_words);
$sql_where = "WHERE name LIKE '%$ser%'";
foreach($search_words as $word){
$sql_where_or .= " OR name LIKE '%$word%'";
}
}
return $sql_where . $sql_where_or;
}
$sql_where = get_sql_where($ser);
//Run query using $sql_where string
Comments
