Create JSON from SQL Query using PHP

Question

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

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

So all you are getting from the database is this one single row? Then you don’t want to loop over the records in the result set to begin with (no need to loop anything for a total “iteration count” of one), but you want to loop over the columns of this one single record to begin with. — 04FS
– 04FS, Commented May 28, 2019 at 12:22
You can't use 'cONTIME' => $row['cONTIME'], before you've defined the $row-array. That will always give you null (and a warning in your error log about undefined variable). — M. Eriksson
– M. Eriksson, Commented May 28, 2019 at 12:31

u_mulder · Accepted Answer · 2019-05-28 12:35:37Z

1

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';
    }
}

edited May 28, 2019 at 12:35

answered May 28, 2019 at 12:31

u_mulder

54.8k5 gold badges54 silver badges72 bronze badges

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

2 Comments

M. Eriksson Over a year ago

You're missing # in front of the middle color :-)

edwardinchains Over a year ago

soz - was using the app - cheers @u_mulder - that worked a charm

Collectives™ on Stack Overflow

Create JSON from SQL Query using PHP

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related