otak info official Logo   
OTAK.INFO
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 : 5306


Harry
Harry
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
'
'.str_replace('
', ' ', '
$qry = "insert into alkitab (ayatId, ayatText)";
$qry .= " VALUES($counter, "'".mysql_real_escape_string($ayatText)."'";
$res = mysql_query($qry);
').'
'

READ QUERY:
'
'.str_replace('
', ' ', '
$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

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



www.OTAK.INFO
Since 19 January 2007
Page hit : 885,382

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