SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Oct 2008
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How To Store Usernames With Special Characters

    This may sound like a bit of a stupid question, but using MySQL (5.0), what is the best way to store a username that contains special characters (such as Spanish or Portuguese characters like , , , but not limited to these, any character really)?

    When taking a registration from someone, should I run the username through PHP's htmlentities first before inserting it into the database and then whenever querying it, run the query string htmlentities also?

    Also, MySQL seems to by default, store all characters as "latin1_swedish_ci". Is it advisable to store them as "utf8_unicode_ci"?

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    look into CHARACTER SETS (i'd use UTF-8) and COLLATION which tells the characters how to sort alphabetically.

  3. #3
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This might go ahead of future problems. I too went through storing accented char in a MYSQL db, from a web app in PHP.
    As said earlier you need to set the DB charset and collation.
    I hadn't realized that the default charset was latin1 which might be fine, but I recreated my db specifying utf8 and an ut8 collation (spanish_ci for me).
    Things were ok except for 2 problems :
    - when browsing the db with phpmadmin the accented characters would not display correctly (they were fine when retrieved and display on my web pages with php), which I found suspect
    - searches (select statements) with MATCH AGAINST or LIKE would not be accent insensitive, which was a problem (and it was supposed to be with the choosen collation).
    After a couple of hours I found this post that explained something I had not looked into at all :
    forums dot mysql dot com slash read dot php?103,46870,47245

    Even though everything had the correct encoding (utf8) : web pages, tables, etc. ONE thing was still in latin1 :
    Code:
    character_set_server
    You can check that by running the following in mysql :
    Code:
    show variables like 'character_set%';
    So what fixed ALL my problems listed above was just to add the following line after I created the db connection in PHP :
    Code:
    mysql_query("SET NAMES 'utf8'");
    Once you do that, you will have to REENTER the data in your DB.

    Note: if you want to verify that your table has the expected encoding you can run the following in phpmyadmin:
    Code:
    show create table your_table_name;
    I hope this will help. I lost a couple of hours on that before I got it right so I'm posting it hoping it will help people get it right faster.

    I am copying the content of the post where I found that explanation in case it becomes u available:

    As is often the case, I guess your program is using default mysql client library, which is compiled with latin1 character set.

    In that case, even if you enter valid utf8 values, the client interpretes the values as latin1. Then tell the server 'Hey, these values are encoded in latin1'. When the values are stored in the table, MySQL thinks 'Oh, the values passed from the client is latin1 but the table is utf8. I have to convert it!' That's why the values becomes non-utf8.

    By executing "set names utf8", it sets the client character set variables to utf8. So the values sent from the client program are stored 'as is' in the table because the client character set and the table's character set is the same.

    This is FAQ. I think this behavior and workaround should be documented somewhere in the manual. Or client library compiled with different character sets should be downloadable.
    forums dot mysql dot com slash read dot php?103,46870,47245
    [site doesn't let me post url]


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
  •