php array for google charts api

[kris1988edwards]

Good morning, 

 

I am doing a small project including google charts API but I am having trouble getting  my results into the json format that is required. 

 

Google's documentation states the json format should be:

{
  "cols": [
        {"id":"","label":"Topping","pattern":"","type":"string"},
        {"id":"","label":"Slices","pattern":"","type":"number"}
      ],
  "rows": [
        {"c":[{"v":"Mushrooms","f":null},{"v":3,"f":null}]},
        {"c":[{"v":"Onions","f":null},{"v":1,"f":null}]},
        {"c":[{"v":"Olives","f":null},{"v":1,"f":null}]},
        {"c":[{"v":"Zucchini","f":null},{"v":1,"f":null}]},
        {"c":[{"v":"Pepperoni","f":null},{"v":2,"f":null}]}
      ]
}

the query works perfectly fine thats not the problem but getting it into the above format is proving hard than I expected. The below is what I am trying to do to populate the json data in the correct format. 

 

function graphdata()
{
$array['cols'][] = array('type' => 'string');
    $array['cols'][] = array('type' => 'string');
    $array['cols'][] = array('type' => 'string');


$result = sqlsrv_query($conn, $query);
while($row = sqlsrv_fetch_object($result)){ 


$array['rows'][] = array('c' => array( array('v'=>'$row->DateCreated->format('d-m-Y')'), array('v'=>$row->UnitPrice)) );


}
return $array;


}


print json_encode(graphdata());

I am using MSSQL and not the normal mysql hence the above code, i am a starter when it comes to php so I may be doing something basic totally wrong but I cannot seem to get it to work. 

 

Thanks

 

[boompa]

Building your example data:

 

<?php

$data = array();
$cols = array();
array_push($cols, array("id" => "", "label" => "Topping", "type" => "string"));
array_push($cols, array("id" => "", "label" => "Slices", "type" => "number"));

$data['cols'] = $cols;

$toppings = array('mushrooms' => 3, 'Onions' => 1, 'Olives' => 1, 'Zucchini' => 1, 'Pepperoni' => 2);

$rows = array();

foreach($toppings as $topping => $quantity) {
   $elements = array();
   array_push($elements, array("v" => $topping, "f" => null));
   array_push($elements, array("v" => $quantity, "f" => null));
   $row = array("c" => $elements);
   array_push($rows, $row);
}

$data['rows'] = $rows;

echo json_encode($data, JSON_PRETTY_PRINT);

 

 

{
    "cols": [
        {
            "id": "",
            "label": "Topping",
            "type": "string"
        },
        {
            "id": "",
            "label": "Slices",
            "type": "number"
        }
    ],
    "rows": [
        {
            "c": [
                {
                    "v": "mushrooms",
                    "f": null
                },
                {
                    "v": 3,
                    "f": null
                }
            ]
        },
        {
            "c": [
                {
                    "v": "Onions",
                    "f": null
                },
                {
                    "v": 1,
                    "f": null
                }
            ]
        },
        {
            "c": [
                {
                    "v": "Olives",
                    "f": null
                },
                {
                    "v": 1,
                    "f": null
                }
            ]
        },
        {
            "c": [
                {
                    "v": "Zucchini",
                    "f": null
                },
                {
                    "v": 1,
                    "f": null
                }
            ]
        },
        {
            "c": [
                {
                    "v": "Pepperoni",
                    "f": null
                },
                {
                    "v": 2,
                    "f": null
                }
            ]
        }
    ]
}

[Barand]

Does this get you any closer? (not tested)

function graphdata()
{
    $array['cols'][] = array(
            'id' => '',
            'label' => 'DateCreated',
            'pattern' => '',
            'type' => 'string'
            );
    $array['cols'][] = array(
            'id' => '',
            'label' => 'UnitPrice',
            'pattern' => '',
            'type' => 'number'
            );
            
    $result = sqlsrv_query($conn, $query);
    while($row = sqlsrv_fetch_object($result)){ 
        $array['rows'][] = array (
                        'c' =>array(
                                array ('v' => $row->DateCreated->format('d-m-Y'), 'f' => ''),
                                array ('v' => $row->UnitPrice, 'f' => ''),
                            )
                        );
    }
    return $array;
}

[kris1988edwards]

@barand - no it doesn't seem to work I've added in error reporting and getting the following:

 

Notice: Undefined variable: sqlsrv_query() expects parameter 1 to be resource

 

Connection to the database and everything is working correctly.

[Barand]

You need to pass $conn to the function

function graphdata($conn) 
{
...
}

print json_encode(graphdata($conn));