SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySql encoding problem - can't change to utf-8

    Hello to everyone, great community.
    I'm having this problem, and been trying for weeks now to solve it, but no luck . If you guys could help me it would be great.
    I have 3 .php files, for inserting grades into DB, update results, and one for reporting results (all files attached, I just changed extension to .txt so I can upload them).
    Problem is I can't get it to show utf-8 characters when reading from DB. I changed encoding in DB and tables, and everything is set to "utf8_general_ci", and characters are showing correctly in table when I check them via phpMyadmin.
    I would appreciate any help.
    Many thanks in advance.
    Attached Files Attached Files

  2. #2
    SitePoint Wizard
    Join Date
    Oct 2005
    Posts
    1,834
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)
    When you query the database in your code, you should explicitly instruct the database to send the data back in UTF-8. You do this in one of two ways:

    1) Before executing your query, run a set_charset() function:

    Code:
    $mysqli->set_charset('utf8');
    2) Run a SET NAMES query:

    Code:
    $mysqli->query('SET NAMES utf8');
    The reason you see the characters correctly in phpMyAdmin is because it is doing one of those things. You may not be. You should run one of those when you query or insert data into the database.

    There are other areas for you to investigate if that is not working. First off, you should ensure your PHP files are saved in UTF-8 without BOM (byte order mark) formatting using your text editor.

    Then you should ensure that the server is sending a UTF-8 charset HTML header when it returns data to your browser. You can view the header data in Firefox Web Developer Tools in the Network tab. You can explicitly set the charset in the HTML header in PHP code or by putting a line in an htaccess file. You may be already doing this. Make sure. Also, you should have a UTF-8 charset declared in your HTML in a meta element at the very top of the page after the opening head element.

    The first part specifying the character set before executing queries is where a lot of people get tripped up. So start there.

    You indicate that the database is set up properly with the database collation and text fields set to utf8_general_ci. And since you can view the data in phpMyAdmin properly, the problem probably is not with the tables.

  3. #3
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheesdude, thanks for replying.
    I'l try to present everything clear as I can. I think we can agree that it's not problem with tables, because it is set to utf8_general_ci, and everything is displaying correctly via phpMyAdmin.
    When I check encoding via Firefox (View Page Info) it says: "Encoding: UTF-8", "text/html; charset=UTF-8". Also I have some HTML (not related to tables) utf-8 characters that are showing correctly. Also, UTF-8 charset is declared in HTML in a meta element in the header. So that is OK.
    All PHP files are saved in UTF-8 without BOM (byte order mark) formatting in Notepad++.
    I forgot to mention in first post, I used SQL query in phpMyAdmin to create tables and insert some data(names etc, but grades are inserted via browser), but again as we already concluded there should be no problem with it because everything is showing properly via phpMyAdmin.
    I tried various things like:
    Code:
    /* change character set to utf8 */if (!mysqli_set_charset($link, "utf8")) {
        printf("Error loading character set utf8: %s\n", mysqli_error($link));
    } else {
        printf("Current character set: %s\n", mysqli_character_set_name($link));
    }
    After I used this I got "Error loading character set utf8".

    I tried also this:

    Code:
    $dbh = new mysqli('localhost', 'user', 'pass', 'dbname');    
    mysql_select_db($db, $dbh);    
    mysql_set_charset('utf8', $dbh);
    and this
    Code:
    $db = new mysqli('localhost', 'user', 'pass', 'dbname');    
    $mysqli->set_charset('utf8');
    After both of this I just didn't get anything. I got HTML structure (menu, header..) but where should be php data is just blank, empty.

    I'm really running out of ideas, so if you got any advice I would be grateful.
    If this could help, here it is link for site, where you can check the code.

    Sorry for my English, thank you very much for helping, I really appreciate it.

  4. #4
    SitePoint Wizard
    Join Date
    Oct 2005
    Posts
    1,834
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by leanchristmas View Post
    I tried also this:

    Code:
    $dbh = new mysqli('localhost', 'user', 'pass', 'dbname');    
    mysql_select_db($db, $dbh);    
    mysql_set_charset('utf8', $dbh);
    and this
    Code:
    $db = new mysqli('localhost', 'user', 'pass', 'dbname');    
    $mysqli->set_charset('utf8');
    After both of this I just didn't get anything. I got HTML structure (menu, header..) but where should be php data is just blank, empty.
    I looked at your attached files and see you are using mysqli. Your code is bad. In your first example, you are attempting to create a database object using mysqli but then attempting to use mysql functions.

    PHP Code:
    $dbh = new mysqli('localhost''user''pass''dbname');    
    mysql_select_db($db$dbh);    
    mysql_set_charset('utf8'$dbh); 
    You can't do that. You can't start with mysqli then go to mysql. Whatever your existing code is, mysql or mysqli, all you should need to do is stick the same kind of set_charset function inside your code right before you start executing queries.

    In your second example, you are creating a database object, assigning it to variable $db, but then are not using database object $db in your code.

    PHP Code:
    $db = new mysqli('localhost''user''pass''dbname');    
    $mysqli->set_charset('utf8'); 
    The second line there should be:

    PHP Code:
    $db->set_charset('utf8'); 
    I'll use an example from your file exampleReport.txt. Why don't you try it this way: create the database object, run the set_charset function, then execute your query.

    PHP Code:
    // Create database object.
    $db = new mysqli(HOST,USERNAME,PASSWORD,DATABASE);

    // After you create the database object, set the character set to UTF-8 as in the following line:
    $db->set_charset('utf8');

    $sql "SELECT attrId, description
        FROM attribute
        ORDER BY attrId"
    ;

    // Then execute your query.
    $res $db->query($sql); 

  5. #5
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It works perfectly
    Can't thank you enough


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
  •