Android with php: Saving utf-8 string to MySQL

Question

I know I am asking a question that has been asked many times before, but I feel I need clarification on my problem.

I have an Android app that sends a JSON encoded string to a PHP script. The following code then saves the data as a complete JSON string. (There are also other functions to properly save the data to multiple fields, not just as one JSON string)

$json_complete = $_POST['questionnaire'];
$q = json_decode($json_complete, true);

//save complete json string to database
$query_upload = "INSERT INTO questions_json (q_id, json) VALUES('".mysql_real_escape_string($q[qID])."', '$json_complete') ON DUPLICATE KEY UPDATE json = VALUES(json)";
$result = mysql_query($query_upload) or errorReport("Error in query: $query_upload. ".mysql_error());
if (!$result)
    errorReport($result);

The problem is that some of the text included in the JSON string is taken from strings.xml in Android that is encoded in utf-8. As an example in the strings.xml file <item>Can\'t get there</item> is saved as "Canu0027t get there" in MySQL. Also "Wholesale & Retail" is saved as "Wholesale u0026 Retail".

I also had json_decode() in php fail on me because of non utf-8 encoded characters in the JSON string. Got the following error: Malformed UTF-8 characters, possibly incorrectly encoded.

How can I handle all these scenarios? Any help please.

Why json_decode() fails in php ??? Are you getting a wrong string or you have an error message? — Milos Cuculovic
– Milos Cuculovic, Commented Oct 8, 2012 at 14:42
And even if you save Canu0027t get there, when you will display this for example with var a = "Canu0027t get there"; and alert(a); you will see: Canu't get there — Milos Cuculovic
– Milos Cuculovic, Commented Oct 8, 2012 at 14:45

DragonWork · Accepted Answer · 2013-01-26 23:53:00Z

2

You also have to mysql_real_escape_string() your $json_complete in your query, otherwise you'd loose the original /uXXXX encodings.

$json_complete = $_POST['questionnaire'];
$q = json_decode($json_complete, true);

//save complete json string to database
$query_upload = "INSERT INTO questions_json (q_id, json) VALUES('".mysql_real_escape_string($q[qID])."', '".mysql_real_escape_string($json_complete)."') ON DUPLICATE KEY UPDATE json = VALUES(json)";
$result = mysql_query($query_upload) or errorReport("Error in query: $query_upload. ".mysql_error());
if (!$result)
    errorReport($result);

edited Jan 26, 2013 at 23:53

answered Oct 9, 2012 at 13:14

DragonWork

2,4351 gold badge19 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

brunodea · Accepted Answer · 2012-10-08 14:48:53Z

1

Did you set the encoding for the post?

    List<NameValuePair> params = ...;
    HttpPost httppost = new HttpPost(hostname);
    httppost.setEntity(new UrlEncodedFormEntity(params, "UTF-8"));

answered Oct 8, 2012 at 14:48

brunodea

2201 silver badge11 bronze badges

1 Comment

Rynardt Over a year ago

I tried that but it made no difference. Still get "it's" saved as "itu0027s"

Martin Müller · Accepted Answer · 2012-10-08 14:52:13Z

0

In general, all strings nowadays should be UTF-8 because it allows for easy internationalization. You should set up your MySQL db such that string fields are stored as UTF-8 (utf8_general_ci) and you won't have problems with the conversion.

If you insist on another encoding in your db you may want to check out mb_check_encoding($string,'UTF-8')and mb_convert_encoding($string, 'ISO-8859-1', 'UTF-8').

answered Oct 8, 2012 at 14:52

Martin Müller

2,53522 silver badges34 bronze badges

2 Comments

Rynardt Over a year ago

Change my MySQL to utf8_general_ci, but made no difference. Also tried mb_check_encoding and mb_convert_encoding, but also made no difference. Still get "it's" saved as "itu0027s"

Martin Müller Over a year ago

that's weird. so the decoded json contains additional characters whcih are there because php can't decode utf-8 json correctly? or maybe you need to change the way those strings are read from the xml and embedded into the json.

Collectives™ on Stack Overflow

Android with php: Saving utf-8 string to MySQL

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related