JSON From Database – PHP and MySQL

By reading the article, JSON With PHP, we have a better understanding on how to work with JSON with PHP, now we’ll expand it by talking about “JSON from database” (using PHP and MySQL) along with problems that may occur.

Again, before continue reading this article, I recommend you to read the article: Understanding JSON with PHP, because all examples here using PHP and MySQL.

I. Creating JSON From Database

Before we continue to discuss how to create JSON from database, we need a sample data to work with. Suppose we have an article table in the blog database that contains the following data:

id title content
1 Understanding JSON With PHP <img src=”images/logo_json.png”/>
JSON is an abbreviation of “Javascript Object Notation”. JSON has a “rule” of writing that can be seen on <a href=”http://json.org”>JSON.ORG</a> JSON doesn’t treat the single quote (‘) as a special character so it’s not <em>escaped</em>, but it doesn’t mean will not cause problems, especially if it used in <span style=”color: red”>HTML element attribute</span>
2 Understanding JSON With PHP  –  Part II This article is series of the earlier <a href=”http://webdevzoom.com/json-with-php/”>earlier</a>.

In this part, we’ll discuss options that we can use when using the json_encode() function, In addition, we’ll also discuss how to handle errors that may occur when working with JSON

Note: the content column has latin1_swedish_ci collation, which also means using latin1_swedish character set

Next, with json_encode function, we create JSON data as follows:

$conn = mysqli_connect('localhost', 'root', '', 'blog');
$query = mysqli_query($conn, 'SELECT * FROM article WHERE id=1');
while ($row = mysqli_fetch_assoc($query)) {
	$data[] = $row;
}
echo '<pre>'; print_r($data); echo '</pre>';				
echo json_encode($data);

The result:

Array
(
    [0] => Array
        (
            [id] => 1
            [title] => Understanding JSON With PHP
            [content] => <img src="http://webdevzoom.com/images/logo_json.png"/> JSON is abbreviation of "Javascript Object Notation".
JSON has a "rule" of writing that can be seen on <a href="http://json.org">JSON.ORG</a>JSON doesn't treat the single quote (') as a special character so it's not <em>escaped</ em>, but it doesn't mean will not cause problems, especially if it used in <span style = 'color: red'>HTML element attribute</ span>
        )
)

[{"id":"1","title":"Understanding JSON With PHP","content":"<img src="http://webdevzoom.com/images/logo_json.png"/>JSON is abbreviation of "Javascript Object Notation". JSON has a "rule" of writing that can be seen on <a href="http://json.org">JSON.ORG</a>JSON doesn't treat the single quote (') as a special character so it's not <em>escaped</ em>, but it doesn't mean will not cause problems, especially if it used in <span style = 'color: red'>HTML element attribute</ span>"}]

Note: in the above example, since the data source is an indexed array (first index), then the generated JSON data is an array.

In addition, because data pulled from database contains only one row, we can directly fetch it without need to use the while loop as follows:

$conn = mysqli_connect('localhost', 'root', '', 'blog');
$query = mysqli_query($conn, 'SELECT * FROM article WHERE id=1');
$row = mysqli_fetch_assoc($query);
echo '<pre>'; print_r($row); echo '<pre>';			
echo json_encode($row);

The result:

Array
(
    [id] => 1
    [title] => Understanding JSON With PHP
    [content] => ...
)
{"id":"1","title":"Understanding JSON With PHP","content":"<img src="http://webdevzoom.com/images/logo_json.png"/>JSON is abbreviation of "Javascript Object Notation". JSON has a "rule" of writing that can be seen on <a href="http://json.org">JSON.ORG</a>JSON doesn't treat the single quote (') as a special character so it's not <em>escaped</ em>, but it doesn't mean will not cause problems, especially if it used in <span style = 'color: red'>HTML element attribute</span>"}

Note that in the above results, since the data source is an associative array, the resulting JSON data is an object.

II. Encoding UTF-8 Problem

In the previous article, we know that the first argument of the json_encode function, that is the text to be converted into JSON string, must be encoded in UTF-8.

When we create a JSON from a database, sometimes we’ll encounter this encoding problem because before data saved into a database, it may not be encoded in UTF-8, e.g. data from user input.

Continuing the previous example, let’s take all the articles and convert it to JSON, here is the code:

$conn = mysqli_connect('localhost', 'root', '', 'blog');
$query = mysqli_query($conn, 'SELECT * FROM article');
while ($row = mysqli_fetch_assoc($query)) {
	$data[] = $row;
}
					
echo json_encode($data);

When the script executed, we’ll find a blank page, why? because there is an error, and if an error occurred, the json_encode function doesn’t show any message.

To find out the error, we can use the json_last_error_msg() function (exists from PHP Version 5.5.0). When that function executed, we’ll get an error message as follows:

Malformed UTF-8 characters, possibly incorrectly encoded

The message tells us that the data contain non UTF-8 characters so it breaks the function, now the question is, how do we know characters caused the error?

The easiest way is, we print the original data first, then identify the results, for example:

$conn = mysqli_connect('localhost', 'root', '', 'blog');
$query = mysqli_query($conn, 'SELECT * FROM artikel');
while($row = mysqli_fetch_assoc($query)) {
	$data[] = $row;
}
					
echo '<pre>'; print_r($row); echo '</pre>';

The result:

JSON From Database - Encoding Error

JSON From Database – Encoding Error

Did you notice there is a strange character there? yes, a character with a question mark, this character is not recognized by the existing encoding (UTF-8), so it can not be displayed.

Furthermore.. if we look at the database manager like phpMyAdmin, we see that the character is “look like” a dash (-) but it’s not really a dash.

Note: I copy-paste the data from Microsoft Word, that doesn’t use UTF-8 encoding even if we don’t see any strange characters.

III. Solving Encoding UTF-8 Problem

To fix the encoding problem, we can’t simply define the encoding in the header, e.g.:

header('Content-type: text/html; charset=utf-8');

or something like this

<html>
<head>
	<meta charset="utf-8">
</head>
<body>
	...
</body>
</html>

Why?

Because when we execute the mysqli_query() function, the encoding process has been done using the default character set.

So if we look at the HEX value of query result, the question mark character is actually: 0x96 or 150th character of ASCII characters as follows:

$conn = mysqli_connect('localhost', 'root', '', 'blog');
$query = mysqli_query($conn, 'SELECT title FROM article WHERE id = 2');
$row = mysqli_fetch_assoc($query);

$arr = str_split($row['title']);
echo ord($arr[30]); // result: 150;

The 150th character is an extended character, so it doesn’t exist on the UTF-8 character set (UTF-8 only holds the basic ASCII character, 0-127), so it can not be displayed.

Furthermore, if character set that we use to display the data holds the character, such as WINDOWS-1252 or ISO-8859-1, then the character can be displayed well.

header('Content-type: text/html; charset=WINDOWS-1252');
$conn = mysqli_connect('localhost', 'root', '', 'blog');
$query = mysqli_query($conn, 'SELECT title FROM article WHERE id = 2');
$row = mysqli_fetch_assoc($query);

echo '<pre>'; print_r($row); echo '</pre>';

Result:

JSON From Database - Solving Character Set Error

JSON From Database – Solving Character Set Error

Solution?

So, what is the solution?

The easiest solution is to re-type the character (e.g. with phpMyAdmin), so we can assure that the content encoded using UTF-8.

Another solution is to define the encoding before performing the mysqli_query() function as follows:

$conn = mysqli_connect('localhost', 'root', '', 'blog');
mysqli_set_charset($conn, 'utf8');
$query = mysqli_query($conn, 'SELECT * FROM article');
while($row = mysqli_fetch_assoc($query)) {
	$data[] = $row;
}
			
$json = json_encode($data);
echo $json;

The Result:

[
    {
        "id": "1",
        "title": "Understanding JSON With PHP",
        "content": "<img src="http://webdevzoom.com/images/logo_json.png"/>JSON is abbreviation of "Javascript Object Notation". JSON has a "rule" of writing that can be seen on <a href="http://json.org">JSON.ORG</a>JSON doesn't treat the single quote (') as a special character so it's not <em>escaped</ em>, but it doesn't mean will not cause problems, especially if it used in <span style = 'color: red'>HTML element attribute</ span>
        )"
    },
    {
        "id": "2",
        "title": "Understanding JSON With PHP  \u2013  Part II",
        "content": This article is part of the earlier <a href="http://webdevzoom.com/json-with-php/">earlier</a>. In this part, we'll discuss options that we can use when using the json_encode() function, In addition, we'll also discuss how to handle errors that may occur when working with JSON
    }
]

Using this way, the json_encode() function works properly, however, the data in the database still contains non UTF-8 characters, so if possible, it’s better to repair the character by re-type it.

IV. Tips

How to make sure that data in the database always encoded using UTF-8? The only way is to make sure that the data inserted into the database are encoded using UTF-8.

For example, if we provide HTML form to capture data from users, then make sure that our HTML page uses UTF-8 encoding, e.q. by defining meta charset such as the previous example:

<html>
<head>
	<meta charset="utf-8">
</head>
<body>
	...
</body>
</html>

Download material used in this article:

Ok, that’s all about JSON From Database and how to fix the error “Malformed UTF-8 characters, possibly incorrectly encoded” when creating JSON with PHP.

Any idea?

Subscibe Now

Loves articles on webdevzoom.com? join our newsletter to get quality article right to your inbox. Nothing else, just quality stuff!!!

Our Commitment: We respect your privacy, we'll not share your credential to any party

Leave a comment

Like Us

Newsletter

Great information from webdevzoom.com right to your inbox

  1. Understanding JSONP – With Javascript and jQuery Examples

  2. JSON With Javascript and Ajax

  3. JSON From Database – PHP and MySQL

  4. JSON in HTML Document

  5. JSON With PHP – A Complete Guide

  6. Understanding JSON – A Comprehensive Guide

  7. 7 Best Free Online Image Compressor & Optimizer Tools – Compared & Tested

  8. 40+ Modern Free WordPress Themes For Blog in 2017 – Beautiful & Responsive

  9. Understanding Constant in PHP – Updated to PHP 7

  10. Understanding Variable in PHP – All PHP Version