Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2009
    Posts
    6

    Unanswered: Select from 2 fields with one textbox

    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!

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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' )
    Last edited by it-iss.com; 03-08-11 at 05:59.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Feb 2009
    Posts
    6
    Quote Originally Posted by it-iss.com View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •