Create nested JSON objects using PHP

Question

I have the following MySQL table:

id     desc     qty
--------------------
10      abc      5
20      xyz      12
30      qwe      9

How can I use PHP/MySQL query to create the following JSON file?

{
"10":{"desc":"abc","qty":"5"},
"20":{"desc":"xyz","qty":"12"},
"30":{"desc":"qwe","qty":"9"}
}

Here is my attempt

$query="SELECT id,desc,qty FROM table";
$result = @mysql_query($query);
while ($row=mysql_fetch_object($result))
{ 
    $data[]=$row;
}
echo json_encode($data);

The result is an array and I am not sure how to display it correctly

[
{"id":"10","desc":"abc","qty":"5"},
{"id":"20","desc":"xyz","qty":"15"},
{"id":"30","desc":"qwe","qty":"9"}
]

Any help is appreciated

Welcome to StackOverflow! This task shouldn't be so complicated, but we are happy to help if you tell us where you got stuck! Right now there's nothing we can jump on. Is it the database thing? Is it the logic? Is it the json conversion? — Jeff
– Jeff, Commented Apr 30, 2018 at 23:40
@Jeff I updated the original question with my current code and incorrect output. — user2763405
– user2763405, Commented Apr 30, 2018 at 23:52

Gaurav · Accepted Answer · 2018-04-30 23:58:39Z

1

There are many ways to achieve this.One of them is below. changes your database credentials and table name.

<?php 
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDB";
$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT * FROM TableName";
$result = $conn->query($sql);
$results_array =array();
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $results_array[$row['id']] = array(
                        'desc'=>$row['desc'],
                        'qty'=>$row['qty'],
                                );
    }
} else {
    echo "0 results";
}

$json_array = json_encode($results_array);
echo $json_array;

Results should be look alike

{
"1":{"desc":"abc","qty":"12"},
"2":{"desc":"xyz","qty":"54"}
}

edited Apr 30, 2018 at 23:58

answered Apr 30, 2018 at 23:53

Gaurav

4423 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jh1711 · Accepted Answer · 2018-05-01 00:18:48Z

0

Thanks for editing your question. You can change the structure of your results with another loop:

foreach($data as $d)
  $r[$d->id] = ['desc' => $d->desc, 'qty' => $d->qty];
$data = $r;

right above:

echo json_encode($data);

or you could adjust your while loop to get the same result:

$data[$row->id] = ['desc' => $row->desc, 'qty' => $row->qty];

or you could use array column (again right above json_encode):

$data = array_column($data, null, 'id');

The last example produces a slightly different output, but you can still use it in many situations. If you can't the first two options are preferable.

edited May 1, 2018 at 0:18

answered May 1, 2018 at 0:11

jh1711

2,3381 gold badge14 silver badges20 bronze badges

Collectives™ on Stack Overflow

Create nested JSON objects using PHP

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related