php - Cannot properly insert greek characters in mysql database

Question

Our mysql database shows ? ????? ?3???…?o??? 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;
        }

Answer 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.