SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2007
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Same column from one table but gives differently

    Hi gurus,
    I am new to this forum & PHP/MySQL, I am learning PHP and MySQL. I have a table with 2 columns, "Name" & "Value". I have 2 rows:

    ------------------------------------
    |Name | Value | /* table columns*/
    -------------------------------------
    ------------------------------------
    |'EmailSubject'| 'Account Activated' | /* row 1 */
    ------------------------------------
    |'EmailBody' | 'BlahBlah...' | /* row 2 */
    ------------------------------------

    My problem is that when I used CHinese characters for the 2 rows' value fields, my email client could display the email body's chinese characters properly but the email subject was displayed as garbage.

    This is very strange, can you help?

    Thanks,
    Charlie
    Last edited by Charlie123; Feb 6, 2007 at 17:21. Reason: formating

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    do the characters look ok in phpmyadmin?

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2007
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for reply.
    No. In PhpMyAdmin, all the chinese characters in both rows were displayed as garbage but in my web admin page the 2 rows chinese characters were displayed correctly.
    In the hpMyAdmin page, I can have MySQL document display Chinese version properly.
    I tried this table's char set to both utf-8 and gb2312 without luck in PhpMyAdmin interface.

    Here is my MySQL server query:

    mysql> show variables like 'char%';
    +--------------------------+----------------------------+
    | Variable_name | Value |
    +--------------------------+----------------------------+
    | character_set_client | latin1 |
    | character_set_connection | latin1 |
    | character_set_database | utf8 |
    | character_set_results | latin1 |
    | character_set_server | latin1 |
    | character_set_system | utf8 |
    | character_sets_dir | /usr/share/mysql/charsets/ |
    -----------------------------------------------------------
    Last edited by Charlie123; Feb 6, 2007 at 19:30. Reason: addition info.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    well, that explains why your characters are screwed up in phpmyadmin. but that doesn't matter. as long as the data is ok when it goes in and comes out, then don't worry about that now.

    overall, this means there isn't a mysql problem.

    i think you need to find a forum that knows something about the intricacies of formatting email messages to allow utf8 characters in an email subject line. they'll be able to tell you how to generate the email properly.

  5. #5
    SitePoint Enthusiast
    Join Date
    Feb 2007
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, but what explains the screwups in PhpMyAdmin interface?
    In PHP language, what is used for sending emails?

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    the screw-ups are easy. your PHP page is utf8. your mysql connection mode is latin1. you sent utf8 data across the connection. the table is defined as utf8. mysql did a latin1 -> utf8 conversion on the data when it stored the data. the same thing happens in reverse when you retrieve the data from mysql. you lucked out that all of the characters you have used so far have reversible mappings!

    and i have no idea how you're sending your emails in php. there is more than one way to do it!

  7. #7
    SitePoint Enthusiast
    Join Date
    Feb 2007
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks.
    I have one more question: what is character_set_database? I know a DB's charset defaults to character_set_server.
    In the manul it says:
    "The character set and collation of the default database can be determined from the values of the character_set_database and collation_database system variables."
    I don't know what is the "default database".

    And what is character_set_system? I am veru confused by the manul.
    Thanks,
    Charlie

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    "default database", a.k.a. the "currently selected" database.

    the system character set defines the character set of table names, column names, database names, etc.

  9. #9
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Charlie, you might want to look at this article
    as well as this one to see why your table design should be avoided.

  10. #10
    SitePoint Enthusiast
    Join Date
    Feb 2007
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys! I also read the 2 articles recommended by quelphdad, they are great in teaching me into the DB desgin principles. I bought the PHP/MySQL book by Kevin Yank and still learning it. I am also reading the MySQL manul.

    Can I say this:
    When you create a new DB, its charset is defaulted to character_set_server if you don't explicitly specify it, character_set_database is not involved in DB creation.
    After you have created a DB and selected it as default database, character_set_database is derived from this DB's character set by MySQL server. In the other word, character_set_database can never be assigned by a MySQL client program. If you don't select a DB to use, character_set_database is NULL.

    Please comment my above saying.

    Another question: I need to set up a new DB for chinese web site, using PHP/MySQL, I am going to create the DB with utf-8, to call "SET NAMES utf8" in PHP code, to set charset to utf-8 in HTML/PHP code, is it right or more needed to be done/corrected?
    Last edited by Charlie123; Feb 7, 2007 at 18:37. Reason: more info.

  11. #11
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    with the exception of the NULL thing (which i'm not in a position to test right now), your entire post appears accurate.

  12. #12
    SitePoint Enthusiast
    Join Date
    Feb 2007
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks,
    Is my saying in the previous post about Chinese characters also correct?

    This call: mysql_query("SET NAMES 'utf8'"); Should it be right after connection or after selecting a DB?
    Regards,
    Charlie

  13. #13
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    yes

  14. #14
    SitePoint Enthusiast
    Join Date
    Feb 2007
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    email subject still gives wrong

    Hi guys, thanks for help.

    I created a fresh DB with utf-8 and all html set as utf-8, my PHP scripts still have problem with email subject after I applied "SET NAMES 'utf8'" and did a complete fresh install of PHP application.

    Here is the code:
    if( 'HTML' == $aRecipientInfo['EmailFlag'])
    {
    $sMailHeader = "MIME-Version: 1.0\r\n" . "Content-type: text/html; charset=utf-8\r\n" . $sMailHeader;
    $iSendingResult = mail( $sRecipientEmail, $sMailSubject, $sMailBody, $sMailHeader, $sMailParameters );
    }

    The sMailSubject & sMailBody are exactly in the same retrieved from same table(utf-8). My admin page can display email subject & body in Chinese properly, my PHPMyAdmin also displays chinese properly for the email subject & body. But in the real email client ( hotmail or outlook etc), the email subject from my web site was displayed as garbage or some times always give me arbitrary 2 chinese characters.
    I am stuck here for a long time, any help?
    Thanks a lot.
    Charlie

  15. #15
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    so obviously, this isn't a problem with mysql and PHP, per se.
    Quote Originally Posted by longneck View Post
    i think you need to find a forum that knows something about the intricacies of formatting email messages to allow utf8 characters in an email subject line. they'll be able to tell you how to generate the email properly.
    you have to do something special to be able to use utf-8 characters in a subject line. i don't know what that is. that is why i suggest asking in a forum of email specialists.


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
  •