Cannot properly insert greek characters in mysql d

问题:

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;
        }

回答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.

回答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;'));

回答3:

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');

回答4:

Use database varchar field as utf8_general_ci.

回答5:

If you are using a php script to insert data into data base then first use utf8_encode function on data to be inserted and then use utf8_decode on the same data and then perform the database insertion operation.