SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    London, UK
    Posts
    506
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Inserting foreign character into mysql

    Hello Guys,

    I am struggling to understand or work out how to insert foreign characters such Arabic, Japanese into mysql.

    I've tried the follwing, but none of them work.

    try 1:
    PHP Code:
    mysql_query("SET NAMES 'utf8'");
    mysql_query("SET CHARACTER SET utf8");
    $string "غير متوفرة";
    $sql="Update strings set hi = '".$string."' ";
    if(!
    $run=mysql_query($sql)){
        echo 
    mysql_error();

    try 2:
    PHP Code:
    mysql_query("SET NAMES 'utf8'");
    mysql_query("SET CHARACTER SET utf8");
    $string utf8_encode("غير متوفرة");
    $sql="Update strings set hi = '".$string."' ";
    if(!
    $run=mysql_query($sql)){
        echo 
    mysql_error();

    look at this url http://devzone.zend.com/images/articles/4469/image2.jpg how has this guy managed to do it? Any ideas plzzz

  2. #2
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    London, UK
    Posts
    506
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    bump...any ideas anyone plz

  3. #3
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What is your field's collation? As far as i know it should be 'utf8_general_ci' and your table's charset should be 'utf8'.
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  4. #4
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    London, UK
    Posts
    506
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by rajug View Post
    What is your field's collation? As far as i know it should be 'utf8_general_ci' and your table's charset should be 'utf8'.
    Yes they are in utf. here you see:

    CREATE TABLE langs (
    id char(50) character set utf8 NOT NULL,
    name char(50) character set utf8 NOT NULL,
    meta char(50) character set utf8 default NULL,
    error_text char(50) character set utf8 NOT NULL,
    encoding char(50) character set utf8 default NULL,
    PRIMARY KEY (id))
    ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;



    CREATE TABLE strings (
    string_id char(100) character set utf8 NOT NULL,
    page_id char(50) character set utf8 NOT NULL,
    en text character set utf8 NOT NULL,
    de text character set utf8 NOT NULL,
    hi text character set utf8 NOT NULL,
    PRIMARY KEY (string_id)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

    when i insert from php it shows as ?? ???? in the database. I even changed the mysql my.ini to this:

    [client]

    port=3306

    default-character-set=utf8

    [mysql]

    default-character-set=utf8


    yet no luck

  5. #5
    SitePoint Wizard silver trophybronze trophy Stormrider's Avatar
    Join Date
    Sep 2006
    Location
    Nottingham, UK
    Posts
    3,133
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Could it be that it is inserting correctly, but whatever you are using to show the database isn't displaying it properly?

  6. #6
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I also have the same problem:
    When I look at mysql interface in the command prompt, the characters are unreadable.

    However, when I display the records on my browser, everything is in order and it works fine.

    I don't know whether mysql in the command prompt window will display non-roman scripts correctly, but it does fine when accessing it from a web interface with a up-to-date browser. Do you need to read the records in the command prompt or can you get by with relying only on viewing from a browser?

  7. #7
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    London, UK
    Posts
    506
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Stormrider View Post
    Could it be that it is inserting correctly, but whatever you are using to show the database isn't displaying it properly?
    hey stormrider, when i check the db it shows ? marks

    when I render on the brwowser it also shows as ? marks

  8. #8
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    London, UK
    Posts
    506
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by swto View Post
    I also have the same problem:
    When I look at mysql interface in the command prompt, the characters are unreadable.

    However, when I display the records on my browser, everything is in order and it works fine.

    I don't know whether mysql in the command prompt window will display non-roman scripts correctly, but it does fine when accessing it from a web interface with a up-to-date browser. Do you need to read the records in the command prompt or can you get by with relying only on viewing from a browser?
    swto, so how does it show in your db? are you simply echoing it out on the browser that it works fine?

    this is how i render it on the browser
    PHP Code:
    require_once 'Translation2.php';

    // define language table parameters
    $params = array(
      
    'langs_avail_table'     => 'langs',
      
    'lang_id_col'           => 'id',
      
    'lang_name_col'         => 'name',
      
    'lang_meta_col'         => 'meta',
      
    'lang_errmsg_col'       => 'error_text',
      
    'lang_encoding_col'     => 'encoding',
      
      
    'strings_default_table' => 'strings',
      
    'string_id_col'         => 'string_id',
      
    'string_page_id_col'    => 'page_id',
      
    'string_text_col'       => '%s',
    );

    // initialize translation engine
    $tr =& Translation2::factory('MDB2''mysql://root:ma201dq@localhost/internationalization'$params);

    // set character set
    $tr->setCharset('utf8');

    // set language
    if (!isset($_GET['langs'])) {
      
    $tr->setLang('en');
    } else {
      
    $tr->setLang($_GET['langs']);  
    }

    // set error handling
    PEAR::setErrorHandling(PEAR_ERROR_DIE);

    // get translated string in set language
    echo $tr->get('how_are_you''basic'); 
    this is the bit that renders it:
    // get translated string in set language
    echo $tr->get('how_are_you', 'basic');

  9. #9
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is a list to consider for displaying utf8 correctly:

    1) Each table must be set as utf8:
    CREATE TABLE name {
    ...
    } DEFAULT CHARACTER SET utf8;

    2) DB connection php code:
    PHP Code:
    PHP Code:
    <?php 
    $link 
    mysqli_connect('localhost''root''your password'); 
    ... 
    if (!
    mysqli_set_charset($link'utf8')) 

    $output 'Cannot set utf8 encoding.'
    include 
    'your output.php' 
    exit(); 

    ?>
    This will connect you to the db with utf8, if not it will tell you so.

    3)
    echo out your values like this:
    PHP Code:
    PHP Code:
    <?php echo htmlspecialcharacters($table_name['value'], ENT_QUOTES'UTF-8'); ?>
    4)
    set your webpages as charset utf8 and save your code in utf8 encoding.


    5) Finally, you should also make sure the browser you are using is properly configured to read utf8.

  10. #10
    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)
    The collation is irrelevant - Only the encoding matters. The encoding can be set in multiple places though and they all need to match up.

    First: What encoding did you save your php-file in? Check in your text editor if you don't know.

    Second: On the page where you display the string, which encoding are you serving the page with? You can check this by right-clicking and selecting View Page Info.

  11. #11
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    London, UK
    Posts
    506
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by swto View Post
    Here is a list to consider for displaying utf8 correctly:

    1) Each table must be set as utf8:
    CREATE TABLE name {
    ...
    } DEFAULT CHARACTER SET utf8;

    2) DB connection php code:
    PHP Code:
    PHP Code:
    <?php 
    $link 
    mysqli_connect('localhost''root''your password'); 
    ... 
    if (!
    mysqli_set_charset($link'utf8')) 

    $output 'Cannot set utf8 encoding.'
    include 
    'your output.php' 
    exit(); 

    ?>
    This will connect you to the db with utf8, if not it will tell you so.

    3)
    echo out your values like this:
    PHP Code:
    PHP Code:
    <?php echo htmlspecialcharacters($table_name['value'], ENT_QUOTES'UTF-8'); ?>
    4)
    set your webpages as charset utf8 and save your code in utf8 encoding.


    5) Finally, you should also make sure the browser you are using is properly configured to read utf8.
    thanks for the run down swto

  12. #12
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    London, UK
    Posts
    506
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kyberfabrikken View Post
    The collation is irrelevant - Only the encoding matters. The encoding can be set in multiple places though and they all need to match up.

    First: What encoding did you save your php-file in? Check with in your text editor if you don't know.

    Second: On the page where you display the string, which encoding are you serving the page with? You can check this by right-clicking and selecting View Page Info.
    kyberfabrikken spot on when i right clicked on my browser it was encoding on western European, so i changed it utf8, but why does php not cater for this. Even when I make it explicit in php like below it won’t work:

    echo utf8_decode($row['hi']);
    echo htmlspecialchars($row['hi'], ENT_QUOTES, 'UTF-8');

  13. #13
    SitePoint Evangelist
    Join Date
    Apr 2005
    Location
    London, UK
    Posts
    506
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kyberfabrikken View Post
    .

    Second: On the page where you display the string, which encoding are you serving the page with? You can check this by right-clicking and selecting View Page Info.
    i just noticed I was not serving it with any encoding, hence why it did not render properly. so i placed a header('Content-Type: text/html; charset=UTF-8'); and it now works fine.

    thanks all

  14. #14
    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)
    Quote Originally Posted by ma201dq View Post
    i just noticed I was not serving it with any encoding, hence why it did not render properly. so i placed a header('Content-Type: text/html; charset=UTF-8'); and it now works fine.
    Yup. When you don't define the encoding, most things will assume iso-8859-1.

  15. #15
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No worries - a lot of this is taken from Kevin Yank's Book 'Build Your Own Database-Driven Website', chapters 3-5. You may want to check out this book.


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
  •