mySQL - Combining two fields from one table in a select
Posted: Fri Apr 25, 2008 12:51 pm
I have a basic search page that pulls a persons name information from two columns.
fname | lname
So currently, if someone searches individually for “Frank”(first name) or “Rockland”(last name) the results show up just fine
But if someone where to search for “Frank Rockland” I get no results because the first and last names are in different fields.
How should I solve this in one query? My query is already pretty complex (for my knowledge level)
I know it has to be pretty simple, I just can't find the answer.
What I want to do is something like.......
Current working query:
Thanks for the help.
fname | lname
So currently, if someone searches individually for “Frank”(first name) or “Rockland”(last name) the results show up just fine
But if someone where to search for “Frank Rockland” I get no results because the first and last names are in different fields.
How should I solve this in one query? My query is already pretty complex (for my knowledge level)
I know it has to be pretty simple, I just can't find the answer.
What I want to do is something like.......
Code: Select all
select (fname + lname as fullname) from authors
where fullname like '%searchterm%'Code: Select all
select = "select authors.*, pennames.lname as plname, pennames.fname as pfname from authors left join pennames on authors.auth_id = pennames.author_id where firstname like '%".$searchterm."%' or lastname like '%".$searchterm."%' or email like '%".$searchterm."%' or phone like '%".$searchterm."%' or cellphone like '%".$searchterm."%' or pennames.fname like '%".$searchterm."%' or pennames.lname like '%".$searchterm."%' ";