Need help on generating timeline chart using json file

Vamshi Krishna

unread,

Oct 23, 2014, 5:19:33 AM10/23/14

to google-visua...@googlegroups.com

Hi,

I am looking to generate a timeline chart using the json file, but below is the error I am getting. Please help me in fixing this.

JSON file used :

"cols": [

{"id":"Room","label":"Room","pattern":"","type":"string"},

{"id":"Name","label":"Name","pattern":"","type":"string"},

{"id":"Start","label":"Start","pattern":"","type":"date"},

{"id":"End","label":"End","pattern":"","type":"date"}

],

"rows": [

{"c":[{"v":"President","f":null},{"v":"Vamshi","f":null},{"v": new Date(2014,09,23),"f":null},{"v": new Date(2014,09,31),"f":null}]},

{"c":[{"v":"Lab Admin","f":null},{"v":"krishna","f":null},{"v": new Date(2014,09,25),"f":null},{"v": new Date(2014,09,30),"f":null}]}

]

}

html file :

<html>

<head>

</script>

// Load the Visualization API and the piechart package.

//google.load('visualization', '1', {'packages':['corechart']});

google.load("visualization", "1", {packages: ["timeline"]});

// Set a callback to run when the Google Visualization API is loaded.

google.setOnLoadCallback(drawChart);

function drawChart() {

var jsonData = $.ajax({

url: "getData.php",

dataType:"json",

async: false

}).responseText;

// Create our data table out of JSON data loaded from server.

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

// Instantiate and draw our chart, passing in some options.

var chart = new google.visualization.Timeline(document.getElementById('chart_div'));

chart.draw(data, {width: 1000, height: 600})

}

</script>

</head>

<body>

</body>

</html>

getData.php file used:

<?php

// This is just an example of reading server side data and sending it to the client.

// It reads a json formatted text file and outputs it.

$string = file_get_contents("sampleData.json");

//echo $string;

// Instead you can query your database and parse into JSON etc etc

?>

Andrew Gallant

unread,

Oct 23, 2014, 7:43:50 PM10/23/14

to google-visua...@googlegroups.com

The problem is in the JSON; Date objects are not valid JSON. The Visualization API uses a string format for passing dates via JSON. The string format is "Date(year, month, day)" - it is a string that looks like a Date object, without the "new" keyword. As an example, your first row of data should look like this:

{"c":[{"v":"President","f":null},{"v":"Vamshi","f":null},{"v": "Date(2014,09,23)","f":null},{"v": "Date(2014,09,31)","f":null}]},

Vamshi Krishna

unread,

Oct 24, 2014, 10:57:40 AM10/24/14

to google-visua...@googlegroups.com

Thanks Andrew. It worked with the correction that you mentioned. It was the static data which is working. However, I want a JSON file similar to the above one to be generated from the data fetched from database. I am working with PHP and MYSQL. When i run a select query and generate the PHP file i observed that there are few things missing in the JSON file. Also guess may be because of that the graph was also not getting generated, instead a blank page was displayed.

My PHP code to fetch data from db and generate a JSON file

<?php

$cal_st_date = '2014-10-2';

$cal_end_date = '2014-10-9';

$gph_values = array();

include 'db.php';

$fetch_rows = mysql_query("SELECT cg.attach_doc_path,cg.start_date,cg.end_date,cg.coupon_upc,cg.coupon_type FROM CPNS_TESTING cg,CPNS_TESTED ct where cg.cpn_id=ct.coupon_id and ct.approved='Y' and ct.valid='T' and cg.end_date >= '$cal_st_date' group by cg.cpn_id");

$num_rows = mysql_num_rows($fetch_rows);

echo $num_rows;

while($row = mysql_fetch_array($fetch_rows))

{

$row_array['file']= $row['attach_doc_path'];

$row_array['upc'] = $row['coupon_upc'];

$row_array['Cpn_type'] = $row['coupon_type'];

$start_date = $row['start_date'];

$str_start_date = strtotime($start_date);

$str_cal_start_date = strtotime($cal_st_date);

if($str_start_date >= $str_cal_start_date)

{

$arr_start_date = explode("-",$start_date);

$arr_start_date = $arr_start_date[0].",".$arr_start_date[1].",".$arr_start_date[2];

$row_array['start_date'] = $arr_start_date;

}

else

{

$arr_start_date = explode("-",$cal_st_date);

$arr_start_date = $arr_start_date[0].",".$arr_start_date[1].",".$arr_start_date[2];

$row_array['start_date'] = $arr_start_date;

}

$end_date = $row['end_date'];

$str_end_date = strtotime($end_date);

$str_cal_end_date = strtotime($cal_end_date);

if($str_end_date >= $str_cal_end_date)

{

//$arr_end_date = $cal_end_date;

$arr_end_date = explode("-",$cal_end_date);

$arr_end_date = $arr_end_date[0].",".$arr_end_date[1].",".$arr_end_date[2];

$row_array['end_date'] = $arr_end_date;

}

else

{

//$arr_end_date = $end_date;

$arr_end_date = explode("-",$end_date);

$arr_end_date = $arr_end_date[0].",".$arr_end_date[1].",".$arr_end_date[2];

$row_array['end_date'] = $arr_end_date;

}

$text = $upc . "-". $type;

array_push($gph_values,$row_array);

}

//echo json_encode($gph_values);

$fp = fopen('C:\XAMP\htdocs\graph\sampleData.json', 'w+');

fwrite($fp, json_encode($gph_values));

fclose($fp);

$string = file_get_contents("sampleData.json");

echo $string;

?>

JSON file got generated

[{"file":"welcome.txt","upc":"test","Cpn_type":"test","start_date":"2014,10,2","end_date":"2014,10,9"},

{"file":"Queries.txt","upc":"132","Cpn_type":"123","start_date":"2014,10,02","end_date":"2014,10,9"}]

In the above JSON file there is no ROWS and Columns mentioned.

Please assist in generating a proper JSON file.

Andrew Gallant

unread,

Oct 24, 2014, 8:26:09 PM10/24/14

to google-visua...@googlegroups.com

There are several examples of constructing a DataTable from PHP in this group; try searching for "php json datatable".

Incidentally, unless you have a need to store the JSON file on your server, you don't need to copy save the JSON to a file - you can just output it directly into the javascript on page load, or in response to an AJAX query.

Vamshi Krishna

unread,

Oct 27, 2014, 5:34:08 AM10/27/14

to google-visua...@googlegroups.com

I was able to generate the timeline successfully with the help of JSON.

When i generated the graph the everything was fine, except the scale on the major axis.

Currently the scale of the major axis is in terms of hours. It starts from 12AM and keeps ticking till 12AM while the dates are displayed when i hover the timeline.

Is there any possibilities of changing the scale value of graph and make it in terms of dates ??

I want in major axis to show dates only.

Below is the script.

<html>

<head>

'version':'1','packages':['timeline']}]}"></script>

google.setOnLoadCallback(drawChart);

function drawChart() {

var container = document.getElementById('example4.2');

var chart = new google.visualization.Timeline(container);

var data = new google.visualization.DataTable(<?php echo json_encode($data, JSON_NUMERIC_CHECK); ?>);

var options = {

timeline: { groupByRowLabel: false }

};

chart.draw(data, options);

}

</script>

</head>

</html>

Andrew Gallant

unread,

Oct 27, 2014, 8:21:22 PM10/27/14

to google-visua...@googlegroups.com

Sorry, but the Timeline's axis is not yet configurable.

Vamshi Krishna

unread,

Nov 5, 2014, 8:31:06 AM11/5/14

to google-visua...@googlegroups.com

Thanks Andrew, you have been great help all through.

Now it is that, my chart is generating properly.

However i feel a small problem in the chart's first column.

It is not getting displayed completely. Tried to correct the graph pixels in the <div> statement, but yet n show.

Below is the <div> statement

Graph comes as below: The first column should display value as "Undefined."

Reply all

Reply to author

Forward