I would like to search my table having a column of first names and a column of last names. I currently accept a search term from a field and compare it against both columns, one at a time with
select * from table where first_name like '%$search_term%' or
last_name like '%$search_term%';
This works fine with single word search terms but the result set includes everyone with the name "Larry". But if someone enters a first name then a space, then a last name, I want a narrower search result. I've tried the following without success.
select * from table where first_name like '%$search_term%' or last_name
like '%$search_term%' or concat_ws(' ',first_name,last_name)
like '%$search_term%';
Any advice?
EDIT: The name I'm testing with is "Larry Smith". The db stores "Larry" in the "first_name" column, and "Smith" in the "last_name" column. The data is clean, no extra spaces and the search term is trimmed left and right.
EDIT 2: I tried Robert Gamble's answer out this morning. His is very similar to what I was running last night. I can't explain it, but this morning it works. The only difference I can think of is that last night I ran the concat function as the third "or" segment of my search query (after looking through first_name and last_name). This morning I ran it as the last segment after looking through the above as well as addresses and business names.
Does running a mysql function at the end of a query work better than in the middle?
SELECT *,concat_ws(' ',first_name,last_name) AS whole_name FROM users HAVING whole_name LIKE '%$search_term%'
...is probably what you want.
There's a few things that could get in the way - is your data clean?
It could be that you have spaces at the end of the first name field, which then means you have two spaces between the firstname and lastname when you concat them? Using trim(first_name)/trim(last_name) will fix this - although the real fix is to update your data.
You could also this to match where two words both occur but not necessarily together (assuming you are in php - which the $search_term variable suggests you are)
$whereclauses=array();
$terms = explode(' ', $search_term);
foreach ($terms as $term) {
$term = mysql_real_escape_string($term);
$whereclauses[] = "CONCAT(first_name, ' ', last_name) LIKE '%$term%'";
}
$sql = "select * from table where";
$sql .= implode(' and ', $whereclauses);
you can do that (work in mysql) probably other SQL too.. just try this:
select * from table where concat(' ',first_name,last_name)
like '%$search_term%';
Note that the search query is now case sensitive.
When using
SELECT * FROM table WHERE `first_name` LIKE '%$search_term%'
It will match both "Larry" and "larry". With this concat_ws, it will suddenly become case sensitive!
This can be fixed by using the following query:
SELECT * FROM table WHERE UPPER(CONCAT_WS(' ', `first_name`, `last_name`) LIKE UPPER('%$search_term%')
Edit: Note that this only works on non-binary elements. See also mynameispaulie's answer.
To Luc:
I agree with your answer, although I would like to add that UPPER only works on non-binary elements. If you are working with say an AGE column (or anything numeric) you will need to perform a CAST conversion to make the UPPER function work correctly.
SELECT * FROM table WHERE UPPER(CONCAT_WS(' ', first_name, last_name, CAST(age AS CHAR)) LIKE UPPER('%$search_term%');
Forgive me for not responding to Luc's answer directly but for the life of me I could not figure out how to do that. If an admin can move my post, please do so.
SELECT *,concat_ws(' ',first_name,last_name) AS whole_name FROM users HAVING whole_name LIKE '%$search_term%'
...is probably what you want.
There's a few things that could get in the way - is your data clean?
It could be that you have spaces at the end of the first name field, which then means you have two spaces between the firstname and lastname when you concat them? Using trim(first_name)/trim(last_name) will fix this - although the real fix is to update your data.
You could also this to match where two words both occur but not necessarily together (assuming you are in php - which the $search_term variable suggests you are)
$whereclauses=array();
$terms = explode(' ', $search_term);
foreach ($terms as $term) {
$term = mysql_real_escape_string($term);
$whereclauses[] = "CONCAT(first_name, ' ', last_name) LIKE '%$term%'";
}
$sql = "select * from table where";
$sql .= implode(' and ', $whereclauses);
SELECT *,concat_ws(' ',first_name,last_name) AS whole_name FROM users HAVING whole_name LIKE '%$search_term%'
...is probably what you want.
you can do that (work in mysql) probably other SQL too.. just try this:
select * from table where concat(' ',first_name,last_name)
like '%$search_term%';
You can try this:
select * FROM table where (concat(first_name, ' ', last_name)) = $search_term;
To Luc:
I agree with your answer, although I would like to add that UPPER only works on non-binary elements. If you are working with say an AGE column (or anything numeric) you will need to perform a CAST conversion to make the UPPER function work correctly.
SELECT * FROM table WHERE UPPER(CONCAT_WS(' ', first_name, last_name, CAST(age AS CHAR)) LIKE UPPER('%$search_term%');
Forgive me for not responding to Luc's answer directly but for the life of me I could not figure out how to do that. If an admin can move my post, please do so.
Source: Stackoverflow.com