How to store this string as valid json in mysql (utf8_encode and htmlentities not helping)

Question

Here is the string I am trying to store

<div id="site-summary">
    <p>
        <strong>Welcome to Whiteley Helyar.</strong> We are independent, friendly and professional, always seeking to achieve the best for our clients. Whiteley Helyar is one of Bath’s leading residential estate agencies and deal only in sales – nothing else.  Our many satisfied customers value our efficiency, honesty and expert understanding of property in Bath and the surrounding villages. Not only will you find us knowledgeable and helpful, we are also eager and progressive in our approach. If you are house-hunting in one of the UK’s most beautiful and vibrant cities, our experienced team of partners and negotiators can help you find the perfect property.
    </p>
</div>

There is some unusual characters in here which is causing json_encode not to properly encode the string to json when it's in an array.

Here is my code where $about_us is the above string

$about_us = utf8_encode($about_us);
$about_us = htmlentities($about_us, ENT_QUOTES | ENT_IGNORE, "UTF-8");
$about_us = mysql_real_escape_string($about_us);

$link_array = array();
$link_array['stuff'] = 'other string';
$link_array['about_us'] =  $about_us;
$json= json_encode($link_array);

I then store $json in a mysql text field. However. I can see from examining the $json string it's not valid json and fails on http://jsonlint.com/. And when I do a json_decode stuff works correctly but about us is empty.

This is what is in the database for the about_us property:

&lt;div id=&quot;site-summary&quot;&gt;\r\n             &lt;p&gt;&lt;strong&gt;Welcome to Whiteley Helyar.&lt;/strong&gt; We are independent, friendly and professional, always seeking to achieve the best for our clients. Whiteley Helyar is one of Bath&acirc;u0080u0099s leading residential estate agencies and deal only in sales &acirc;u0080u0093 nothing else.  Our many satisfied customers value our efficiency, honesty and expert understanding of property in Bath and the surrounding villages. Not only will you find us knowledgeable and helpful, we are also eager and progressive in our approach. If you are house-hunting in one of the UK&acirc;u0080u0099s most beautiful and vibrant cities, our experienced team of partners and negotiators can help you find the perfect property.&lt;/p&gt;\r\n           &lt;/div&gt;

Any ideas please

Edit: This is different from the linked question since utf8_encode is not helping here (and that is the accepted answer for that question).

If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) instead, and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Jun 19 '15 at 15:27
possible duplicate of [Putting HTML in JSON](http://stackoverflow.com/questions/9492916/putting-html-in-json) — Jay Blanchard, Jun 19 '15 at 15:28
This is different from the linked question since utf8_encode is not helping here (and that is the accepted answer for that question). — Hard worker, Jun 19 '15 at 15:46
The data is an mime encoded/escaped string, looks like the output of mysql_real_escape_string($about_us); without applying the json functions on it. Where is the final code storing the data? — Norbert, Jun 19 '15 at 15:50
var_dump `$about_us` and you'll see that the string is already *garbled*. `json_encode` is not the culprit here. The problem is `htmlentities`, which is not needed in this context. — Yoshi, Jun 19 '15 at 15:54
You handwaved "I then store $json in a mysql text field." Show us that code, too. — Andy Lester, Jun 19 '15 at 17:28

JasonJensenDev · Answer 1 · 2015-06-19T16:26:49.873

First of all, if you're still using mysql_real_escape_string, you're probably not using PDO to interact with the database. You really should be using PDO at this point.

If you use PDO, it solves a lot of your problems and you could do something simple like this:

$db = new PDO('mysql:host=CHANGE_THIS_TO_YOUR_HOST_NAME;
    dbname=CHANGE_THIS_TO_YOUR_DATABASE',
    'CHANGE_THIS_TO_YOUR_USERNAME',
    'CHANGE_THIS_TO_YOUR_PASSWORD');


$about_us = '<div id="site-summary">
            <p><strong>Welcome to Whiteley Helyar.</strong> We are independent, friendly and professional, always seeking to achieve the best for our clients. Whiteley Helyar is one of Bath’s leading residential estate agencies and deal only in sales – nothing else.  Our many satisfied customers value our efficiency, honesty and expert understanding of property in Bath and the surrounding villages. Not only will you find us knowledgeable and helpful, we are also eager and progressive in our approach. If you are house-hunting in one of the UK’s most beautiful and vibrant cities, our experienced team of partners and negotiators can help you find the perfect property.</p>
            </div>';

$link_array = array();
$link_array['stuff'] = 'other string';
$link_array['about_us'] =  $about_us;
$json= json_encode($link_array);

$query = $db->prepare("INSERT INTO settings 
    (json) VALUES (:json)
    ");
$query->bindValue(':json', $json, PDO::PARAM_STR);
$query->execute();

Then, if you look in the database and copy and paste what's in the json field into jsonlint, it will validate just fine. Plus, this way, the html is actually readable in the database (rather than having to store everything with < and " and such).

How to store this string as valid json in mysql (utf8_encode and htmlentities not helping)

1 Answers1