SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Ignore case sensitive when searching in mysql db.

    I'm trying to make an search script and it works fine if I write the username exactly as written in db with capital letters and lowercase letters. But I want to be able to search ignoring uppercase and lowercase... How do I do so?

    PHP Code:
    $search $_GET['q'];
        
    $sql mysql_query("SELECT * FROM ".$prefix."_users WHERE email = '$q' OR username = '$q'");
    $row mysql_fetch_assoc($sql); 
    Thanks in advance :-)

  2. #2
    SitePoint Enthusiast
    Join Date
    Jun 2007
    Location
    Frankfurt/Germany
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    By default MySQL is not case-sensitive unless you search against a binary string. You should consider changing your db design to store email addresses or usernames inside varchar fields.

    However, there is a workaround using the COLLATE operator (pls check http://dev.mysql.com/doc/refman/5.0/...nsitivity.html)

  3. #3
    SitePoint Addict
    Join Date
    Apr 2009
    Posts
    357
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Another quick and dirty workaround if your DB does case-sensitive comparison is to use UPPER() or LOWER() on the search string and the target field data.
    Doug G
    =====
    "If you ain't the lead dog, the view is always the same - Anon

  4. #4
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    or convert all data going into the database to lower case first.


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
  •