convert mysql data to json with additional information using php

Question

Code

<?php
include "dbconfig.php";
include "yrsetup.php";
$tdate='2018-11-19';
$tmarket='sirsi';
$sql="SELECT ALLCODES.NAME AS Type,MIN(LOT1819.RATE) AS 
Min,MAX(LOT1819.RATE) AS Max,ROUND(SUM(LOT1819.RATE)/COUNT(LOTNO),0) AS avg 
FROM LOT1819 
    LEFT JOIN ALLCODES  ON allcodes.`sw`='IT' AND allcodes.`cd`=APCD
    WHERE MRK IS NULL AND DATE1='2018-11-19' AND LOT1819.SW IS NULL GROUP BY 
APCD";    

        $result=mysqli_query($conn,$sql);
        $response = array();
        $response = array('Date' => $tdate, 'market' => $tmarket);
        while($row=mysqli_fetch_assoc($result))
            {
                array_push($response,$row);
            }
            $outsidebrace=')';
            array_push($response,$outsidebrace);
            echo json_encode($response);
            //echo json_encode($response);
?>

result

{"Date":"2018-11-19","market":"sirsi","0":{"Type":"RASHI","Min":"30358","Max":"32699","avg":"31505"},"1":{"Type":"BETTE","Min":"21099","Max":"29618","avg":"27110"},"2":{"Type":"MURI","Min":"23121","Max":"23809","avg":"23465"},"3":{"Type":"KOLE","Min":"18019","Max":"23558","avg":"21928"},"4":{"Type":"K.G.","Min":"14199","Max":"22609","avg":"19109"},"5":{"Type":"CHALI","Min":"24039","Max":"26488","avg":"25773"},"6":{"Type":"B.G.","Min":"16699","Max":"22300","avg":"21109"},"7":{"Type":"11-CHALI","Min":"22720","Max":"23899","avg":"23512"},"8":{"Type":"KOKA","Min":"11589","Max":"20699","avg":"16902"},"9":{"Type":"CH.KEMP","Min":"18315","Max":"24079","avg":"21862"},"10":{"Type":"ARAGERE","Min":"22809","Max":"22809","avg":"22809"},"11":{"Type":"KE.BETTE","Min":"23731","Max":"24631","avg":"24128"},"12":{"Type":"PEPPER","Min":"34699","Max":"34899","avg":"34799"},"13":{"Type":"CHAKRA","Min":"17869","Max":"23119","avg":"20494"},"14":{"Type":"BT RASHI","Min":"26899","Max":"29718","avg":"28431"},"15":{"Type":"MURI BETTE","Min":"21490","Max":"21699","avg":"21594"},"16":{"Type":"MURI RASHI","Min":"25699","Max":"25699","avg":"25699"},"17":{"Type":"CHURU","Min":"7299","Max":"18009","avg":"12654"},"18":{"Type":"LA KOKA","Min":"6199","Max":"9399","avg":"7799"},"19":{"Type":"LA B GOT","Min":"11899","Max":"18499","avg":"15666"},"20":{"Type":"DBL CHALI","Min":"26418","Max":"26418","avg":"26418"},"21":{"Type":"GOT BETTE","Min":"14099","Max":"22421","avg":"18260"},"22":{"Type":"S BETTE","Min":"29518","Max":"29518","avg":"29518"},"23":")"}

this is valid json but i need result in below format

{
    "Date": "2018-11-19",
    "market": "Sirsi",
    "data": [{
        "Type": "RASHI",
        "Min": "30358",
        "Max": "32699",
        "avg": "31505"
    }, {
        "Type": "BETTE",
        "Min": "21099",
        "Max": "29618",
        "avg": "27110"
    }, {
        "Type": "MURI",
        "Min": "23121",
        "Max": "23809",
        "avg": "23465"
    }, {
        "Type": "KOLE",
        "Min": "18019",
        "Max": "23558",
        "avg": "21928"
    }, {
        "Type": "K.G.",
        "Min": "14199",
        "Max": "22609",
        "avg": "19109"
    }, {
        "Type": "CHALI",
        "Min": "24039",
        "Max": "26488",
        "avg": "25773"
    }, {
        "Type": "B.G.",
        "Min": "16699",
        "Max": "22300",
        "avg": "21109"
    }, {
        "Type": "11-CHALI",
        "Min": "22720",
        "Max": "23899",
        "avg": "23512"
    }, {
        "Type": "KOKA",
        "Min": "11589",
        "Max": "20699",
        "avg": "16902"
    }, {
        "Type": "CH.KEMP",
        "Min": "18315",
        "Max": "24079",
        "avg": "21862"
    }, {
        "Type": "ARAGERE",
        "Min": "22809",
        "Max": "22809",
        "avg": "22809"
    }, {
        "Type": "KE.BETTE",
        "Min": "23731",
        "Max": "24631",
        "avg": "24128"
    }, {
        "Type": "PEPPER",
        "Min": "34699",
        "Max": "34899",
        "avg": "34799"
    }, {
        "Type": "CHAKRA",
        "Min": "17869",
        "Max": "23119",
        "avg": "20494"
    }, {
        "Type": "BT RASHI",
        "Min": "26899",
        "Max": "29718",
        "avg": "28431"
    }, {
        "Type": "MURI BETTE",
        "Min": "21490",
        "Max": "21699",
        "avg": "21594"
    }, {
        "Type": "MURI RASHI",
        "Min": "25699",
        "Max": "25699",
        "avg": "25699"
    }, {
        "Type": "CHURU",
        "Min": "7299",
        "Max": "18009",
        "avg": "12654"
    }, {
        "Type": "LA KOKA",
        "Min": "6199",
        "Max": "9399",
        "avg": "7799"
    }, {
        "Type": "LA B GOT",
        "Min": "11899",
        "Max": "18499",
        "avg": "15666"
    }, {
        "Type": "DBL CHALI",
        "Min": "26418",
        "Max": "26418",
        "avg": "26418"
    }, {
        "Type": "GOT BETTE",
        "Min": "14099",
        "Max": "22421",
        "avg": "18260"
    }, {
        "Type": "S BETTE",
        "Min": "29518",
        "Max": "29518",
        "avg": "29518"
    }]
}

how i can solve this problem

Nigel Ren · Accepted Answer · 2018-11-22 10:07:46Z

You need to build up a nested array for the data, at the moment your adding it at the top level...

$result=mysqli_query($conn,$sql);
$response = array('Date' => $tdate, 'market' => $tmarket
               , 'data' => array());
while($row=mysqli_fetch_assoc($result))
    {
        $response['data'][] = $row;
    }
echo json_encode($response);

So in setting up the initial $reponse array, I add a data element with an empty array. Then inside the loop I add the new rows to this item (using [] rather than array_push()).

I have also removed the $outsidebrace bit as this isn't needed.

ADyson · Accepted Answer · 2018-11-22 10:09:59Z

Try it like this. You need to create the "data" property in your main array and then add the individiaul items to that, rather than adding them to the main array. I've also removed a couple of lines which appeared to be redundant.

$result=mysqli_query($conn,$sql);
$response = $response = array('Date' => $tdate, 'market' => $tmarket, "data"=> array());

while($row=mysqli_fetch_assoc($result))
{
    array_push($response["data"],$row);
}
echo json_encode($response);

Collectives™ on Stack Overflow

convert mysql data to json with additional information using php

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related