Google Charts from MySQL JSON Encoded Array

Question

I'm having one heck of a time here, how can I draw up the chart from a json_encoded mySQL array?

Data Retrieval (simply associated array from a PDO query):

    if($cCt > 0){
        header('Content-Type:application/json');    
        $table['cols'] = array(
                                array('id'=>'Date', 'label'=>'Date', 'type'=>'string'),
                                array('id'=>'Carbs', 'label'=>'Carbs', 'type'=>'number'),
                                array('id'=>'Sugar', 'label'=>'Sugar', 'type'=>'number'),
                                array('id'=>'Units', 'label'=>'Units', 'type'=>'number'));
        for($i=0; $i<$cCt; ++$i){
            $W = (isset($_GET['which'])) ? (int)$_GET['which'] : 0;
            switch($W){
                case 1: // weekly
                case 2: // monthly
                case 3: // yearly
                    $date = date('m/d', strtotime($CNums[$i]['Date']));
                    break;
                case 4: // daily
                    $date = date('m/d g:i a', strtotime($CNums[$i]['Date']));
                    break;
                default:
                    $date = date('m/d g:i a', strtotime($CNums[$i]['Date']));
                    break;
            }
            $rows[] = array('c'=>array('v'=>$date, 'v'=>$CNums[$i]['Carbs'], 'v'=>$CNums[$i]['Sugar'], 'v'=>$CNums[$i]['Units']));
        }
        $table['rows'] = $rows; 
        echo json_encode($table, JSON_NUMERIC_CHECK);
    }else{
        echo ErrorBox('Please login to see your charts.');
    }

Code Attempt:

<script type="text/javascript">

    google.load('visualization', '1.0', {'packages':['corechart']});
    google.setOnLoadCallback(drawChart);
    function drawChart() {
        var $dUrl = "/assets/php/charts/chart.data.php?_=<?php echo time(); ?>&which=<?php echo (isset($which)) ? $which : 4; ?>&d=<?php echo (isset($d)) ? $d : null; ?>&dl=<?php echo (isset($dl)) ? $dl : null; ?>";

        jQuery.getJSON($dUrl, function(d){
            // Create a new blank DataTable         
            var data = new google.visualization.DataTable(d);
            // Create our columns
            /*
            data.addColumn('date', 'Date');
            data.addColumn('number', 'Carbs');
            data.addColumn('number', 'Sugar');
            data.addColumn('number', 'Units');
            // Create our Rows
            jQuery.each(d, function(i) {
                data.addRows([d[i].Dates, d[i].Carbs, d[i].Sugar, d[i].Units]);
            }); 
            */      
            var options = {
                'colors': ['red', 'blue', 'yellow'],
                'width': '98%', 
                'height': 280, 
                'backgroundColor': 'none',
                'hAxis': {'textStyle': {fontName: 'Calibri', 
                    fontSize: '12'}},
                'vAxis': {'textStyle': {fontName: 'Calibri', 
                    fontSize: '12'}},
                'legendTextStyle': {fontName: 'Calibri', 
                    fontSize: '12'}
            };
            //var chart = new google.visualization.ColumnChart(document.getElementById('<?php echo $where; ?>'));
            var chart = new google.visualization.ColumnChart(document.getElementById('weekly_chart_div'));
            chart.draw(data, options);

        }).fail(function(msg){console.log('Error pulling in the data.' + msg);});

    }
</script>

Original JSON from the $dUrl:

{"cols":[{"id":"Date","label":"Date","type":"string"},
    {"id":"Carbs","label":"Carbs","type":"string"},
    {"id":"Sugar","label":"Sugar","type":"string"},
    {"id":"Units","label":"Units","type":"string"}],
"rows":[["08\/23","40.0000000","256.0000000","9.0000000"],
    ["08\/24","33.8333333","102.5000000","4.6666667"],
    ["08\/25","38.2000000","290.2000000","10.6000000"],
    ["08\/26","36.0000000","322.0000000","12.0000000"],
    ["08\/28","23.6666667","348.3333333","9.6666667"],
    ["08\/29","31.3333333","214.1666667","7.3333333"],
    ["08\/30","16.0000000","154.0000000","4.0000000"]]}

New JSON after Data Retrieval Update:

{"cols":[{"id":"Date","label":"Date","type":"string"},
    {"id":"Carbs","label":"Carbs","type":"number"},
    {"id":"Sugar","label":"Sugar","type":"number"},
    {"id":"Units","label":"Units","type":"number"}],
"rows":[{"c":{"v":9}},{"c":{"v":4.6666667}},{"c":{"v":10.6}},{"c":{"v":12}},{"c":{"v":9.6666667}},{"c":{"v":7.3333333}},{"c":{"v":4}}]}

Yes, right now I am getting an error this.J[a].c is undefined, but it only shows where the chart should be loaded...not in FireBug

What I am aiming for is something like this: example: http://o7th.com/Image3.png

Answer 1

Your rows are not formatted correctly. Rows is an array of objects, where each object has a "c" (array of cells) and an optional "p" (properties object) parameters. The array of cells is an array of objects with "v" (column data type, value of cell) and optional "f" (string, formatted value of cell) and "p" (parameters object) properties.

As an example, your first row of data should look like this:

{"c":[{"v":"08\/23"},{"v":40,"f":"40.0000000"},{"v":256,"f":"256.0000000"},{"v":9,"f":"9.0000000"}]}

In order to generate that from a JSON encoded PHP array, the array would have to look like this:

$row = array(
    'c' => array(
        array('v' => '08\/23'),
        array('v' => 40, 'f' => "40.0000000"),
        array('v' => 256, 'f' => "256.0000000"),
        array('v' => 9, 'f' => "9.0000000")
    )
);

By default, MySQL outputs all numbers as strings, so you need to cast them as ints or floats in order for them to be output as numbers, like this:

$foo = '33.333333'; // $foo == '33.333333'
$bar = (int) $foo; // $bar == 33
$cad = (float) $foo; // $cad == 33.333333

You can change or remove the formatted values if you don't want them (they are what will appear in the tooltips of the chart).

Edit:

You need to give each cell it's own array in the cells array; try this:

$rows[] = array(
    'c'=>array(
        array('v'=>$date),
        array('v'=>$CNums[$i]['Carbs']),
        array('v'=>$CNums[$i]['Sugar']),
        array('v'=>$CNums[$i]['Units'])
    )
);