Clever Geek Handbook

Transpose the returned JSON object (PHP, mySQL)

The object I am returning from my sql query is a single row with about 50 columns. It returns an object like this:

[
    {
        "State": "Alabama",
        "State Abbrev": "AL",
        "County": "Madison",
        "FIPS": "01089",
        "msa": "3440",
        "msaname": "HUNTSVILLE, AL",
        "cbsa": "26620",
        "cbsaname": "Huntsville, AL",
        "Population": "346892",
        "Length of Life Rank": "4",
        "Quality of Life Rank": "6",
    }
]

I need a format:

[
{ "column": "State", "value": "Alabama" },
 { "column": "State Abbrev", "value": "AL" },
 ...
 ]

How do I transfer the result according to my requirements? I am getting data like this:

$sql4 = "SELECT d.* FROM `mytable` s 
INNER JOIN `secondtable` c ON 
c.`zip_code` = s.`zip_code` 
INNER JOIN `anothertable` d ON 
d.`fips` = c.`fips` 
WHERE s.`ID` = '{$prov_number1}' group by `ID`"; 
$result4 = $dbh->query($sql4)->fetchAll(PDO::FETCH_ASSOC);
$data['demo_info'] = $result4;
header('Content-type: application/json');
echo json_encode($data);

EDIT: Some good answers, but I think my results will return incorrectly because my data is split differently than what I have above. If I am var_dump

$result4

, the structure looks something like this:

array(1) {
  [0]=>
  array(93) {
    ["State"]=>
    string(7) "Alabama"
    ["State Abbrev"]=>
    string(2) "AL"
    ["County"]=>
    string(7) "Madison"
    ["FIPS"]=>
    string(5) "01089"
    ["msa"]=>
    string(4) "3440"
    ["msaname"]=>
    string(14) "HUNTSVILLE, AL"
    ["cbsa"]=>
    string(5) "26620"
}}

I think this may change the answers as I tried several and didn't get anything in the "column" and my data is all overflowing with "values".

+3


source to share


4 answers 


$finalresult = array();
$icount = 0 ;
foreach($result4 as $row) {
     foreach ( $row as $key => $val ) {

        $finalresult[$icount]["column"] = $key;
        $finalresult[$icount]["value"] = $val;
        $icount++;
     }
}   
$data['demo_info'] = $finalresult;


+1


source


Using a loop foreach 

$result = array();
$i = 0;
foreach($arr as $key => $value){
    $result[$i]['column'] = $key;
    $result[$i]['value'] = $value;
    $i++;
}
echo json_encode($result);

And using array_map 



$result = array_map(function($k,$v){return array('column' => $k,'value' => $v);},  array_keys($arr),$arr);
echo json_encode($result);

DEMO

+2


source


Try the following:

$data = json_decode( $json );
$result = array();
$n=0;
foreach($data[0] as $k=>$v) {
    $result[$n]['column'] = $k;
    $result[$n]['value'] = $v;
    $n++;
}
$result_json = json_encode( $result );
+1


source


Just create an array like this and return -

$newArray = array_map(function($value, $key){
    return array('column' => $key, 'value' => $value);
}, $array, array_keys($array));

json_encode($newArray);
0


source






More articles:


All Articles