Jump to content
thara

Mysql insert issue with chracters from my native language

Recommended Posts

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 by thara

Share this post


Link to post
Share on other sites

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

SHOW VARIABLES LIKE '%char%';
show?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

post-3105-0-94426500-1526812389_thumb.png

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.