SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast Slarti42's Avatar
    Join Date
    May 2001
    Location
    UK
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Case sensitive for some reason.

    I'm having a little trouble with a SELECT query. I keep reading here that a query like:

    select countyname from tblcounties where countyname like 'N%'

    should return the same results as:

    select countyname from tblcounties where countyname like 'n%'

    BUT with the first one I get all of the results from the table I want and the second one returns nothing. All of the values in the table hav a capital at the begining.

    Anyone know what I'm doing wrong??

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats weird I just tried it and it worked the same for me whether I used a capital letter or not. I tried

    PHP Code:
    select from tablename where field1 like 'T%';

    //Which produces the exact same result as 

    select from tablename where field1 like 't%'
    What version of MySQL are you using?
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    SitePoint Zealot zook's Avatar
    Join Date
    May 2001
    Location
    Houston, tx
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    County names are capitalized, therfor when the N is capital, you get your results, but when it's not, you don't. The reason for this could be that you are on a Unix server, which is case sensetive for just about everything.

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Except for data in a database, yes table names and db names are case-sensitive, but data should not be. Zook, try it on a unix server if you have access and you too will see that within queries data is case-insensitive. There is some other reason why its not working.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  5. #5
    SitePoint Enthusiast Slarti42's Avatar
    Join Date
    May 2001
    Location
    UK
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Nope no Unix here

    I'm running Windows 2k pro and MySQL version 3.23.33 (I think)
    The table has two coloms:
    1:CountyID - Auto Number - Primary Key
    2:CountyName - Blob - Unique - Keyname name

    I needed to make the County name unique so that people could not add another county with the same name. Would any of this cause my problem?

  6. #6
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No but the fact that you made the field a blob field is what is causing the case sensitivity. blob is for storing binary data and is case sensitive, all you need is a varchar field for your county name, and your problem should dissappear.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  7. #7
    SitePoint Enthusiast Slarti42's Avatar
    Join Date
    May 2001
    Location
    UK
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've just checked another table and it's case insensitive . I think this must be something to with the key to make it unique. I've also noticed that if you add a value with lower case at the start, then sort the result set, it gets displayed at the bottom of the list even if it starts with 'a'.

    Anyone got any ideas for script to avoid duplicate entries instead of the database doing it??

  8. #8
    SitePoint Enthusiast Slarti42's Avatar
    Join Date
    May 2001
    Location
    UK
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you SO much freddydoesphp. Thats sorted it. I dont know how that managed to change to blob because I created it the same as another table I have in the database. I'm glad I know about that now. Cheers!!!


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
  •