Using mysql concat() in WHERE clause?

Question

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?

Answer 1

SELECT *,concat_ws(' ',first_name,last_name) AS whole_name FROM users HAVING whole_name LIKE '%$search_term%'

...is probably what you want.

Answer 2

What you have should work but can be reduced to:

select * from table where concat_ws(' ',first_name,last_name) 
like '%$search_term%';

Can you provide an example name and search term where this doesn't work?

Answer 3

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);