Page 1 of 1

mySQL - Combining two fields from one table in a select

Posted: Fri Apr 25, 2008 12:51 pm
by goodwinpro
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.......

Code: Select all

select (fname + lname as fullname) from authors 
where fullname like '%searchterm%'
Current working query:

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."%'  ";
Thanks for the help.

Re: mySQL - Combining two fields from one table in a select

Posted: Fri Apr 25, 2008 1:46 pm
by lafever
Have you thought of trying explode()?

Code: Select all

 
$fullname = $search_string;
$fullname = explode(" ", $fullname);
 
if (count($fullname) > 1) {
// query would search by fname = %$fullname[0]% AND lname = %$fullname[1]% OR lname = %$fullname[0]% AND fname = %$fullname[1]%
// or however you want to do it, that way they can search by lastname, firstname also
} else {
// search by first name or last name
}
 
Of course I don't know if the query can be ran like that or not. Just trying to help with some ideas. I'm sure some of the more experienced here can help a little deeper. Also, don't forget to escape your data too.

Re: mySQL - Combining two fields from one table in a select

Posted: Fri Apr 25, 2008 3:35 pm
by EverLearning
You could try something like

Code: Select all

 
// your query ... and in continuation this 
"OR CONCAT(firstname, ' ', lastname) LIKE '%$searchterm%'
OR CONCAT(lastname, ' ',firstname) LIKE '%$searchterm%'"

Re: mySQL - Combining two fields from one table in a select

Posted: Fri Apr 25, 2008 4:27 pm
by goodwinpro
Beautiful!!!! It works. Thank you.

I tried the last example first, and it worked perfectly the first time, so I didn't get to try the first suggestion.

I really appreciate it.