Cannot properly insert greek characters in mysql database

Question

Our mysql database shows Î Î¿Î»Ï Î³Î»Ï…ÎºÏŒÏ in place of greek characters while sending data from an emulator to a mysql database. Other characters are left ok.

screenshot from phpMyAdmin:

UPDATE:

After using

@Félix Gagnon-Grenier answer in my code it gives me this:

Sql for table creation

CREATE TABLE `cart` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `product_name` varchar(255) NOT NULL,
 `product_price` double(3,2) NOT NULL,
 `product_image` varchar(255) NOT NULL,
 `quantity` int(11) NOT NULL,
 `preferation1` varchar(50) NOT NULL,
 `preferation2` varchar(50) NOT NULL,
 `preferation3` varchar(50) NOT NULL,
 `preferation4` varchar(50) NOT NULL,
 `magazi_id` int(11) NOT NULL,
 `servitoros_id` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

php

<?php
    error_reporting(E_ALL ^ E_NOTICE);
    ini_set("default_charset", "UTF-8");
    header('Content-type: text/html; charset=UTF-8');
    mb_internal_encoding('UTF-8');
    mb_http_input("utf-8");
    try {
        $handler = new PDO('mysql:host=localhost;dbname=database', 'username', 'password');
        $handler->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES 'utf8' COLLATE 'utf8_general_ci' ");
        $handler->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch (Exception $e) {
        echo $e->getMessage();
        die();
    }

    $productName = $_POST['productName'];
    $productPrice=$_POST['productPrice'];
    $productImage = $_POST['productImage'];
    $quantity = $_POST['quantity'];
    $sugar = $_POST['sugar'];
    $milk = $_POST['milk'];
    $flavor=$_POST['flavor'];
    $comment = $_POST['comment'];
    $magazi = $_POST['magazi_id'];
    $servitoros = $_POST['servitoros_id'];

    $handler->query("INSERT INTO cart(id, product_name, product_price, product_image, quantity, preferation1, preferation2, preferation3, preferation4, magazi_id, servitoros_id) VALUES('', '$productName','$productPrice','$productImage', '$quantity', '$sugar', '$milk', '$flavor', '$comment', '$magazi', '$servitoros')");
    die();
?>

Java

protected Void doInBackground(String... params) {
            nameValuePairs = new ArrayList<>();
            nameValuePairs.add(new BasicNameValuePair("productName", productName));
            nameValuePairs.add(new BasicNameValuePair("productPrice", String.valueOf(price)));
            nameValuePairs.add(new BasicNameValuePair("productImage", image));
            nameValuePairs.add(new BasicNameValuePair("quantity", String.valueOf(quantityNumberFinal)));
            nameValuePairs.add(new BasicNameValuePair("sugar", sugarPreference));
            nameValuePairs.add(new BasicNameValuePair("milk", milkPreference));
            nameValuePairs.add(new BasicNameValuePair("flavor", flavorPreference));
            nameValuePairs.add(new BasicNameValuePair("comment", comment));
            nameValuePairs.add(new BasicNameValuePair("magazi_id", String.valueOf(2)));
            nameValuePairs.add(new BasicNameValuePair("servitoros_id", String.valueOf(13)));
            try
            {
                HttpParams httpParams = new BasicHttpParams();
                HttpProtocolParams.setContentCharset(httpParams, "UTF-8");
                httpClient = new DefaultHttpClient(httpParams);
                httpPost = new HttpPost(params[0]);
                httpPost.setEntity(new UrlEncodedFormEntity(nameValuePairs, "UTF-8"));
                response = httpClient.execute(httpPost);
                httpEntity = response.getEntity();
                is = httpEntity.getContent();
            }
            catch(Exception e)
            {
                Log.e("Fail 1", e.toString());
            }
            return null;
        }

Solution 1

Your problem is related to your charset-encoding. It's important that your entire code has the same charset to avoid issues where characters displays incorrectly.

There are quite a few settings that needs to be properly defined and I'd strongly recommend UTF-8, as this has most letters you would need (Scandinavian, Greek, Arabic, Russian, etc.).

Here's a little list of things that has to be set to a specific charset.

Headers

• Setting the charset in both HTML and PHP headers to UTF-8

  • PHP:

    header('Content-Type: text/html; charset=utf-8');
    

    (PHP headers has to be placed before any output (echo, whitespace, HTML)!)

  • HTML:

    <meta charset=utf-8" />
    

    (HTML-headers are placed within the <head> / </head> tag)

Connection

• You also need to specify the charset in the connection itself. For your PDO example, it's done like this

  $handler = new PDO('mysql:host=localhost;dbname=database;charset=utf8', 'username', 'password', array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET CHARACTER SET UTF8"));
  

  Note the charset=utf8-attribute. Other MySQL-APIs have different ways of doing this should you use something else in the future.

Database

• Your database and its tables has to be set to UTF-8. Note that charset is not the same as collation. I see you already set your collation to UTF-8, so that's good, but do the same for the entire database and all tables.

  You can do that by running the queries below once for each database and tables (for example in phpMyAdmin)

  ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci; 
  ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
  

  Note that any data already stored in the database will not automatically have their broken charset fixed. So its important you do this before inserting data, or that you re-insert it after setting the charset.

php.ini specification

• In your php.ini file, you should specify the default charset for your platform, like this

  default_charset = "utf-8";
  

File-encoding

• It's also important that the .php file itself is UTF-8 encoded. If you're using Notepad++ to write your code, this can be done in the "Format" drop-down on the taskbar.

Emojis

• In MySQL (both in the table, database and connection-object), you will need to specify the utf8mb4 charset, as opposed to regular utf8, if you wish to work with emojis.

I don't know much about Java, but if you can set attributes to UTF-8 there as well, do it. In essence, everything that can be set to a specific charset should be set to the same.

Should you follow all of the pointers above, chances are your problem will be solved. If not, you can take a look at this StackOverflow post: UTF-8 all the way through.

Solution 2

An excellent introduction about using UTF8 with PHP and Mysql can be found in this blog. The main points are:

1. Set the database tables to UTF8
2. In your php.ini set default_charset = "utf-8";
3. After the connection is established you may have to run the following command/query: set names UTF-8;

I guess you are missing point 3.

In your PHP script, you should do something like this (untested):

$handler = new PDO('mysql:host=localhost;dbname=database', 
  'username', 
  'password',
  array(PDO::MYSQL_ATTR_INIT_COMMAND => 
    'SET NAMES utf8;SET CHARACTER SET UTF8;'));

Solution 3

Use database varchar field as utf8_general_ci.

Solution 4

Your whole php/mysql setup seems to be encoded with utf-8, but the java code isn't, starting from there:

httpClient = new DefaultHttpClient();
httpPost = new HttpPost(params[0]);
httpPost.setEntity(new UrlEncodedFormEntity(nameValuePairs));

First, the DefaultHttpClient class seems to be deprecated. However, docs say that

This class sets up the following parameters if not explicitly set:

• ContentCharset: HTTP.DEFAULT_CONTENT_CHARSET

The http default charset is ISO-8859-1 according to the java constant field values

The HttpPost itself does not appear to mingle with character sets.

But the UrlEncodedFormEntity does. Also from the docs:

UrlEncodedFormEntity(Iterable parameters)
Constructs a new UrlEncodedFormEntity with the list of parameters with the default encoding of HTTP.DEFAULT_CONTENT_CHARSET

Since ISO-8859-1 can't keep greek characters, passing them through those instances will modify them, hence breaking the characters in your php code.

All of this code seems to be deprecated, but change java to output utf-8 encoded characters:

HttpParams httpParams = new BasicHttpParams();
HttpProtocolParams.setContentCharset(httpParams ,"UTF-8");
httpClient = new DefaultHttpClient(httpParams);
httpPost = new HttpPost(params[0]);
httpPost.setEntity(new UrlEncodedFormEntity(nameValuePairs,"UTF-8"));

and remove redundancy in your php:

ini_set("default_charset", "UTF-8");
header('Content-type: text/html; charset=UTF-8');
mb_internal_encoding('UTF-8');
mb_http_input("utf-8");

Header will overwrite the ini_set, so you can remove it, and the http input is already in utf8 (since we just transformed it in java), so you can remove it too:

header('Content-type: text/html; charset=UTF-8');
mb_internal_encoding('UTF-8');

View more solutions

Comments

[luksch]

Maybe it is just the logging console that is set to the wrong charset? Did you look in the database via another way (console or phpMyAdmin or something)

[Admin]

i have done everything that you suggest but still the same

[luksch]

And? Do the values contain Greek characters in the DB? So is the problem related to inserting or retrieving, or both?

[Admin]

preferation1 and preferation2 are the greek data i want to insert

[Admin]

i wonder does it have to do anything if it is InnoDB or MyISAM?

[Qirel]

I see you updated your code after my post, but you did not include charset=utf8 in your connection-object. Is that just left out of the code you updated, or do you actually have it in your actual code? @KostasDrakonakis

[Admin]

i left it out but whether is in or out it gives me the same result

[Qirel]

Never the less, it should be in there. It's important that the connection has the charset set to UTF-8. However, if you echo $sugar; before you insert it, is it in the proper format?

[Admin]

first of all DefaultHttpClient works fine since i have imported legacy library. now ISO-8859-1 does not support greek characters

[Rick James]

Dont use any encode/decode functions. When properly configured conversions are automatic.