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

READ QUERY:

$qry = "select * from alkitab";
$res = mysql_query($qry);
if(!$res) {
    echo 'Query failed: read failed 2334';
    echo '<br /><br />'.mysql_error().'<br /><br />'.$qry;
    return;
}


$nl = "\r\n";

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

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

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

die();

/**
   * @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