search by firtst name or last name or first and last combined?

jasondavis

Here is my query, it will search a user input name for first name or for last name but when a user types in First name SPACE Last name it returns nothing, how can I make it search this way?

select * from friend_reg_user where 1=1 
and (fname like '%$search%' AND lname like '%$search%') and status='Active'

Like my guess is this part is fine for search for first or last name but in the user submitted name I need to search for a "jason+davis" the + and count the first word as a varibale and the 2nd word as a variable

rulian

you wouldnt need subqueries, just WHERE clause AND/OR 's
example

select * from friend_reg_user where fname like '%$search%' OR lname like '%$search%' and status='Active'

or, if you are little more keen on DB stuff, use MyISAM tables and do full text searches and use MATCH/ AGAISNT

jasondavis

sorry that was a typo I have updated with what i have, Im just looking for a way to search for a 1st and last name when submitted as 1 string of text

rulian

What you'll probably need is reg ex at this point.
Some form of look ahead and reverse look ahead to match a string front to back, back to front. You may pull alot of unwanted information like that though

wilku

You can concatenate fields of a table to look in both fields (as well any of them):

select * from friend_reg_user where 1=1
and concat(fname,' ', lname) like '%$search%' and status='Active'

Still if user will make a 'typo' inserting two spaces between first and last name, this query won't work.
An alternative would be to split search string with a space and look for individual words (using a clever mixture of AND and OR 😉 )

jasondavis

thats works well, im guessing for performance "split search string with a space and look for individual words" might be faster?

wilku

I'm not sure but I think the result of concat() can't be indexed, so it will be slower using concat.