Link to home
Start Free TrialLog in
Avatar of lvmllc
lvmllcFlag for United States of America

asked on

Working with JSON from MSQL that has an apostrophe

I am retrieving values from MySQL with PHP for use in a mapping application all works unless my data has an apostrophe in it.

PHP
if ($result = $mysqli->query($query)) {
		while ($row = $result->fetch_assoc()) {
			$json[] = ($row);
		}
}
echo "var locations = '".json_encode($json)."'";

Open in new window



the JavaScript parsing so I can use this data in a Leaflet Map is as follows

var parsedLocations = JSON.parse(locations);
i = 0;
while (i < parsedLocations.length) {
    var marker = L.marker([parsedLocations[i].latitude, parsedLocations[i].longitude], {
        title: parsedLocations[i].title,
        opacity: 0.9,
        riseOnHover: true
    }).addTo(map);

Open in new window


So all works unless there is an apostrophe or other special character in the JSON output
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

When you retrieve data from a data base query, you get a results set that is in clear text.  The best thing to do with it is aggregate the results into an array of rows, then use PHP json_encode() to prepare a JSON string.

JSON data must be valid UTF-8.  You may want to check your character set encoding.  It has to be right end-to-end.  The apostrophe has no special significance in JSON-encoded data.
http://json.org/
ASKER CERTIFIED SOLUTION
Avatar of Brian Tao
Brian Tao
Flag of Taiwan, Province of China image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lvmllc

ASKER

This is a bit strange.  My local server is PHP PHP Version 5.6.3 and it prints it out OK, but  with my server that is 5.4.14

All I get from the server is  var locations = ''
Can check the line of code and make sure it's a | (pipe) between the 2 options and not a , (comma)?
???
Not sure I can offer a sure fix, But, I have done much code work using PHP strings to javascript and javascript variables. When you write from PHP to JS strings, you must use the JS  single or double quote, If you use a single JS Quote as example -
echo "var v1 = '".$jsvar."';";

so the JS string from PHP, CAN NOT have any UN-escaped single quotes in it, as your PHP variable string  $jsvar  MUST have all  '  changed to  \'
$jsvar = str_replace("'", "\'", $jsvar);
echo "var v1 = '".$jsvar."';";


so you might try -
$js = json_encode($json);
$js = str_replace("'", "\'", $js);
echo "var locations = '".$js."';";

Open in new window

Here is a simple example showing a JSON object that contains an apostrophe.

Client side:
<?php // demo/temp_lvmllc_client.php
/**
 * See http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28630465.html
 */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);

// CREATE OUR WEB PAGE IN HTML5 FORMAT
$htm = <<<HTML5
<!DOCTYPE html>
<html dir="ltr" lang="en-US">
<head>
<meta charset="utf-8" />
<meta name="robots" content="noindex, nofollow" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">

<style type="text/css">
/* STYLE SHEET HERE */
</style>

<script type="text/javascript" src="http://code.jquery.com/jquery-latest.min.js"></script>
<script>
$(document).ready(function(){
    $("#signal").click(function(){
        $.get("temp_lvmllc_server.php", function(response){
            $("#output p#target").html(response);
        });
    });
});
</script>

<title>HTML5 Page With jQuery and JSON in UTF-8 Encoding</title>
</head>
<body>

<noscript>Your browsing experience will be much better with JavaScript enabled!</noscript>

<div   id="signal">Click Here to Retrieve JSON from the Server!</div>
<div   id="output">
   <p  id="target">This element gets the AJAX (JSON) response</p>
</div>


</body>
</html>
HTML5;

// RENDER THE WEB PAGE
echo $htm;

Open in new window

Server side:
<?php // demo/temp_lvmllc_server.php
/**
 * See http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28630465.html
 * Ref http://php.net/manual/en/function.json-encode.php
 * Ref http://json.org/
 *
 * Return a JSON object that contains an apostrophe
 */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);

$text = "Katie O'Brien";
$json = json_encode($text);
if ( json_last_error() ) echo json_last_error();
echo $json;

Open in new window

(Edited to remove a redundant element in the JS)
Here's a slightly more advanced example.  This shows how to retrieve the JSON string from the server, turn it into a  JavaScript object, and use the properties of the object to inject information into the DOM.

Client side:
<?php // demo/temp_lvmllc_client.php
/**
 * See http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28630465.html
 */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);

// CREATE OUR WEB PAGE IN HTML5 FORMAT
$htm = <<<HTML5
<!DOCTYPE html>
<html dir="ltr" lang="en-US">
<head>
<meta charset="utf-8" />
<meta name="robots" content="noindex, nofollow" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">

<style type="text/css">
/* STYLE SHEET HERE */
</style>

<script type="text/javascript" src="http://code.jquery.com/jquery-latest.min.js"></script>
<script>
$(document).ready(function(){
    $("#signal").click(function(){
        $.get("temp_lvmllc_server.php", function(response){
            var obj = $.parseJSON(response);
            var txt = obj.a + ' and ' + obj.b;
            $("#output p#target").html(txt);
        });
    });
});
</script>

<title>HTML5 Page With jQuery and JSON in UTF-8 Encoding</title>
</head>
<body>

<noscript>Your browsing experience will be much better with JavaScript enabled!</noscript>

<div   id="signal">Click Here to Retrieve JSON from the Server!</div>
<div   id="output">
   <p  id="target">This element gets the AJAX (JSON) response</p>
</div>


</body>
</html>
HTML5;

// RENDER THE WEB PAGE
echo $htm;

Open in new window

Server side:
<?php // demo/temp_lvmllc_server.php
/**
 * See http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28630465.html
 * Ref http://php.net/manual/en/function.json-encode.php
 * Ref http://json.org/
 *
 * Return a JSON object that contains an apostrophe
 */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);

$data = [ 'a' => "Katie O'Brien", 'b' => 'Jack Williams' ] ;
$json = json_encode($data);
if ( json_last_error() ) echo json_last_error();
echo $json;

Open in new window

not sure if any are still lookin at this, but I got a couple of alerts about ray's comments, so I looked at some of my old code, and the best way to do this may be to actually WRITE the PHP array to a REAL javascript { } object notation , , without the clunky  json_encode( ) and then as a JS string mess.
<?php 
$aryJS = array('on'=>true,'name'=>'Joker\'s Krazy', 'address'=>'123 Ain\'t St.', 'price'=>3.50, 'quote' => 'Tang said "Take the Money!"');
$JS = json_encode($aryJS);
// IMPORTANT ! You must escape BOTH single quotes AND the escape charaters for double quotes
$JSvar = str_replace(array('\\',"'"), array('\\\\',"\'"), $JS);
?><!DOCTYPE html><html><head><title>PHP to JS</title>
<style>body{background:#fff9e8;height:100%;}</style>
<script>/* <![CDATA[ */
<?php echo "var user1 = '".$JSvar."';"; ?>

var user2 = {<?php $out = '';
   // BELOW I convert the $aryJS array to a variable of an OBJECT
foreach ($aryJS as $key => $val) {$out .= $key.':';
if(is_string($val)) { $val = str_replace('"', '\"', $val);
$out .= '"'.$val.'",'; } else $out .= $val.',';}
$out = substr($out, 0, -1);
echo $out; ?>};
user1 = JSON.parse(user1);
//  you do not need to parse( ) variable user2
/* ]]> */</script>
</head>
<body><h3>PHP to JS</h3>
<script>/* <![CDATA[ */
if(user2.on) document.write("user1 name "+user1.name+"<br />user2 address "+user2.address); /* ]]> */</script>
<hr>
<?php echo $JS.'<br />'.$JSvar; ?>
</body></html>

Open in new window


please look on line 5 to see me escaping the php  json_encode string.

and on line 11 to see how I use each PHP array element to write a JS Object, instead of a string, . .

This is only good for arrays that have strings and numbers (Boolean) in them, it will not work if elements are arrays or php objects, I have used this on database ROW arrays, since they do not have any arrays or objects in them.
will not work if elements are arrays or php objects,
That limitation may not exist if you're reasonably careful about the data you want to return.  A careful reading of the JSON standard and the PHP json_encode() function will show that they play very, very well together.  Database models that are returned in object format are used all the time in Laravel/Angular applications to communicate between the API and the client JavaScript.  In short, Facebook could not exist if this limitation existed!

The key here seems to be that the application should return a JSON string, and not a JavaScript instruction.  Separating the concerns (data vs programming) is always a smart approach in software development.  So my recommendation would be to put all of the JavaScript programming in the front end scripts and let PHP return just the data, not any JavaScript instructions.
Ray you could not be more wrong.
@Slick: Are you saying my example code does not work?  I tested it before I posted it.  What is going wrong in your tests?
Here's another example using the data set posted above.  First the server side of the process.  You can run this script separately in your web browser to see exactly what data it returns to the AJAX request.  Line 13 does all of the work!
http://iconoun.com/demo/temp_slick812_server.php
<?php // demo/temp_slick812_server.php
/**
 * See http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28630465.html
 * Ref http://php.net/manual/en/function.json-encode.php
 * Ref http://json.org/
 *
 * Return a JSON object that contains both quotes and an apostrophe
 */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);

$data = array( 'on' => true, 'name' => "Joker's Krazy", 'address' => "123 Ain't St.", 'price' => 3.50, 'quote' => 'Tang said "Take the Money!"' );
$json = json_encode($data);
if ( json_last_error() ) echo json_last_error();
echo $json;

Open in new window

And here is the client-side of the process.  When you click the top line, the bottom line is replaced by the data from the server.
http://iconoun.com/demo/temp_slick812_client.php
<?php // demo/temp_slick812_client.php
/**
 * See http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28630465.html
 */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);

// CREATE OUR WEB PAGE IN HTML5 FORMAT
$htm = <<<HTML5
<!DOCTYPE html>
<html dir="ltr" lang="en-US">
<head>
<meta charset="utf-8" />
<meta name="robots" content="noindex, nofollow" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">

<style type="text/css">
/* STYLE SHEET HERE */
</style>

<script type="text/javascript" src="http://code.jquery.com/jquery-latest.min.js"></script>
<script>
$(document).ready(function(){
    $("#signal").click(function(){
        $.get("temp_slick812_server.php", function(response){
            var obj = $.parseJSON(response);
            var txt = 'Fail';
            if (obj.on)
            {
                txt = obj.name + '<br>' + obj.address + '<br>' + obj.price + '<br>' + obj.quote;
            } else
            {
                alert('Failure');
            }
            $("#output p#target").html(txt);
        });
    });
});
</script>

<title>HTML5 Page With jQuery and JSON in UTF-8 Encoding</title>
</head>
<body>

<noscript>Your browsing experience will be much better with JavaScript enabled!</noscript>

<div   id="signal">Click Here to Retrieve JSON from the Server!</div>
<div   id="output">
   <p  id="target">This element gets the AJAX (JSON) response</p>
</div>


</body>
</html>
HTML5;

// RENDER THE WEB PAGE
echo $htm;

Open in new window

I think the key to understanding is threefold.  First you have to get the quotes right as you assemble the data that goes into the array or object that will be fed to JSON_Encode().  Second, you must use valid UTF-8 data.  Ascii is a subset of UTF-8 and a lot of western-language web sites can be built with nothing but Ascii, so they are automatically UTF-8 compliant, but if you have European accented characters in the data, you may need to make some modifications to the data.  Third, you have to "just let go" and allow JSON_Encode() to do its work.  The PHP function will encode the double-quotes correctly, and the JSON standard will do the rest of the work.  It's actually deceptively easy!
Ray , , you make me laugh. This is a joke right?
No, not a joke, this is really how it works.  You can install the scripts and try them on your own server.
@Brian Tao  - probably abandoned?
yes your code as-
        echo "var locations = '".json_encode($json, JSON_HEX_APOS | JSON_HEX_AMP)."'";

does offer a good way to parse the json_encode() to have all  '  as  \u0027  , , and it does work in most current versions of PHP to get the javascript "STRING" write of the HTML JSON to work with a javascript-
       JSON.parse(locations);
it works on my servers with PHP ver 5.4 .

and (like you)  I really could not understand his problem about no output as -
         var locations = ''

so I posted my attempt at solution, to escape the single  '
@Ray Paseur, , sorry, I did not mean that your code does not work, just that it has no relevance to the problem here.

I said - "Ray you could not be more wrong" about this -
     "application should return a JSON string, and not a JavaScript instruction"

Most of the javascript frameworks that do "data binding" require that server database selects be placed into javascript as JS Objects to bind to the various page elements and and javascript loops, to have the product-name and product-prices shown as changes to the page. in JS -

var prods = [
  {pName:"blue shirt", pPrice: 12.98},
  {pName:"volly ball", pPrice: 22.50},
  {pName:"cell phone", pPrice: 222.00}
  ];

here is a PHP function that will change PHP arrays or objects to a "STRING" than can echo out to javascript variable as the JSON syntax to have it work in javascript, this will do many levels of depth (arrays within arrays, and will use a null output if the PHP value is not a standard type.) It does associative arrays and numerical arrays.
I did not use this here because of it's complexity, and you do not need it for a single level ROW from a database.
function ary2obj($aryPhp) {
$o = 1;
if (is_object($aryPhp)) $aryPhp = (array) $aryPhp;
if(!is_array($aryPhp) || !count($aryPhp)) return '[]';
if(is_int(key($aryPhp))) {$out = '['; $o = 0;} else $out = '{';

foreach ($aryPhp as $key => $val) {
  if($o) $out .= $key.':';
  switch (gettype($val)) {
  case 'NULL': 
  case 'resource':
  case 'unknown type':
  $out .= 'null,'; break;
  case 'boolean': if($val) $out .= 'true,'; else $out .= 'false,'; break;
  case 'string': $val = str_replace('"', '\"', $val);
  $out .= '"'.$val.'",'; break;
  case 'object': //$val = (array) $val;
  case 'array': $val = ary2obj($val);
  $out .= $val.','; break;
  default: $out .= $val.',';
  }
  }
$out = substr($out, 0, -1);
if($o) $out .= '}'; else $out .= ']';
return $out;	
}

Open in new window

But you can not effectively do PHP values from sources, to Javascript complex objects and arrays using the PHP -
   json_encode( )
and then doing double work in JS, to parse BACK from an unneeded string, to an object, with  JSON.parse( ),
Skipping the TO STRING part saves many operations.
OK, whatever.  I see it differently.  To me it appears that the problem arises because the original script tried to return a kluge of JavaScript statements and data, instead of simply returning the JSON data.  My recommendation would be to return the JSON data in the form of a PHP string variable that contains a JSON-encode() representation of the object.  I would recommend assigning this to the JavaScript variables in the JavaScript programming.

In any case, best regards to all, and good luck with the projects! ~Ray