#1
  1. No Profile Picture
    http://stealthwd.ca
    Devshed Novice (500 - 999 posts)

    Join Date
    Dec 2005
    Posts
    706
    Rep Power
    203

    utf text with special character enters empty


    Hey everyone. I have UTF-8 text that I'm trying to enter into my table. It will enter ok, unless there are
    special characters.

    Here's my example

    Code:
    INSERT INTO recipients (project_title) VALUES('Métis')
    This will enter empty text into the project_title field

    This next example does work though....

    Code:
    INSERT INTO recipients (project_title) VALUES('Mtis')
    I removed that funky e and the text enters fine.

    The field in question is a varchar
    character set utf8
    collation utf8_general_ci.

    Help please and thanks!
  2. #2
  3. Forgotten Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,013
    Rep Power
    9616
    When you wrote Métis you did not do so using UTF-8. It's more than just the table column: the client, server, and connection all need to agree on using UTF-8 for exchanging data.

    See what the settings are:
    Code:
    SHOW VARIABLES LIKE '%char%'

    Comments on this post

    • Dameon51 agrees : Looks like my DB as a whole wasn't set to UTF8. You saved my butt again!
  4. #3
  5. No Profile Picture
    http://stealthwd.ca
    Devshed Novice (500 - 999 posts)

    Join Date
    Dec 2005
    Posts
    706
    Rep Power
    203
    So I thought the problem would be solved with this, but I it still doesn't work. I kept the problem going in the php forum as I thought that was the problem, but someone suggested bringing it back here.

    Here is a sample I setup to remove all the things that are going on in our CMS and in regards to reading in the CSV file.

    PHP Code:
    <?php

    $mysqli 
    = new mysqli(
        
    "z","z",
        
    "z"
    );

    iconv_set_encoding("internal_encoding""UTF-8");
    iconv_set_encoding("output_encoding""UTF-8");
    iconv_set_encoding("input_encoding""UTF-8");

    $mysqli->set_charset('utf8');
    $mysqli->query("SET NAMES 'utf8'");
    $mysqli->query("set character_set_server = utf8");

    $mysqli->query("DELETE FROM recipients");    

    $tempQ $mysqli->query("SHOW VARIABLES LIKE '%char%'");

    while(
    $x mysqli_fetch_assoc($tempQ)){
        
    var_dump($x);
    }

    $query "INSERT INTO recipients (project_title,supervisors) 
        VALUES( 
            '(They will be leaders!) Growing up well in a northern Métis',
            'no special chars here')"
    ;

    var_dump(mb_check_encoding ($query),"UTF-8");

    $mysqli->query(
    $query    
    );


    ?>

    The output is as follows.....

    array(2) { ["Variable_name"]=> string(20) "character_set_client" ["Value"]=> string(4) "utf8" } array(2) { ["Variable_name"]=> string(24) "character_set_connection" ["Value"]=> string(4) "utf8" } array(2) { ["Variable_name"]=> string(22) "character_set_database" ["Value"]=> string(4) "utf8" } array(2) { ["Variable_name"]=> string(24) "character_set_filesystem" ["Value"]=> string(6) "binary" } array(2) { ["Variable_name"]=> string(21) "character_set_results" ["Value"]=> string(4) "utf8" } array(2) { ["Variable_name"]=> string(20) "character_set_server" ["Value"]=> string(4) "utf8" } array(2) { ["Variable_name"]=> string(20) "character_set_system" ["Value"]=> string(4) "utf8" } array(2) { ["Variable_name"]=> string(18) "character_sets_dir" ["Value"]=> string(26) "/usr/share/mysql/charsets/" } bool(true) string(5) "UTF-8"
  6. #4
  7. Forgotten Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,013
    Rep Power
    9616
    There's one more place that needs to be in UTF-8: your file. Since you wrote Métis in the file, you have to make sure it is UTF-8 too. Make sure you save it as such with your editor, and without the BOM because PHP isn't smart enough to process it.

    And your mb_check_encoding line is wrong - got the "UTF-8" in the wrong place.
  8. #5
  9. No Profile Picture
    http://stealthwd.ca
    Devshed Novice (500 - 999 posts)

    Join Date
    Dec 2005
    Posts
    706
    Rep Power
    203
    Oops.

    I changed it to

    PHP Code:
    var_dump(mb_check_encoding ($query,"UTF-8")); 
    And it returns true...

    bool(true)
  10. #6
  11. Forgotten Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,013
    Rep Power
    9616
    Then everything seems fine.

    Are you still having a problem? How and where?
  12. #7
  13. No Profile Picture
    http://stealthwd.ca
    Devshed Novice (500 - 999 posts)

    Join Date
    Dec 2005
    Posts
    706
    Rep Power
    203
    Code:
    $query = "INSERT INTO recipients (project_title,supervisors) 
        VALUES( 
            '(They will be leaders!) Growing up well in a northern Métis',
            'no special chars here')";
    When I have the é in the there, that whole project_title field enters blanks in the database.

    The supervisors field always enters fine as it has no special characters, but if I throw the é in there it enters blank too.

    So pretty much anytime I have é the field enters blank.
  14. #8
  15. Forgotten Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,013
    Rep Power
    9616
    To be clear, what do you mean by "blanks"? It insert "Mtis"? Nothing, meaning you see a totally empty project_title? Spaces?

    After the query, do another fetch loop with a SHOW WARNINGS query.
    PHP Code:
    $tempQ2 $mysqli->query("SHOW WARNINGS");
    while(
    $x mysqli_fetch_assoc($tempQ2)){
        
    var_dump($x);

  16. #9
  17. No Profile Picture
    http://stealthwd.ca
    Devshed Novice (500 - 999 posts)

    Join Date
    Dec 2005
    Posts
    706
    Rep Power
    203
    By nothing I mean the field is empty. I try to insert '(They will be leaders!) Growing up well in a northern Métis' and it comes up empty. Not a single character.

    I did your query to output the warnings. I was getting one because I didn't have a default set for one of the other fields. I fixed that and now there are no warnings.
  18. #10
  19. Forgotten Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,013
    Rep Power
    9616
    MySQL will not do stuff like clear out a value without warning about it.

    What program are you using to manually inspect all of the data in the table?
  20. #11
  21. No Profile Picture
    http://stealthwd.ca
    Devshed Novice (500 - 999 posts)

    Join Date
    Dec 2005
    Posts
    706
    Rep Power
    203
    Thought I'd put this to rest. It did end up being my database program. It's an older version of navicat from 2008. Every time I would refresh to look at the table, it would bugger up the fields with special characters.

    Funny thing is a coworker has the exact same version of navicat, and hers wouldn't do that.

    Seems like I waited lots of time because I was using out dated, or possible not properly configured dev tools.

    Lesson learned.
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2006
    Posts
    2,618
    Rep Power
    1766
    Tracking down issues like that are a total pain - well done for doing so and thank you for letting us know
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc

IMN logo majestic logo threadwatch logo seochat tools logo