Question 40614 in PHP Question posted in
Official manual can be found here.

Create json array from multiple sql table – PHP

I have create the json file from the sql table but the output is not same with the output i expected. Below is my code to create the json file from the sql table

<?php
include 'config.php';


$sql = "SELECT table_id, table_name,GROUP_CONCAT(price) price FROM tables INNER JOIN table_orders ON table_id = res_table_id GROUP BY table_id";
$result = mysqli_query($mysqli,$sql);

while($data1 = $result->fetch_assoc()){
    $data[] = $data1;
}

$encoded_data = json_encode($data,JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
file_put_contents('data_json',$encoded_data);

?>

The output will look like this

[
    {
        "table_id": "1",
        "table_name": "Table 1",
        "price": "20.00,15.00,7.90"
    },
    {
        "table_id": "2",
        "table_name": "Table 2",
        "price": "7.90,15.00"
    }
]

I want my result something like the output below:

{"table":[
    {
        "table_id": "1",
        "table_name": "Table 1",
        "price": [
            "20.00","15.00","7.90"
        ]
    },
    {
        "table_id": "2",
        "table_name": "Table 2",
        "price": [
            "7.90","15.00"
        ]
    }
]}
Tags:

2

Answers


  1. 0 
    $my_new_data = [];
while($data1 = $result->fetch_assoc()){
    array_push($my_new_data, $data1);
}
    Login or Signup to reply.
  2. 0 
    <?php
include 'config.php';

$sql = "SELECT table_id, table_name,GROUP_CONCAT(price) price FROM tables INNER JOIN table_orders ON table_id = res_table_id GROUP BY table_id";
$result = mysqli_query($mysqli,$sql);
$data = [];

while($data1 = $result->fetch_assoc()){
    $data1['price'] = explode(',', $data1['price']);
    $data[] = $data1;
}

$encoded_data = json_encode(['table' => $data], JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
file_put_contents('data_json', $encoded_data);

    What this does:

    In your initial loop, you first format your array items in the way you want. Then you can format your JSON string in the nested structure you want by passing in the format you like.

    There are a few ways to optimize this, but that’s the gist of it.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search