Wrong encoding in communication between php/mysql

Question

Hello I bought this app code that uses data from sql database. There is no problem in english, but I need to use czech special signs like č,ř,š,ě,í,é.. I can alter the data using these czech signs in CMS without problem, but in sql database they are wrong (like é instead of č), when I alter the data in database its wrong in CMS (in different way č is ?) and than its of course wrong in android app also or it crashes because it does not recognize charset. I try to solve this for more than month, I have read everything here and on google, but still dont know where the problem is. I have even recreated whole database by hand and set up by hand for each table utf8, or utf8mb4. I have tried to add to function

mysql_query("SET NAMES 'utf8mb4'");

or

mysql_query("SET NAMES 'utf8'");

but it has zero effect like I did not do anything

I have of course changed all the php files to utf8 or utf8mb4, but the errors are still the same. I did the same with database.

If I run

SHOW VARIABLES LIKE '%character%'

I get these results:

character_set_client    utf8mb4
character_set_connection    utf8mb4
character_set_database  utf8
character_set_filesystem    binary
character_set_results   utf8mb4
character_set_server    utf8
character_set_system    utf8
character_sets_dir  /usr/share/percona-server/charsets/

PHP Version 5.4.45

Thanks for any ideas or any help.

Update: SHOW CREATE TABLE returned:
    category    CREATE TABLE `category` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `institution_id` int(11) NOT NULL,
 `programme_id` varchar(255) COLLATE utf8_bin NOT NULL,
 `name` varchar(255) COLLATE utf8_bin NOT NULL,
 `description` text COLLATE utf8_bin NOT NULL,
 `question_time` varchar(255) COLLATE utf8_bin NOT NULL,
 `limits` varchar(255) COLLATE utf8_bin NOT NULL,
 `correct_ans_score` int(11) NOT NULL,
 `wrong_ans_score` int(11) NOT NULL,
 `expire_date` varchar(255) COLLATE utf8_bin NOT NULL,
 `no_of_days` varchar(255) COLLATE utf8_bin NOT NULL,
 `status` int(1) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8 COLLATE=utf8_bin


category    CREATE TABLE `category` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `institution_id` int(11) NOT NULL,
 `programme_id` varchar(255) COLLATE utf8_bin NOT NULL,
 `name` varchar(255) COLLATE utf8_bin NOT NULL,
 `description` text COLLATE utf8_bin NOT NULL,
 `question_time` varchar(255) COLLATE utf8_bin NOT NULL,
 `limits` varchar(255) COLLATE utf8_bin NOT NULL,
 `correct_ans_score` int(11) NOT NULL,
 `wrong_ans_score` int(11) NOT NULL,
 `expire_date` varchar(255) COLLATE utf8_bin NOT NULL,
 `no_of_days` varchar(255) COLLATE utf8_bin NOT NULL,
 `status` int(1) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Possible duplicate of [UTF-8 all the way through](http://stackoverflow.com/questions/279170/utf-8-all-the-way-through) — Alastair McCormack, May 25 '16 at 18:50

user3281831 · Answer 1 · 2016-05-26T08:55:42.570

0

Thanks Mees Kluivers, there was some info indeed that helped me. I added these two lines to beginning of every PHP file and now it shows correct characters both in CMS and in mysql database, for some reason android app still shows ? instead of č, but its still huge step forward after month dealing with it.

mysql_query("set names 'utf8'");
mysql_query("set charset 'utf8'");

edited May 26 '16 at 08:55

answered May 26 '16 at 08:44

user3281831

839
5
14
24

score 0 · Answer 2 · answered May 31 '16 at 21:58

0

After you have converted to mysqli_* interface, use the set_charset('utf8') function.

Also, be sure that your column/table is declared CHARACTER SET utf8 (or utf8mb4). Please provide SHOW CREATE TABLE.

I would be happy to further diagnose your problem, but I need more info. Is č turning into ? or Ä or being truncated or perhaps something else. I see no way for it to turn into é, so I need more clues.

answered May 31 '16 at 21:58

Rick James

135,179
13
127
222

As i wrote in the answer, PHP/mysql communication seems working ok after i added those two lines of code in every php file. Now problem moved to the android app which still shows ? instead of č. however, if I add č directly in the code of the app, it shows č, it shows ? only when the data comes from mysql database. So if Im right, the whole problem is the database. – user3281831 Jun 01 '16 at 19:43
`?` usually comes from table definition being wrong. Let's see `SHOW CREATE TABLE`. – Rick James Jun 01 '16 at 20:52
Ok I have updated the question with result of SHOW CREATE TABLE from probably the most important table in the database. – user3281831 Jun 01 '16 at 21:36
What language (PHP / Java / etc) does Android talk to the database with? – Rick James Jun 01 '16 at 23:27
The app project is in appcelerator studio (titanium studio), so it is java. The problem is I did not build the app, only bought it and there are ttitanium alloy things which I do not understand and cannot find in documentation how it connects to remote database, only found stuff about local sqlite database, not how it connects to remote sql database.. – user3281831 Jun 02 '16 at 07:28
Ok I got it wrong, I found another PHP files in another folder which are probably used to communicate with the app, but after I changed it, č is not ?, but it is now %C4%8D, which looks very similar to UTF8 hex code for letter č, c4 8d. – user3281831 Jun 02 '16 at 10:45
`%C4%8D` would be the "url encoding" -- useful for safely passing 8-bit stuff in urls. `C48D` will, I think, be correctly converted back into `č` by the receiving web page. – Rick James Jun 02 '16 at 15:08
Any idea why does it do that? Im really confused why would it url encode normal text in database. – user3281831 Jun 04 '16 at 09:50
"It"? MySQL never adds the `%`. PHP adds them if you call `url_encode()`. Is there any other product involved? – Rick James Jun 04 '16 at 16:26
I found this in function.php function array_encode($arr) { $en_arr=array(); if(count($arr)>0) { foreach($arr as $key=>$val) { if(is_array($val)) $en_arr[$key]=array_encode($val); else $en_arr[$key]=urlencode($val); } } return $en_arr; } function array_decode($arr) { if(count($arr)>0) { foreach ($arr as $key=>$val) { if(is_array($val)) $de_arr[$key]=array_decode($val); else $de_arr[$key]=urldecode($val); } } return $de_arr; }`code` – user3281831 Jun 05 '16 at 08:48
Sry for formatting, for some reason I cannot add it as code. – user3281831 Jun 05 '16 at 09:09
But why ever encode or decode? That is, _when_ do you call those functions? – Rick James Jun 05 '16 at 22:49
I do not know, i did not write the code, only bought it. i tried to find it. Looks like $arr is everywhere in the code, 690 matches exactly. – user3281831 Jun 07 '16 at 10:16
I have kinda solved it by replacing urlencode with urldecode. So far app seems working ok. – user3281831 Jun 09 '16 at 09:28

Wrong encoding in communication between php/mysql

2 Answers2