Select from 2 fields with one textbox

Frankiee · #1 (**permalink**) 03-08-11, 04:17

Hi all,

I've got a table Customers, with firstname and surname as fields. On my page I've got a single textbox where it should be possible to search for customers.

The search should be possible in three different ways:

Only firstname (Joe)
Only surname (Smith)
Or both. (Joe Smith)

Can anyone help me with this?

Thanks!

it-iss.com · #2 (**permalink**) 03-08-11, 04:53

Be aware that if you have large numbers of entries in the table you should use indexes on first name and last name.

Code:

SELECT firstname,
       lastname
FROM   table
WHERE  firstname = '$input'
        OR lastname = '$input'
        OR Concat(firstname, ' ', lastname) = '$input'

If performance becomes a big issue try the separating the firstname and lastname programmatically and then issue the statement as follows:

Code:

SELECT firstname,
       lastname
FROM   table
WHERE  firstname = '$input'
        OR lastname = '$input'
        OR ( firstname = '$inputfirstname'
             AND lastname = '$inputlastname' )

Frankiee · #3 (**permalink**) 03-08-11, 05:02

Quote:

Originally Posted by it-iss.com

Be aware that if you have large numbers of entries in the table you should use indexes on first name and last name.

Code:

SELECT firstname,
       lastname
FROM   table
WHERE  firstname = '$input'
        OR lastname = '$input'
        OR Concat(firstname, ' ', lastname) = '$input'

If performance becomes a big issue try the separating the firstname and lastname programmatically and then issue the statement as follows:

Code:

SELECT firstname,
       lastname
FROM   table
WHERE  firstname = '$input'
        OR lastname = '$input'
        OR ( firstname = '$inputfirstname'
             AND lastname = '$inputlastname' )

That worked perfectly! Thank you

Internet Services

Web Development

Digital Marketing

Consumer Tech