creating json object from sql database using php

Question

I am trying to create a json object from my mysql database for a android project. I need an output something like this:

{
    "feed": [
        {
            "id": 1,
            "name": "National Geographic Channel",
            "image": "http://api.androidhive.info/feed/img/cosmos.jpg",
            "status": "\"Science is a beautiful and emotional human endeavor,\" says Brannon Braga, executive producer and director. \"And Cosmos is all about making science an experience.\"",
            "profilePic": "http://api.androidhive.info/feed/img/nat.jpg",
            "timeStamp": "1403375851930",
            "url": null
        },
        {
            "id": 2,
            "name": "TIME",
            "image": "http://api.androidhive.info/feed/img/time_best.jpg",
            "status": "30 years of Cirque du Soleil's best photos",
            "profilePic": "http://api.androidhive.info/feed/img/time.png",
            "timeStamp": "1403375851930",
            "url": "http://ti.me/1qW8MLB"
        }
]
}

But I am getting ouput something like this:

{"feed":[{"id":"0","name":"punith","image":"","status":"ucfyfcyfffffffffffffffffffffffffffffffff","profilePic":"http:\/\/api.androidhive.info\/feed\/img\/nat.jpg","timestamp":"1403375851930","url":""}]}

Everything is on a single line and the id attribute should not be quotes. Is there anything I could do.This is my php file

<?php
define('HOST','');
define('USER','');
define('PASS','');
define('DB','');

$con = mysqli_connect(HOST,USER,PASS,DB);

$sql = "select * from timeline";

$res = mysqli_query($con,$sql);

$result = array();

while($row = mysqli_fetch_array($res)){
array_push($result,
array('id'=>$row[0],
'name'=>$row[1],
'image'=>$row[2],
'status'=>$row[3],
'profilePic'=>$row[4],
'timestamp'=>$row[5],
'url'=>$row[6]
));
}

echo json_encode(array("feed"=>$result));

mysqli_close($con);

?>

And will it affect if the output is on a single line. The database contains exactly the same columns used as attributes.

Thanks in advance

You can specify `JSON_PRETTY_PRINT` as the second parameter of [`json_encode`](http://www.php.net/manual/en/function.json-encode.php). — Passerby, Aug 21 '15 at 09:42
Your question and explanation at the end helped me to create a complex json without resorting to JSON_* functions of mysql. $jres = array(); while($row = mysqli_fetch_array($result)) { array_push($jres, array('value' => $row[0], 'data' => array( 'last' => $row[0], 'expcode' => $row[1] ) ) ); } — Polymath, Aug 24 '22 at 07:01

Jitender Dev · Answer 1 · 2015-08-21T09:48:48.567

1

Try encoding with JSON_PRETTY_PRINT

$json_string = json_encode($data, JSON_PRETTY_PRINT);

where data is your result array.

in your case

echo json_encode(array("feed"=>$result),JSON_PRETTY_PRINT);

refer this Tutorial from the official PHP docs .

edited Aug 21 '15 at 09:48

answered Aug 21 '15 at 09:42

Jitender Dev

6,907
2
24
35

score 1 · Accepted Answer · edited May 23 '17 at 11:58

ID in quotes

ID is in quotes because it is a string, not an integer. You can change that by changing this:

array('id'=>$row[0]

to this:

array('id'=>intval($row[0])

"Pretty Printing"

Putting it on multiple lines will only affect readability but not how the data is computed - but you can prettify it: Pretty-Printing JSON with PHP

$output = json_encode(array("feed"=>$result), JSON_PRETTY_PRINT);
echo $output;

score 0 · Answer 3 · answered Aug 21 '15 at 09:46

Aswell as the other answers, it would be more semantic to include the json header, I've found that just including this also helps with the appearance of the json itself so that it is formatted properly and not all one continuous string.

header('Content-Type: application/json');

score 0 · Answer 4 · answered Aug 21 '15 at 11:38

0

For php>5.4

$json=json_encode(array("feed"=>$result),JSON_PRETTY_PRINT);
header('Content-Type: application/json');
print_r($json);

answered Aug 21 '15 at 11:38

dknepa

116
2
8

creating json object from sql database using php

4 Answers4