CODEWITHSUNDEEP
phpjsonnested
user2763405
user2763405

Reputation: 15

Create nested JSON objects using PHP

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

Upvotes: 0

Views: 2385

Answers (2)

jh1711
jh1711

Reputation: 2328

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.

Upvotes: 0

Gaurav
Gaurav

Reputation: 442

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"}
}

Upvotes: 1

Related Questions