How to do JSON pretty print with PHP

Question

How can I do JSON pretty print from MySQL? I used JSON_PRETTY_PRINT in my code but it is not printing what I am expecting. My current script is:

<?php   
//open connection to mysql db
$connection = mysqli_connect("127.0.0.1","root","Kunal@7890","testdb") or die("Error " . mysqli_error($connection));

//fetch table rows from mysql db
$sql = "select id,title,profilepic,created_at,url from news";

$result = mysqli_query($connection, $sql) or die("Error in Selecting " . mysqli_error($connection));
$rows = array();
while($r = mysqli_fetch_assoc($result)) {
    $rows[] = $r;

    print json_encode($rows, JSON_PRETTY_PRINT);

}
?>

for this script I am getting a result like:

[ { "id": "1", "title": "test", "profilepic": "0", "created_at": "2016-09-05 12:11:17", "url": "0" } ][ { "id": "1", "title": "test", "profilepic": "0", "created_at": "2016-09-05 12:11:17", "url": "0" }, { "id": "2", "title": "JCECE", "profilepic": "http:\/\/results.jharkhandeducation.net\/JCECEB\/JCECEB-Logo.jpg", "created_at": "2016-09-16 10:14:55", "url": "https:\/\/jcece.co.in\/" } ]

I want my result to be printed with the table name first followed by columns, something like this:

{
    "news": [
     {
            "id": 36,
            "name": "JCECE",
            "image": null,
            "status": " JCECE 2016 will be conducted on June 5, 2016 by JCECE Board, which is the exam conducting authority for the engineering entrance examination. JCECE 2016 will be conducted to offer admissions to undergraduate engineering courses at the participating institutes of JCECE 2016 in the state of Jharkhand. As of now, there are a total of 19 colleges (government+private) that will offer over 6000 B.E/B.Tech seats to aspiring candidates in Jharkhand. 

Application Dates:16 Apr 2016 to 16 May 2016

Admit Card Date:11 May 2015

Exam Dates:05 Jun 2016

Result Date:01 Jul 2015 to 10 Jul 2015       ",
            "profilePic": "http://results.jharkhandeducation.net/JCECEB/JCECEB-Logo.jpg",
            "timeStamp": "1461323436930",
            "url": "https://jcece.co.in/"
        },
    {
            "id": 39,
            "name": "THAPAR UNIVERSITY",
            "image": null,
            "status": "The details about the Thapar University B.Tech admission 2016 have been released. The admission will be held as per the JEE Main 2016 score but candidates will have to fill a separate application form for it. Interested candidates, who are also eligible, may access the link below to apply. The last date to submit the application form is 26 April 2016.

Last Date:26 Apr 2016",
            "profilePic": "https://upload.wikimedia.org/wikipedia/commons/d/da/ThaparUniversityLogo.jpg",
            "timeStamp": "1459595788930",
            "url": "http://www.thapar.edu/"
        },
]

Answer 1

json_encode($rows,JSON_PRETTY_PRINT); returns prettified data with newline characters. This is helpful for command line input, but as you've discovered doesn't look as pretty within the browser. The browser will accept the newlines as the source (and thus, viewing the page source will indeed show the pretty JSON), but they aren't used to format the output in browsers. Browsers require HTML.

Ideally, you should use <pre> tags to wrap your output. This will represent it on the browser the same way it's represented in the source.

If that's not adequate for some reason, you might also consider replacing the newline characters with the appropriate <br> tags. However, this forfeits some formatting with whitespace.

<?php

$data = array(
    'foo' => array(
        'bar',
        'baz'
    )
);

$jsonData = json_encode($data, JSON_PRETTY_PRINT);

echo "<h1>Original</h1>";
echo $jsonData;

echo "<h1>&lt;pre&gt;</h1><br>";
echo "<pre>" . $jsonData . "</pre>";

echo "<h1>str_replace()</h1><br>";
echo str_replace("\n", "<br>", $jsonData);

?>

Answer 2

Kind of an old question, so I assume you've probably already figured this out by now, but this shows up pretty often in questions where people are having trouble with getting the output they expect from json_encode, so for future readers, there are two main things you need to do to fix this:

1. The formatting is actually there, you just don't see it in the browser.

All the new lines and indentation that JSON_PRETTY_PRINT adds will not normally be displayed in your browser, but if you view the page source, they will be there. Unless you specify otherwise, your PHP output is probably going to be sent to the browser with a Content-Type:text/html header, so the browser will interpret the page as an HTML document, which means it will collapse all the white space (new lines and indentation) in the JSON to single spaces, which is why you're only seeing a single line on your screen.

If the entire output of your script is JSON, you can add a header at the top to specify that, so the browser will know it isn't HTML.

header('Content-type: Application/JSON');

If you have some JSON that you want to display along with other HTML content, you can use the <pre> tags as suggested in the other answer.

echo '<pre>'. json_encode($people, JSON_PRETTY_PRINT) . '</pre>';

2. Don't encode until you've fetched all the rows.

This is the other part of the problem:

while($r = mysqli_fetch_assoc($result)) {
    $rows[] = $r;
    print json_encode($rows, JSON_PRETTY_PRINT);
}

If you're wanting to output the result of query in JSON, you must wait until you have fetched all your results before encoding them.

With the code above, you're fetching a row, appending it to an array, then immediately printing the entire JSON-encoded array for every row in your query result. Do this instead.

while($r = mysqli_fetch_assoc($result)) {
    $rows[] = $r;                               // add rows to array
}
print json_encode($rows, JSON_PRETTY_PRINT);    // encode and print full array