SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    Not yet perfect mattalexx's Avatar
    Join Date
    Oct 2005
    Location
    Taos, NM, US
    Posts
    441
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Encoding issue from PHP to MySQL

    I am trying to save Spanish characters to a MySQL database. In this example, I'm trying to save the character "ñ".

    The database is collation "utf8_general_ci".
    The table is collation "utf8_general_ci".
    The field I'm writing to is collation "utf8_general_ci"
    The file that the PHP code is written in is encoded UTF-8.

    This works:
    PHP Code:
    mysql_query("UPDATE app_properties SET lan_coverage = 'Pi".chr(241)."on/Juniper' WHERE id = 1"); 
    This doesn't work:
    PHP Code:
    mysql_query("UPDATE app_properties SET lan_coverage = 'Piñon/Juniper' WHERE id = 1"); 
    Adding a SET NAMES call makes no difference:
    PHP Code:
    mysql_query("SET NAMES 'UTF-8'");
    mysql_query("UPDATE app_properties SET lan_coverage = 'Piñon/Juniper' WHERE id = 1"); 
    Any idea what could be going on here?
    Matt Alexander
    Alexander Site Design

  2. #2
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Collation is irrelevant. Check the charset instead. You can use either latin1 or utf8 for Spanish characters, but I would strongly recommend utf8. MySql can set the charset in three different places: per column (rare), per table or per database. They apply in that order; Eg. if you don't set a charset for the column, the table charset will apply and so on. The normal thing to do is thus to set the charset per table - not per column.

    In addition to the tables charset, you can set the charset of the connection between MySql server and client. You can do so with SET NAMES. You should set the connection charset to the same as the data that you send to it. Eg. the same charset as you use in your php application. If it differs from the table charsets, MySql will then be able to convert automatically. Again, I recommend using utf8 here, which means that every string within your php application should be utf8 encoded. And this seems to be the crux of your problem:

    This:
    PHP Code:
    mysql_query("UPDATE app_properties SET lan_coverage = 'Pi".chr(241)."on/Juniper' WHERE id = 1"
    is not utf8. The utf8 encoding for ñ is two bytes with the value of 195 177. The value 241 is the encoding in latin1.

    So it would seem that you're wrong - your files are latin1 encoded.

  3. #3
    Not yet perfect mattalexx's Avatar
    Join Date
    Oct 2005
    Location
    Taos, NM, US
    Posts
    441
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for your reply.

    I tried to encode the string before I passed it:
    PHP Code:
    mysql_query("SET NAMES 'UTF-8'");
    mysql_query(utf8_encode("UPDATE app_properties SET lan_coverage = 'Piñon/Juniper' WHERE id = 1")); 
    But the string ended up looking like this:
    Code:
    mysql> SELECT lan_coverage
        -> FROM app_properties
        -> WHERE id = 1;
    +------------------+
    | lan_coverage     |
    +------------------+
    | Piñon/Juniper |
    +------------------+
    1 row in set (0.00 sec)
    Matt Alexander
    Alexander Site Design

  4. #4
    Not yet perfect mattalexx's Avatar
    Join Date
    Oct 2005
    Location
    Taos, NM, US
    Posts
    441
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I figured it out!

    I was calling this, which is wrong:
    PHP Code:
    mysql_query("SET NAMES 'UTF-8'");
    mysql_query("UPDATE app_properties SET lan_coverage = 'Piñon/Juniper' WHERE id = 1"); 
    It should be SET NAMES 'UTF8' instead:
    PHP Code:
    mysql_query("SET NAMES 'UTF8'");
    mysql_query("UPDATE app_properties SET lan_coverage = 'Piñon/Juniper' WHERE id = 1"); 
    Code:
    mysql> SELECT lan_coverage FROM app_properties WHERE id = 1;
    +---------------+
    | lan_coverage  |
    +---------------+
    | Piñon/Juniper |
    +---------------+
    1 row in set (0.00 sec)
    Thanks again for your help.
    Matt Alexander
    Alexander Site Design

  5. #5
    SitePoint Member
    Join Date
    Dec 2006
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you! Thank you! Thank you!
    It has been more than six hours now that I am trying to find a problem for this...


    sorry for bumping an old thread.


    Quote Originally Posted by mattalexx View Post
    I figured it out!

    I was calling this, which is wrong:
    PHP Code:
    mysql_query("SET NAMES 'UTF-8'");
    mysql_query("UPDATE app_properties SET lan_coverage = 'Piñon/Juniper' WHERE id = 1"); 
    It should be SET NAMES 'UTF8' instead:
    PHP Code:
    mysql_query("SET NAMES 'UTF8'");
    mysql_query("UPDATE app_properties SET lan_coverage = 'Piñon/Juniper' WHERE id = 1"); 
    Code:
    mysql> SELECT lan_coverage FROM app_properties WHERE id = 1;
    +---------------+
    | lan_coverage  |
    +---------------+
    | Piñon/Juniper |
    +---------------+
    1 row in set (0.00 sec)
    Thanks again for your help.

  6. #6
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ahh .. .that's a gotcha alright.


Tags for this Thread

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
  •