Creating JSON code with PHP for NVD3 charts

[engy123]

Hi,

 

I'm absolutely stuck with this piece of code, I've searched and searched to get it working and have failed miserably. It also doesn't help that I'm brand new to php

 

My Json output currently looks like this 

 

  

 

    

 [

        {

            "key": "OWM1",

            "values": [

                [

                    "x : EW",

                    "y :4"

                ],

                [

                    "x : RSE",

                    "y :3"

                ],

                [

                    "x : SWE",

                    "y :2"

                ],

                [

                    "x : WTE",

                    "y :1"

                ],

                [

                    "x : WWE",

                    "y :1"

                ]

            ]

        },

        {

            "key": "OWM2",

            "values": [

                [

                    "x : EW",

                    "y :4"

                ],

                [

                    "x : RSE",

                    "y :2"

                ],

                [

                    "x : SWE",

                    "y :1"

                ],

                [

                    "x : WTE",

                    "y :3"

                ],

                [

                    "x : WWE",

                    "y :2"

                ]

            ]

        }

    ]

 

 

But I need it to look like this for the sake of NVD3 chart to be able to process it correctly.

 

 

    [

      {

        "key": "OWM1",

    

        "values":

          [      

            { x : "EW", y : 4 },

            { x : "RSE", y : 3 },

            { x : "SWE",   y : 2 }  

            { x : "WTE",   y : 1 }  

            { x : "WWE",   y : 21 }  

          ]

      },

       {

        key: "OWM2",

    

        values:

          [      

            { x : "EW", y : 4 },

            { x : "RSE", y : 2 },

            { x : "SWE",   y : 1 }  

            { x : "WTE",   y : 3 }  

            { x : "WWE",   y : 2 } 

          ]

      }]

 

 

 

 

 

Here is my PHP code that I am producing the first output of json with.

 

 

 

   $result = mysqli_query($con,"SELECT CODE, _POS, COUNT(CODE) AS COUNT from defects WHERE FPOS IN ('OWM1','OWM2') GROUP BY POS, CODE");

    

    

    if($result) {

        $jsonData = convert($result);

    }

    

    function convert($result) {

    

        $intermediate = array();

    

        while($rows = mysqli_fetch_assoc($result)) {

            $key = $rows['POS'];

            $x = $rows['CODE'];

            $y = $rows[''];

            $intermediate[$key][] = array('x : ' .$x,'y :' . $y);

        }

    

     

       $output = array();

    

        foreach($intermediate as $key => $values) {

            $output[] = array(

                "key" => $key,

                'values' => $values

            );

        }

    

        echo json_encode($output);

        

    }

        

    

    

    

    

    mysqli_close($con);

 

 

Is there any way anyone could help me manipulate the code I have to look exactly like the second set of JSON code that is compatible with NVD3 charts?

 

 

Thanks alot.

 

[Frank_b]

in JSON:

 

[] is an array.

{} is an object.

 

Now we do have two types of arrays in PHP.

 

1. normal / numeric

2. associative

 

a numeric array will produce a JSON array like this:

[
  "Frank",
  "Netherlands",
  44
] 

while an associative will produce a JSON object(!) like this:

{
  "Frontname":"Frank",
  "Country":"Netherlands",
  "Age":44
}

Now the first opening array is a numeric one (because we see a [ character).

The elements inside that array are JSON objects so we need to start there with an associative PHP array.

 

At the end we will get this:

<?php
$array = array( // numeric array
	array( // associative array
		'key' => 'OWM1',
		'values' => array( // numeric array
			array( // associative array
				'x' => 'EW',
				'y' => 4
			),
			array(
				'x' => 'RSE',
				'y' => 3
			),
			// and so on
		)
	),
	array(
		'key' => 'OWM2',
		'values' => array(
			array(
				'x' => 'EW',
				'y' => 4
			),
			array(
				'x' => 'RSE',
				'y' => 2
			),
			// and so on
		)
	),
);

// show us the json:
echo '<pre>';
echo json_encode($array, JSON_PRETTY_PRINT);
echo '</pre>';
?>

Edited September 28, 2014 by Frank_b