MySQL encoding parsed wrong

Question

Im trying to add some text with this command, included in my php file:

$sql = "INSERT INTO $this->table_name_category (name, marker_icon) VALUES ('".$name."', '".$marker_icon."')";

Everything looks fine in English characters, but in GREEK one's, the parsed text is being converted to question marks "??????"

Is there something else that i must take care off? Is there a way to tell INSERT command to use UTF8 in order to support Greek characters?

Thanks in advance.

My database is set to utf8_unicode_ci (tables too).

My code is:

<?php

class Store_locator_wpress_db {

    var $wpdb;
    var $table_name;
    var $table_name_category;

    function Store_locator_wpress_db() {
        global $wpdb;
        $this->wpdb = $wpdb;
        $this->table_name = $wpdb->prefix . "store_wpress";
        $this->table_name_category = $wpdb->prefix . "store_wpress_category";
        $this->table_name_category2 = $wpdb->prefix . "store_wpress_category2";
    }

    function setup_tables() {
        self::create_tables();
        self::update_stores_table();
        //self::update_categories_table();
    }

    function update_stores_table() {
        $sql = "DESCRIBE $this->table_name";
        $result = $this->wpdb->get_results($sql, 'ARRAY_N');

        for($i=0; $i<count($result); $i++) {
            $field[] = $result[$i][0];
        }

        if(!in_array('category_id',$field)) {
            $sql = "ALTER TABLE `$this->table_name` ADD `category_id` INT NOT NULL AFTER `post_id`";
            $this->wpdb->query($sql);
        }
        if(!in_array('category2_id',$field)) {
            $sql = "ALTER TABLE `$this->table_name` ADD `category2_id` INT NOT NULL AFTER `category_id`";
            $this->wpdb->query($sql);
        }
        if(!in_array('country',$field)) {
            $sql = "ALTER TABLE `$this->table_name` ADD `country` VARCHAR( 60 ) NOT NULL AFTER `email`";
            $this->wpdb->query($sql);
        }
        if(!in_array('city',$field)) {
            $sql = "ALTER TABLE `$this->table_name` ADD `city` VARCHAR( 60 ) NOT NULL AFTER `email`";
            $this->wpdb->query($sql);
        }
    }

    function update_categories_table() {
        $sql = "DESCRIBE $this->table_name_category";
        $result = $this->wpdb->get_results($sql, 'ARRAY_N');

        for($i=0; $i<count($result); $i++) {
            $field[] = $result[$i][0];
        }

        if(!in_array('marker_icon',$field)) {
            $sql = "ALTER TABLE `$this->table_name_category` ADD `marker_icon` VARCHAR( 200 ) NOT NULL AFTER `name`";
            $this->wpdb->query($sql);
        }
    }

    function create_tables() {
        $sql = "CREATE TABLE IF NOT EXISTS " . $this->table_name . " (
        `id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
        `user_id` BIGINT NOT NULL ,
        `post_id` BIGINT NOT NULL ,
        `category_id` INT NOT NULL ,
        `name` VARCHAR( 160 ) NOT NULL ,
        `logo` VARCHAR( 160 ) NOT NULL ,
        `address` VARCHAR( 160 ) NOT NULL ,
        `lat` VARCHAR( 20 ) NOT NULL ,
        `lng` VARCHAR( 20 ) NOT NULL ,
        `url` VARCHAR( 160 ) NOT NULL ,
        `description` TEXT NOT NULL ,
        `tel` VARCHAR( 30 ) NOT NULL ,
        `email` VARCHAR( 60 ) NOT NULL ,
        `created` DATETIME NOT NULL
        ) ENGINE = MYISAM;";
        $this->wpdb->query($sql);

        $sql = "CREATE TABLE IF NOT EXISTS " . $this->table_name_category . " (
        `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
        `name` VARCHAR( 120 ) NOT NULL,
        `marker_icon` VARCHAR( 200 ) NOT NULL
        ) ENGINE = MYISAM ;";

        $this->wpdb->query($sql);

        $sql = "CREATE TABLE IF NOT EXISTS " . $this->table_name_category2 . " (
        `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
        `name` VARCHAR( 120 ) NOT NULL
        ) ENGINE = MYISAM ;";

        //$this->wpdb->query($sql);
    }

    function get_locations($criteria) {
        $lat = $criteria['lat'];
        $lng = $criteria['lng'];
        $page_number = $criteria['page_number'];
        $nb_display = $criteria['nb_display'];
        $distance_unit = $criteria['distance_unit'];
        $category_id = $criteria['category_id'];
        $category2_id = $criteria['category2_id'];
        $radius_id = $criteria['radius_id'];

        $start = ($page_number*$nb_display)-$nb_display;

        if($distance_unit=='miles') $distance_unit='3959'; //miles
        else $distance_unit='6371'; //km

        $sql = "SELECT s.*, c.marker_icon, c.name category_name,
        ( $distance_unit * acos( cos( radians('".$lat."') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('".$lng."') ) + sin( radians('".$lat."') ) * sin( radians( lat ) ) ) ) AS distance 
        FROM ".$this->table_name." s
        LEFT JOIN ".$this->table_name_category." c
        ON s.category_id=c.id
        WHERE 1 ";

        if($category_id!='') $sql .= " AND category_id='$category_id'";
        if($category2_id!='') $sql .= " AND category2_id='$category2_id'";

        if($radius_id!='') $sql .= " HAVING distance<='".$radius_id."'";

        if($lat!=''&&$lng!='') $sql .= " ORDER BY distance";
        else $sql .= " ORDER BY id DESC";

        if($nb_display!='') $sql .= " LIMIT $start, $nb_display";

        $locations = $this->wpdb->get_results($sql, 'ARRAY_A');

        return $locations;
    }

    function return_nb_stores($criteria=array()) {
        $category_id = $criteria['category_id'];

        $sql = "SELECT count(*) as nb 
        FROM $this->table_name WHERE 1";

        if($category_id!='') $sql .= " AND category_id='$category_id'";

        $results = $this->wpdb->get_results($sql, 'ARRAY_A');
        return $results[0];
    }

    function return_stores($criteria=array()) {
        $id = $criteria['id'];
        $post_id = $criteria['post_id'];
        $category_id = $criteria['category_id'];

        $sql = "SELECT s.*, c.marker_icon 
        FROM $this->table_name s
        LEFT JOIN ".$this->table_name_category." c
        ON s.category_id=c.id
        WHERE 1";

        if($id>0) $sql .= " AND s.id='$id'";
        if($post_id>0) $sql .= " AND s.post_id='$post_id'";
        if($category_id>0) $sql .= " AND s.category_id='$category_id'";

        $sql .= ' ORDER BY s.created DESC';

        $results = $this->wpdb->get_results($sql, 'ARRAY_A');
        return $results;
    }

    function return_categories($criteria=array()) {
        $id = $criteria['id'];
        $sql = "SELECT * FROM $this->table_name_category WHERE 1";
        if($id>0) $sql .= " AND id='$id'";
        $sql .= ' ORDER BY name';

        $results = $this->wpdb->get_results($sql, 'ARRAY_A');
        return $results;
    }

    function return_categories2($criteria=array()) {
        $id = $criteria['id'];
        $sql = "SELECT * FROM $this->table_name_category2 WHERE 1";
        if($id>0) $sql .= " AND id='$id'";
        $sql .= ' ORDER BY name';

        $results = $this->wpdb->get_results($sql, 'ARRAY_A');
        return $results;
    }

    function return_nb_stores_by_category() {
        $sql = 'SELECT c.id, count(*) nb 
        FROM '.$this->table_name.' s, '.$this->table_name_category.' c 
        WHERE s.category_id=c.id GROUP BY s.category_id';
        $results = $this->wpdb->get_results($sql, 'ARRAY_A');
        for($i=0; $i<count($results); $i++) {
            $storesCat[$results[$i]['id']] = $results[$i]['nb'];
        }
        return $storesCat;
    }

    function return_nb_stores_by_category2() {
        $sql = 'SELECT c.id, count(*) nb 
        FROM '.$this->table_name.' s, '.$this->table_name_category2.' c 
        WHERE s.category2_id=c.id GROUP BY s.category2_id';
        $results = $this->wpdb->get_results($sql, 'ARRAY_A');
        for($i=0; $i<count($results); $i++) {
            $storesCat[$results[$i]['id']] = $results[$i]['nb'];
        }
        return $storesCat;
    }

    function delete_store($id) {
        $user_id = get_current_user_id();
        $sql = "SELECT * FROM $this->table_name WHERE id='$id' AND user_id='$user_id'";
        $results = $this->wpdb->get_results($sql, 'ARRAY_A');
        if(count($results)>0) {
            $sql = "DELETE FROM $this->table_name WHERE id='%d'";
            $this->wpdb->query($this->wpdb->prepare($sql, $id));
            return 'The store has been deleted.';
        }
        else {
            return 'Only the author of this store, can delete it.';
        }
    }

    function delete_category($id) {
        $sql = "SELECT * FROM $this->table_name WHERE category_id='$id'";
        $results = $this->wpdb->get_results($sql, 'ARRAY_A');
        if(count($results)>0) {
            return "You cannot delete this category because it's containing ".count($results)." store(s). Please delete the stores first then try again.";
        }
        else {
            $sql = "DELETE FROM $this->table_name_category WHERE id='%d'";
            $this->wpdb->query($this->wpdb->prepare($sql, $id));
            return 'The category has been deleted.';
        }
    }

    function delete_category2($id) {
        $sql = "SELECT * FROM $this->table_name WHERE category2_id='$id'";
        $results = $this->wpdb->get_results($sql, 'ARRAY_A');
        if(count($results)>0) {
            return "You cannot delete this category because it's containing ".count($results)." store(s). Please delete the stores first then try again.";
        }
        else {
            $sql = "DELETE FROM $this->table_name_category2 WHERE id='%d'";
            $this->wpdb->query($this->wpdb->prepare($sql, $id));
            return 'The category has been deleted.';
        }
    }

    function update_store($criteria) {
        $sql = "UPDATE $this->table_name SET 
        post_id='".$criteria['post_id']."', category_id='".$criteria['category_id']."', category2_id='".$criteria['category2_id']."', 
        name='".$criteria['name']."', logo='".$criteria['logo']."', url='".$criteria['url']."', 
        address='".$criteria['address']."', lat='".$criteria['lat']."', lng='".$criteria['lng']."', 
        description='".$criteria['description']."', tel='".$criteria['tel']."', email='".$criteria['email']."'
        WHERE id='".$criteria['id']."'";
        $this->wpdb->query($sql);
    }

    function update_category($criteria) {
        $sql = "UPDATE $this->table_name_category SET name='".$criteria['name']."', marker_icon='".$criteria['marker_icon']."' 
        WHERE id='".$criteria['id']."'";
        $this->wpdb->query($sql);
    }

    function update_category2($criteria) {
        $sql = "UPDATE $this->table_name_category2 SET name='".$criteria['name']."' 
        WHERE id='".$criteria['id']."'";
        $this->wpdb->query($sql);
    }

    function add_store($criteria) {
        $sql = "INSERT INTO $this->table_name 
        (user_id, post_id, category_id, category2_id, name, logo, address, lat, lng, url, description, tel, email, created) 
        VALUES ('".$criteria['user_id']."', '".$criteria['post_id']."', '".$criteria['category_id']."', '".$criteria['category2_id']."', '".$criteria['name']."', '".$criteria['logo']."', '".$criteria['address']."', '".$criteria['lat']."', '".$criteria['lng']."', 
        '".$criteria['url']."', '".$criteria['description']."', '".$criteria['tel']."', '".$criteria['email']."', '".date('Y-m-d H:i:s')."')";
        //echo $sql.'<br>';
        $this->wpdb->query($sql);
    }

    function add_category($criteria=array()) {
        $name = $criteria['name'];
        $marker_icon = $criteria['marker_icon'];
        $sql = "INSERT INTO $this->table_name_category (name, marker_icon) VALUES ('".$name."', '".$marker_icon."')";
        $this->wpdb->query($sql);
    }

    function add_category2($criteria=array()) {
        $name = $criteria['name'];

        $sql = "INSERT INTO $this->table_name_category2 (name) VALUES ('".$name."')";
        $this->wpdb->query($sql);
    }
}

?>

No i dont. The command i use is this: function add_category($criteria=array()) { $name = $criteria['name']; $marker_icon = $criteria['marker_icon']; $sql = "INSERT INTO $this->table_name_category (name, marker_icon) VALUES ('".$name."', '".$marker_icon."')"; $this->wpdb->query($sql); } — Johnathan Smith, Dec 11 '13 at 08:33

score 2 · Answer 1 · edited May 23 '17 at 12:29

2

before you do any query or CRUD in mysql issue this command

"SET NAMES utf8"
"SET CHARACTER SET utf8"

in PHP...

mysql_query("SET NAMES utf8");

to make sure everything is in UTF-8 and you use PDO... modify your PDO connection into this depending on what API you use

$pdo = new PDO( 
    'mysql:host=hostname;dbname=defaultDbName', 
    'username', 
    'password', 
    array(PDO::MYSQL_ATTR_INIT_COMMAND => "utf8") 
);

reference:

Accented characters in mySQL table

edited May 23 '17 at 12:29

Community

1
1

answered Dec 11 '13 at 08:15

Netorica

18,523
17
73
108

1

If you always work with UTF-8, you can edit your `/etc/my.cnf` file adding `init-connect='SET NAMES utf8'` in order to avoid setting it explicitly everytime you connect to MySQL from PHP. – Carlos Dec 11 '13 at 08:30
@jackflash very nice suggestion. But we have a hosting that works on databases that has specific encoding especially on japanese databases that always encode information to `SJIS` – Netorica Dec 11 '13 at 08:31
Thank you! However i tried adding it like this and it doesnt work: function add_category($criteria=array()) { mysql_query("SET NAMES utf8"); $name = $criteria['name']; $marker_icon = $criteria['marker_icon']; $sql = "INSERT INTO $this->table_name_category (name, marker_icon) VALUES ('".$name."', '".$marker_icon."')"; $this->wpdb->query($sql); } – Johnathan Smith Dec 11 '13 at 08:32
can you show us more of your codes? and update it to your question – Netorica Dec 11 '13 at 08:32
1

Don't `SET NAMES utf8`, use `mysql_set_charset` or `charset=utf8` in the DSN, depending on what API you use. See http://stackoverflow.com/a/12720360/476 – deceze Dec 11 '13 at 08:45
@Mahan thank you for your suggestion. I updated my question with the code, as per your request. Any ideas on how to make it work inside my code? – Johnathan Smith Dec 11 '13 at 09:07

score 1 · Answer 2 · answered Dec 11 '13 at 08:11

1

Couple of things to check are the connection and the column. Columns can have their own char set

http://www.php.net/manual/en/mysqli.set-charset.php

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

answered Dec 11 '13 at 08:11

exussum

18,275
8
32
65

score 1 · Answer 3 · answered Dec 11 '13 at 08:48

1.Use show create table X to see the encoding of the table.

something like:

mysql> show create table user;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `password` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2.If charset is not utf8, change it such.

mysql> alter table y character set 'utf8';

tried ALTER TABLE wp_store_wpress_category CHARACTER SET utf8, COLLATE utf8_unicode_ci; with no luck — Johnathan Smith, Dec 11 '13 at 09:01
please, post create table output. and if you insert stuff into sql from terminal, can you read it normally or is it still "???". if you can see normally, then it's the web layer that is somewhere damaging the input. check the content type in your html page - is it matching, e.g. is it uf8? — jancha, Dec 11 '13 at 09:12
If i edit the field manually from phpmyadmin, i get a warning: Warning: #1366 Incorrect string value: '\xCE\xB4\xCF\x83\xCE\xB4...' for column 'name' at row 1 - but everything else is working fine in Greek characters on my website. — Johnathan Smith, Dec 11 '13 at 09:16

MySQL encoding parsed wrong

3 Answers3