CODEWITHSUNDEEP
phpjson
edwardinchains
edwardinchains

Reputation: 119

Create JSON from SQL Query using PHP

I am attempting to output JSON in the following format:

[{
  "category": "cONTIME",
  "value": 155,
  "color": "#0f9b0f"
},{
  "category": "cLATE",
  "value": 244,
  "color": "#dd1818"
},{
  "category": "cTOTAL",
  "value": 399,
  "color": "#0575E6"
},{
  "category": "ONTIME",
  "value": 60,
  "color": "#0f9b0f"
},{
  "category": "LATE",
  "value": 58,
  "color": "#dd1818"
},{
  "category": "TOTAL",
  "value": 118,
  "color": "#0f9b0f"
}]

The stored procedure I am executing returns the following table - This is SQL SERVER - I have greyed over the columns that are not of interest

enter image description here

I have attempted this by using 

$categories = [
    'cONTIME' => $row['cONTIME'],
    'cLATE' => [],
    'cTOTAL' => [],
];
$colors = [
    'ONTIME' => "#0f9b0f",
    'LATE' => "#dd1818",
    'TOTAL' => "#0575E6",
];

$json = array();

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
        $json['category'] = $categories;
        $json['value'] = $row;
        $json['color'] = $colors;
}
echo json_encode($json);

Returning this: 

{
   "category":{
      "cONTIME":null,
      "cLATE":[

      ],
      "cTOTAL":[

      ]
   },
   "value":{
      "cTOTAL":399,
      "cONTIME":155,
      "cLATE":244,
      "cPERCENTS":38,
      "TOTAL":118,
      "ONTIME":60,
      "LATE":58,
      "PERCENTS":50,
      "KPI":"UP"
   },
   "color":{
      "ONTIME":"#0f9b0f",
      "LATE":"#dd1818",
      "TOTAL":"#0575E6"
   }
}

I have also attempted this:

$json = array();

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {          
        $json[] = $row;
}
echo json_encode($json);

Returning: 

[
   {
      "cTOTAL":399,
      "cONTIME":155,
      "cLATE":244,
      "cPERCENTS":38,
      "TOTAL":118,
      "ONTIME":60,
      "LATE":58,
      "PERCENTS":50,
      "KPI":"UP"
   }
]

As well as the above I have tried numerous other methods & examples but can't seem to achieve the format I need. I am fully aware of PDO, Prepared Statements, Injection etc!, I just need to format the JSON first, but thanks in advance for the advice

Any help would be greatly appreciated - PHP Noob

Upvotes: 0

Views: 111

Answers (1)

u_mulder
u_mulder

Reputation: 54841

Something like this should work:

$json = array();

while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {          
    foreach ($row as $fieldName => $fieldValue) {
        if (in_array($fieldName, ['cTOTAL', 'cONTIME', 'cLATE', 'TOTAL' ,'ONTIME', 'LATE'])) {
            $json[] = [
                "category" => $fieldName,
                "value" => $fieldValue,
                "color" => getColorByFieldName($fieldName),
            ];
        }
    }
}
echo json_encode($json);

function getColorByFieldName($fieldName) 
{
    if ($fieldName === 'ONTIME' || $fieldName === 'cONTIME') {
        return '#0f9b0f';
    }
    if ($fieldName === 'LATE' || $fieldName === 'cLATE') {
        return '#dd1818';
    }
    if ($fieldName === 'TOTAL' || $fieldName === 'cTOTAL') {
        return '#0575E6';
    }
}

Upvotes: 1

Related Questions