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

asgallant · Accepted Answer · 2013-08-30 15:15:45Z

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'])
    )
);

edited Aug 30, 2013 at 15:15

answered Aug 30, 2013 at 14:54

asgallant

26.4k6 gold badges75 silver badges91 bronze badges

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

4 Comments

Kevin Over a year ago

ok, now for some reason my JSON isn't returning correct values anymore. I'll post the code for it above

Kevin Over a year ago

posted data retrieval above... json returned is not the same as it was... and i have php 5.3 installed, so adding in the JSON_NUMERIC_CHECK to json_encode properly formats the numbers for me :)

asgallant Over a year ago

Each cell requires its own array. I'll update my post with some PHP for you to try.

Kevin Over a year ago

yeah, got it. I must've miss-saw (lol) the extra array in there. Udpated and now works. Thanks!

Collectives™ on Stack Overflow

Google Charts from MySQL JSON Encoded Array

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related