Bad encoding when selecting table from mysql

Question

Defines constant for connection to database

define("DB_SERVER", "127.0.0.1");
define("DB_USER", "chutnej_app");
define("DB_PASS", "1234");
define("DB_NAME", "chutnej");

Create a database connection

$connection = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_NAME);

This is table in mysql

CREATE TABLE category (
    category_id INT NOT NULL AUTO_INCREMENT,
    category_name VARCHAR(255) UNIQUE,
    PRIMARY KEY (category_id)
    ) 
ENGINE = InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

Populate table with some data:

INSERT INTO category (category_name) VALUES ('Reportáže'), ('Rozhovory'), ('Místa');

This functions I call to select above data from the table

function get_category() { 
    global $connection;
    $query = "SELECT * FROM category";
    $categories = mysqli_query($connection, $query);  
    confirm_query($categories);

    while($category = mysqli_fetch_array($categories)) {
      echo "<option value=\"{$category['category_id']}\">{$category['category_name']} </option>";
    }  
}

Result:

<li>
  <label for="article_category">Druh článku:</label><br />
  <select name="article_category" id="article_category" type="text">
  <option value="3">M�sta</option><option value="1">Report��e</option><option value="2">Rozhovory</option> 
  </select>
</li>

Thats what i get in the broswer, All browsers do this. Encoding in broswer is set to UTF-8. Could anyone explain me what is causing the problem? Atleast aim me to right direction, that would be enough:) I thought that just by specifying UTF8 in CREATE TABLE statement I would solve encoding problems.

http://stackoverflow.com/questions/11436594/how-to-fix-double-encoded-utf8-characters-in-an-utf-8-table (change 'latin1' to 'cp1250') — Niloct, Oct 10 '13 at 19:44

score 1 · Answer 1 · answered Oct 10 '13 at 19:45

1

Use

mysqli_set_charset($connection, 'utf8');

to set the charset of the database connection to UTF-8, too. (See Docs)

answered Oct 10 '13 at 19:45

TheWolf

1,385
7
16

score 0 · Accepted Answer · answered Oct 10 '13 at 19:39

0

Execute

SET NAMES utf8

as first query after connection. You may need to rewrite current table data;

Docs:

http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html

answered Oct 10 '13 at 19:39

Peter

16,453
8
51
77

2

@DavidKošvica acctionaly TheWolf answer is better - Hint from docs: `This is the preferred way to change the charset. Using mysqli_query() to set it (such as SET NAMES utf8) is not recommended.` – Peter Oct 10 '13 at 20:25

Bad encoding when selecting table from mysql

2 Answers2