otak info official Logo   
Personal Blog

Hariyanto Lim

정길상 / 鄭吉祥
Home   Last Posts  
Please login to see more topics and discussion categories.
goto main category list >> Software programming >> PHP

Title : How to store unicode string into mysql and load it to generate json
Total Reply : 0
Total View : 5029

Total thread: 131
Total reply: 50

Post #174
How to store unicode string into mysql and load it to generate json
From time to time, I often need to store unicode string into MySQL then read/query it to generate json string, i have done this task a few time but i always forget how to do it, so this post is simply just to remind me of how to do it properly again in the future.

INSERT to MySQL, use mysql_real_escape_string() directly without any other escape technique
$qry = "insert into alkitab (ayatId, ayatText)";
$qry .= " VALUES($counter, "'".mysql_real_escape_string($ayatText)."'";
$res = mysql_query($qry);
$qry = "select * from alkitab";
$res = mysql_query($qry);
if(!$res) {
    echo 'Query failed: read failed 2334';
    echo '<br /><br />'.mysql_error().'<br /><br />'.$qry;

$nl = "\r\n";

echo "{";
echo $nl.", \"bible_contents\" : [";

$counter = 0;
  while($arr = mysql_fetch_array($res)) {

    echo $nl;

    if($counter > 1) {
      echo ", "; // separator

    // NOTE: because the verse string is stored AS-IS using mysql_real_escape_string()
    // so we need to decode it !!
    $rawStringFromDatabase = $arr['ayatText'];
    $decoded = escapeJsonString($rawStringFromDatabase);

    echo "\"".$decoded."\"";

echo $nl."]";
echo $nl."}";


   * @param $value
   * @return mixed
  function escapeJsonString($value) { # list from www.json.org: (\b backspace, \f formfeed)
      $escapers = array("\\", "/", "\"", "\n", "\r", "\t", "\x08", "\x0c");
      $replacements = array("\\\\", "\\/", "\\\"", "\\n", "\\r", "\\t", "\\f", "\\b");
      $result = str_replace($escapers, $replacements, $value);
      return $result;
Falling in love with the world

Write : 2016-04-15 15:01:47
Last edit : 2016-04-15 15:03:46

If you want to create a new reply then please login first.

Since 19 January 2007
Page hit : 841,409

Code update 24th June 2013
Brain is a very capable to solve big problems
but requires constant reminders about how to.
peace bird