Match single input and two database fields

i'm building a search form for a website, with a 开发者_JAVA百科single input fields (name and lastname) and i need it to match against two database fields (NAME_FIELD and LNAME_FIELD). Obviously users can type in the name first or the lastname (or only the lastname) as they prefer. Thank you

Assuming the user inputs are NULL when not used:

select *
    from YourTable
    where NAME_FIELD = coalesce(@name, NAME_FIELD)
        and LNAME_FIELD = coalesce(@lastname, LNAME_FIELD)

Assuming you are using prepared statements:

WHERE (:name IS NULL OR NAME_FIELD LIKE :name) 
  OR (:lname IS NULL OR LNAME_FIELD LIKE :lname)

Have a look at MyISAM Full Text Search, which will lead to much better performance then the LIKE operator.

The syntax may be off a bit, but you could do something like:

WHERE 'name_field' LIKE %'YOUR_INPUT'% OR 'lname_field' LIKE %'YOUR_INPUT'%

You can use = 'YOUR_FIELD' to match it exactly.