Mysql insert issue with chracters from my native language

[thara]

I am trying to insert data from my mother language (Sinhala) into my mysql table. But MySQL displays my chracters as question marks.

This is how I test it:

CREATE DATABASE sinhala_test;
USE kindheart;

ALTER DATABASE sinhala_test
CHARACTER SET utf8 
COLLATE utf8_general_ci

CREATE TABLE IF NOT EXISTS `category` (
`category_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`name_si` VARCHAR(100) COLLATE utf8_unicode_ci NOT NULL,
`description_si` TEXT COLLATE utf8_unicode_ci NOT NULL,
`last_update` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `category` VALUES (1,'පොත් පත් සහ පාසල් උපකරණ','නවතම පොත් පත් අවශ්‍යතා ඇතුළු සියළුම පාසැල් හා අධ්‍යාපන මෙවලම්.',NOW());

This is what can I get from MySQL command line:

mysql> select * from category\G
*************************** 1. row ***************************
category_id: 1
name_si: ???? ??? ?? ????? ?????
description_si: ???? ??????? ??????? ????????. ????/????????? ????? ?????? ?????? ?? ???????? ??????.
last_update: 2018-05-20 11:11:20

Can anybody tell me how can I fix this problem?

 

NOTE: When inserting from phpMyAdmin its correctly work.

Edited May 20, 2018 by thara

[requinix]

There are multiple places that must all be set to utf8 for it to work correctly. What does

SHOW VARIABLES LIKE '%char%';

show?

[Barand]

And make sure your mysqli connection is using UTF-8

 

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

[thara]

There are multiple places that must all be set to utf8 for it to work correctly. What does

SHOW VARIABLES LIKE '%char%';

show?

 

mysql> SHOW VARIABLES LIKE '%char%';
+--------------------------+------------------------------------------------+
| Variable_name            | Value                                          |
+--------------------------+------------------------------------------------+
| character_set_client     | cp850                                          |
| character_set_connection | cp850                                          |
| character_set_database   | utf8                                           |
| character_set_filesystem | binary                                         |
| character_set_results    | cp850                                          |
| character_set_server     | latin1                                         |
| character_set_system     | utf8                                           |
| character_sets_dir       | I:\wamp64\bin\mysql\mysql5.7.9\share\charsets\ |
+--------------------------+------------------------------------------------+
8 rows in set (1.15 sec)

And make sure your mysqli connection is using UTF-8

 

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

 

This is how I connect to PHP:

$mysqli = new MySQLi(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

// Verify the connection and
// Set the character set:
if ($mysqli->connect_error) {
  //echo $mysqli->connect_error;
  //unset($mysqli);
  echo 'Database connection failed...' . 'Error: ' . $mysqli->connect_errno . ' ' . $mysqli->connect_error;
  exit();
} else { // Establish the encoding.
  $mysqli->set_charset('utf8');
}

Insert is ok with PHP. but problem is, when using command line interface.

[requinix]

See how character_set_client, character_set_connection, and character_set_results are all wrong? That's a problem.

 

https://dev.mysql.com/doc/refman/8.0/en/charset-connection.html

SET NAMES 'utf8';

[Barand]

With these settings:

mysql> show variables like '%char%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | utf8                                                    |
| character_set_connection | utf8                                                    |
| character_set_database   | utf8                                                    |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8                                                    |
| character_set_server     | utf8                                                    |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+

I get

mysql> select * from category\G;
*************************** 1. row ***************************
   category_id: 1
       name_si: ÓÂ┤ÓÀ£Ó¡ÓÀè ÓÂ┤Ó¡ÓÀè ÓÀâÓÀä ÓÂ┤ÓÀÅÓÀâÓ¢ÓÀè ÓÂïÓÂ┤ÓÂÜÓÂ╗Ó½
description_si: ÓÂ▒ÓÀÇÓ¡Ó© ÓÂ┤ÓÀ£Ó¡ÓÀè ÓÂ┤Ó¡ÓÀè ÓÂàÓÀÇÓÀüÓÀèÔÇìÓÂ║Ó¡ÓÀÅ ÓÂçÓ¡ÓÀöÓÀàÓÀö ÓÀâÓÀÆÓÂ║ÓÀàÓÀöÓ© ÓÂ┤ÓÀÅÓÀâÓÀÉÓ¢ÓÀè ÓÀäÓÀÅ ÓÂàÓÂ░ÓÀèÔÇìÓÂ║ÓÀÅÓÂ┤ÓÂ▒ Ó©ÓÀÖÓÀÇÓ¢Ó©ÓÀè.
   last_update: 2018-05-20 10:49:33

(Same result after using set names 'utf8' )

 

However, PHP output to broweser gives attached.