Re: Google-charts date range filter from mysql

[asgallant]

You need to input the dates as Date objects in order to use them like dates.  I would recommend handling this with a change in your PHP (and a small change in javascript) to put the query results in the JSON DataTable format, which would solve the issue cleanly.  If you want a quick-and-dirty solution, you can use a DataView to transform the strings into Date objects, and use the view instead of the DataTable to draw your Dashboard. 

Changing the PHP would look something like this:

$SQLString = "SELECT    
	count(score) as counts,
	score, month,
	date FROM persons  
	GROUP BY day, month, year 
	ORDER BY date asc";     

$result = mysql_query($SQLString);   
$num = mysql_num_rows($result);   

# setup DataTable
$data['cols'] = array(
	array('type' => 'date', 'label' => 'Day'),
	array('type' => 'number', 'label' => 'Counts'),
);       
for ($i = 0; $i < $num; $i++) {
	$date = substr(mysql_result($result, $i, "date"), 0, 10);
	$dateArr = explode('-', $date);
	$year = $dateArr[0];
	$month = $dateArr[1] - 1; // subtract 1 because javascript uses a zero-based index for months
	$day = $dateArr[2];
	$data['rows'][$i] = array('c' => array(
		array('v' => "Date($year, $month, $day)"),
		array('v' => (int) mysql_result($result, $i, "counts"))
	));
}   
echo json_encode($data);

and you would change the javascript DataTable constructor to this:

var data = new google.visualization.DataTable(obj);

Also, you shouldn't need to parse the returned JSON, as jQuery is supposed to do that for you when you set the dataType as JSON.

If you want the quick-and-dirty version, add this to your js, and draw the Dashboard using the DataView:

var view = new google.visualzation.DataTable(data);
view.setColumns([{
    type: 'date',
    label: data.getColumnLabel(0),
    calc: function (dt, row) {
        var dateArr = dt.getValue(row, 0).split('-');
        var year = dateArr[0];
        var month = dateArr[1] - 1; // subtract 1 because javascript uses a zero-based index for months
        var day = dateArr[2];
        return new Date(year, month, day);
    }
}, 1]);

On Monday, November 19, 2012 8:56:16 AM UTC-5, Pedro Guimarães wrote:

$SQLString = "SELECT    
                count(score) as counts,
                score, month,
                date FROM persons  
                GROUP BY day, month, year 
                ORDER BY date asc";     

    $result = mysql_query($SQLString);   
    $num = mysql_num_rows($result);   

# set heading   
    $data[0] = array('day','counts');       
    for ($i=1; $i<($num+1); $i++)
    {
        $data[$i] = array(substr(mysql_result($result, $i-1, "date"), 0, 10),
            (int) mysql_result($result, $i-1, "counts"));
    }   
    echo json_encode($data);

This gives me something like this: [["day","counts"],["2012-01-20",1],["2012-02-06",4]

function drawChart() {
            var jsonData = $.ajax({
                url: "charts.php",
                dataType: "json",
                async: false
            }).responseText;

            var obj = jQuery.parseJSON(jsonData);
            var data = google.visualization.arrayToDataTable(obj);

            var options = {
                title: 'Counts'
            };

Now, i want to build my chart with a date range controler:

var control = new google.visualization.ControlWrapper({
                 'controlType': 'ChartRangeFilter',
                 'containerId': 'control',
                 'options': {
                   // Filter by the date axis.
                   'filterColumnIndex': 0,
                   'ui': {
                     'chartType': 'LineChart',
                     'chartOptions': {
                       'chartArea': {'width': '90%'},
                       'hAxis': {'baselineColor': 'none'}
                     },
                     // Display a single series that shows the closing value of the stock.
                     // Thus, this view has two columns: the date (axis) and the stock value (line series).
                     'chartView': {
                       'columns': [0,1]
                     },
                     // 1 day in milliseconds = 24 * 60 * 60 * 1000 = 86,400,000
                     'minRangeSize': 86400000
                   }
                 },
                 // Initial range: 2012-02-09 to 2012-03-20.
                 'state': {'range': {'start': new Date(2012, 1, 1), 'end': new Date(2012, 4, 20)}}
               });

The problem is:

["2012-01-20",1], that "2012-01-20" is a string and that date controler only works with date types, what could I do ?

Thanks

[Pedro Guimarães]

Hi,

Thanks for being helping me.

Ill follow your tip, but how do I draw thechart now ?

I was doing this:

var dashboard = new google.visualization.Dashboard(document.getElementById('dashboard_div')).bind(chart).draw(data);

[asgallant]

Did you modify your PHP, or use the DataView?  If you changed your PHP, you don't have to adjust how you draw your chart.  If you used the DataView, then you need to replace "data" in that line with the DataView object variable ("view" in the code I posted).