Cannot insert Greek characters correctly in mysql database

Question

Cannot insert Greek characters correctly in mysql database

Our mysql database shows Î Î¿Î»Ï Î³Î»Ï…ÎºÏŒÏ instead of Greek characters when sending data from the emulator to the mysql database. The remaining characters are left in order.

screenshot from phpMyAdmin:

UPDATE:

After use

@ Félix Gagnon-Grenier's answer in my code this gives me the following:

Sql to create a table

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

+5

android php mysql http-headers character-encoding

user5364525 25 sept. '15 at 12:38

source share

5 answers

Qirel · Answer 1 · 2015-09-25T14:27:24+0000

Your problem is with the encoding encoding. It is important that all your code has the same encoding in order to avoid problems when characters are displayed incorrectly.

There are quite a few settings that need to be correctly defined, and I highly recommend UTF-8, since it has most of the letters you will need (Scandinavian, Greek, Arabic, Russian, etc.).

Here is a short list of things that should be installed in a specific encoding.

Headings

Setting encoding in HTML and PHP headers for UTF-8
- PHP:
```
 header('Content-Type: text/html; charset=utf-8'); 
```
  (PHP headers should be placed before any output (echo, spaces, HTML!))
- HTML:
```
 <meta charset=utf-8" /> 
```
  (HTML headers are placed in the <head> / </head> )

Compound

You also need to specify the encoding in the connection itself. For your PDO example, this was 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 attribute charset=utf8 . Other MySQL APIs have different ways to do this if you use something else in the future.

Database

Your database and its tables must be installed in UTF-8. Please note that the encoding does not match sorting. I see that you have already configured the mapping to UTF-8, so okay, but do the same for the entire database and all tables.
This can be done by executing the following queries for each database and tables (e.g. in phpMyAdmin)
```
 ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci; ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; 
```
Please note that any data already stored in the database will not be automatically corrected. Therefore, it is important that you do this before you insert data, or that you reinsert it after setting the encoding.

php.ini specification

In your php.ini you must specify the default encoding for your platform, for example
```
 default_charset = "utf-8"; 
```

File encoding

It is also important that the .php file itself is encoded in UTF-8 encoding. If you use Notepad ++ to write code, you can do this in the "Format" drop-down list on the taskbar.

Emojis

In MySQL (both in the table, database, and in the connection object) you need to specify charset utf8mb4 , and not regular utf8 if you want to work with emojis.

I have little knowledge of Java, but if you can also set attributes for UTF-8, do it. In fact, everything that can be set for a particular encoding should be the same.

If you follow the signs above, the likelihood that your problem will be resolved. If not, you can take a look at this StackOverflow: UTF-8 entry all the way .

luksch · Answer 2 · 2015-09-25T12:54:47+0000

A great introduction on using UTF8 with PHP and Mysql can be found in this blog . Basic moments:

Install database tables in UTF8
In your set php.ini default_charset = "utf-8";
After establishing a connection, you may need to run the following command / query: set names UTF-8;

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

Félix gagnon-grenier · Answer 3 · 2015-09-30T06:24:45+0000

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

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

First, the DefaultHttpClient class seems deprecated. However, the documents say that

This class sets the following parameters, unless explicitly specified:
ContentCharset: HTTP.DEFAULT_CONTENT_CHARSET

The default http wrapper is ISO-8859-1 according to the values of the java constant value

HttpPost itself does not mix with character sets.

But UrlEncodedFormEntity . Also from the documents:

  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 cannot contain Greek characters, passing them through these instances modifies 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 the 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");

The header will overwrite ini_set, so you can delete it, and the http input is already in utf8 (since we just changed it in java), so you can also delete it:

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

RK12 · Answer 4 · 2015-09-25T13:07:58+0000

Use the database varchar field as utf8_general_ci.

Vipin kumar · Answer 5 · 2015-10-06T15:27:27+0000

If you are using a php script to insert data into the database, first use the utf8_encode function for the data you want to insert, and then use utf8_decode on the same data, and then perform the database insert operation.

Cannot insert Greek characters correctly in mysql database

More articles: