SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    A search field that looks in several columns

    I am trying to streamline my PHP code by making only one search field instead of having several. This one field will allow you to type in a person's ID, part of their name, email, etc. Here is my query:


    select date_format(registerDate, '%m-%d-%Y') as registerDate, uID, firstname, lastname, concat( firstname, ' ', lastname ) as fullname,
    city, state, visited, date_format(last, '%m-%d-%Y') as last, member, subscription, totalSearches from users
    where uID = 'smith' or
    membernumber = 'smith' or
    email like '%smith%' or
    phone like '%smith%' or
    city like '%smith%' or
    concat( firstname, ' ', lastname ) like '%smith%'
    order by uID;

    The problem is this query is returning lots of results. It should only be showing users with the last name of smith. Can someone point me in the right direction?

    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    It should only be showing users with the last name of smith.
    if that's what you really want, then don't look for 'smith' in any column other than lastname
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    But how else can I have one search field check the contents of multiple columns? And can you explain why my query above is not working?

    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com

  4. #4
    SitePoint Zealot
    Join Date
    Apr 2009
    Location
    Wenatchee, WA
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you need to be able to search all fields from only one search box, or would it work to have it where the user can select which field they want to search on from a selection/drop-down box, and then have your query search *that* field for the string they enter in a text box?


Bookmarks

Posting Permissions

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