Google Charts

MartynLearnsPHP · December 25, 2014

My wife is a Type 1 diabetic and I am trying to put a site together where she can record her blood/glucose readings and analyse the readings.

I am trying to use Google Charts to create a line graph so that she can see if their is a trend during the day when her blood sugar levels peak and trough. But my coding isn't providing any results.

Can anyone see what I am doing wrong?

<?php
$level=DB::getInstance()->query("SELECT * FROM tabbyhealth WHERE reading!=0");
foreach ($level->results() as $levelresults)
$glucose = $levelresults->reading;
$timestamp = date('d M Y h.i A',strtotime($levelresults->timestamp) +18000);
?>
<head>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
	google.load('visualization', '1.0', {'packages':['corechart']});

	google.setOnLoadCallback(drawChart);

	function drawChart() {

	var data = new google.visualization.DataTable();
	data.addColumn('string', 'Time of Reading');
	data.addColumn('number', 'Blood Glucose Reading');

	data.addRows([
		['<?php echo $timestamp; ?>', '<?php echo $glucose; ?>']
		]);

	var options = {
					title:'Blood Glucose Monitoring',
					curveType: 'function',
					legend: { position: 'bottom' }
					width:600,
					height:300
					hAxis: {
					title: 'Date/Time'
					},
					vAxis: {
					title: 'Reading'
					}
				};

	var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
	chart.draw(data, options);
	}
</script>
</head>

<body>
<div id="chart_div"></div>
</body>

I have even tried replacing

data.addRows([
        ['<?php echo $timestamp; ?>', '<?php echo $glucose; ?>']
        ]);

with

data.addRows([
        [0, 0, 0],   [1, 10, 5],  [2, 23, 15],
        [3, 17, 9],  [4, 18, 10],  [5, 9, 5],
        [6, 11, 3],  [7, 27, 19],  [8, 33, 25]
      ]);

to see if it is just a problem with reading from my database, but I am still getting nothing - by nothing I mean that no chart is appearing.

jay0316 · December 26, 2014

Hey Marty,

I had typed a larger response, but unfortunately had an issue with my browser and lost it. So, I'm going to give you an abbreviated answer. Here is a working example using your test data:

http://jsfiddle.net/jay0316/pxcozvep/1/

Your data types ("string" and "number") for your columns as well as the number of columns need to match the data you are passing in data.addRows. When you use the test data you gave as an example, you are only defining 2 columns in the js, but the there are 3 pieces of data in each array that is in the data.addRows function. You would need a third column to make that work (as you can see in the fiddle link above).

In the php coded version you are passing both the timestamp and glucose as "string" (because of the single quotes you are using around the <?php ?> tags, but the columns in the js code are defined as "string" and "number". These will need to match the data you're passing.

Also, I'd test your php $timestamp variable by printing it out to make sure you're getting what you expect.

Finally, I normally like to include the brackets with my foreach loops so it's clear where it starts and ends. There are a couple ways of doing it. One way might be creating a js variable above your google chart js code you have like so (not tested) :

var rowData = [
<?php 
$level=DB::getInstance()->query("SELECT * FROM tabbyhealth WHERE reading!=0");
foreach ($level->results() as $levelresults)
{
    $glucose = $levelresults->reading;
    $timestamp = date('d M Y h.i A',strtotime($levelresults->timestamp) +18000);
    echo "['".$timestamp."',".$glucose."],";
}	
?>
];

and then pass the js variable to data.addRows like so:

data.addRows(rowData);

Hope this helps set you in the right direction. Happy Holidays!

Edited December 26, 2014 by jay0316

MartynLearnsPHP · December 26, 2014

Hi Jay,

Thanks for your help. The jsfiddle example works a treat, so the coding is right, but I can't get it to pull in the recorded readings from the database.

What I have currently got to is that I want a Line Chart which has the date along the bottom axis and the hour/readings along the vertical axis. Using your suggestions I have currently tried variations of the following, but without any success:

Version 1:

<script type="text/javascript">
	google.load('visualization', '1.0', {'packages':['corechart']});

	google.setOnLoadCallback(drawChart);

	function drawChart() {

	var data = new google.visualization.DataTable();
	data.addColumn('number', '');
	data.addColumn('number', 'Time of Reading');
	data.addColumn('number', 'Blood Glucose Reading');

	var rowData = [
	<?php 
	$level=DB::getInstance()->query("SELECT * FROM tabbyhealth WHERE reading!=0");
	foreach ($level->results() as $levelresults)
	{
		$glucose = $levelresults->reading;
		$date = date('d M',strtotime($levelresults->timestamp) +18000);
		$timestamp = date('H',strtotime($levelresults->timestamp) +18000);
		echo "['".$timestamp."',".$glucose."],";
	}	
	?>
	];
	
	var rowData = [
	<?php
		$level=DB::getInstance()->query("SELECT * FROM tabbyhealth WHERE reading!=0");
		foreach ($level->results() as $levelresults)
		{
			$date = date('d M',strtotime($levelresults->timestamp) +18000);
			$hour = date('H',strtotime($levelresults->timestamp) +18000);
			$glucose = $levelresults->reading;
			{
			echo "['".$date."','".$hour."',".$glucose."],";
			}
		}
	?>
	];
	
	data.addRows(rowData);

	var options = {
					title:'Blood Glucose Monitoring',
					curveType: 'function',
					legend: { position: 'bottom' },
					width:600,
					height:300,
					hAxis: {
					title: 'Date'
					},
					vAxis: {
					title: 'Reading and Time'
					}
				};

	var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
	chart.draw(data, options);
	}
</script>

Version 2:

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

	google.setOnLoadCallback(drawChart);

	function drawChart() {

	var data = new google.visualization.DataTable();
	data.addColumn('number', '');
	data.addColumn('number', 'Time of Reading');
	data.addColumn('number', 'Blood Glucose Reading');

	var rowData = [
	<?php 
	$level=DB::getInstance()->query("SELECT * FROM tabbyhealth WHERE reading!=0");
	foreach ($level->results() as $levelresults)
	{
		$glucose = $levelresults->reading;
		$date = date('d M',strtotime($levelresults->timestamp) +18000);
		$timestamp = date('H',strtotime($levelresults->timestamp) +18000);
		echo "['".$timestamp."',".$glucose."],";
	}	
	?>
	];
	
	var rowData = [
	
	];
	
	data.addRows([
		<?php
		$level=DB::getInstance()->query("SELECT * FROM tabbyhealth WHERE reading!=0");
		foreach ($level->results() as $levelresults)
		{
			$date = date('d M',strtotime($levelresults->timestamp) +18000);
			$hour = date('H',strtotime($levelresults->timestamp) +18000);
			$glucose = $levelresults->reading;
			{
			echo "['".$date."','".$hour."',".$glucose."],";
			}
		}
		?>
	]);

	var options = {
					title:'Blood Glucose Monitoring',
					curveType: 'function',
					legend: { position: 'bottom' },
					width:600,
					height:300,
					hAxis: {
					title: 'Date'
					},
					vAxis: {
					title: 'Reading and Time'
					}
				};

	var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
	chart.draw(data, options);
	}

MartynLearnsPHP · December 26, 2014

Please ignore the previous post. It is incorrect but, for some reason, I can't edit or delete it. Sorry.

Hi Jay,

Thanks for your help. The jsfiddle example works a treat, so the coding is right, but I can't get it to pull in the recorded readings from the database.

I'm making progress. I think my problem at the momentis trying to get a date on the bottom axis. If I use the database id, then it is working, it I use timestamp then it isn't.

My working code using the table id is:

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

    google.setOnLoadCallback(drawChart);

    function drawChart() {

    var data = new google.visualization.DataTable();
    data.addColumn('number', '');
    data.addColumn('number', 'Time of Reading');
    data.addColumn('number', 'Blood Glucose Reading');

    data.addRows([
        <?php
        $level=DB::getInstance()->query("SELECT * FROM tabbyhealth WHERE reading!=0");
        foreach ($level->results() as $levelresults)
        {
            echo "[";
            echo $levelresults->id;
            echo ",";
            echo date('H',strtotime($levelresults->timestamp) +18000);
            echo ",";
            echo $levelresults->reading;
            echo "],";
        }
        ?>
    ]);

    var options = {
                    title:'Blood Glucose Monitoring',
                    curveType: 'function',
                    legend: { position: 'bottom' },
                    width:600,
                    height:300,
                    hAxis: {
                    title: 'Date'
                    },
                    vAxis: {
                    title: 'Reading and Time'
                    }
                };

    var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
    chart.draw(data, options);
    }

My non-working table using timestamp is:

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

    google.setOnLoadCallback(drawChart);

    function drawChart() {

    var data = new google.visualization.DataTable();
    data.addColumn('number', '');
    data.addColumn('number', 'Time of Reading');
    data.addColumn('number', 'Blood Glucose Reading');

    data.addRows([
        <?php
        $level=DB::getInstance()->query("SELECT * FROM tabbyhealth WHERE reading!=0");
        foreach ($level->results() as $levelresults)
        {
            echo "[";
            echo date('d M',strtotime($levelresults->timestamp) +18000);
            echo ",";
            echo date('H',strtotime($levelresults->timestamp) +18000);
            echo ",";
            echo $levelresults->reading;
            echo "],";
        }
        ?>
    ]);

    var options = {
                    title:'Blood Glucose Monitoring',
                    curveType: 'function',
                    legend: { position: 'bottom' },
                    width:600,
                    height:300,
                    hAxis: {
                    title: 'Date'
                    },
                    vAxis: {
                    title: 'Reading and Time'
                    }
                };

    var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
    chart.draw(data, options);
    }

Is there anyway to get the horizontal axis to show dates?

Edited December 26, 2014 by MartynLearnsPHP

jay0316 · December 26, 2014

Hey Marty,

The first example works because you're passing numbers for all your data and all your columns have 'number' types assigned to them.

The second example doesn't work because this date line is a string:

echo date('d M',strtotime($levelresults->timestamp) +18000);  // output example: 26 Dec

Since it's a string, you'll need to add quotes around it:

echo "'".date('d M',strtotime($levelresults->timestamp) +18000)."'";

Then you'll need to change the column to 'string'. Give this a whirl:

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

    google.setOnLoadCallback(drawChart);

    function drawChart() {

    var data = new google.visualization.DataTable();
    data.addColumn('string', '');
    data.addColumn('number', 'Time of Reading');
    data.addColumn('number', 'Blood Glucose Reading');

    data.addRows([
        <?php
        $level=DB::getInstance()->query("SELECT * FROM tabbyhealth WHERE reading!=0");
        foreach ($level->results() as $levelresults)
        {
            echo "[";
            echo "'".date('d M',strtotime($levelresults->timestamp) +18000)."'";
            echo ",";
            echo date('H',strtotime($levelresults->timestamp) +18000);
            echo ",";
            echo $levelresults->reading;
            echo "],";
        }
        ?>
    ]);

    var options = {
                    title:'Blood Glucose Monitoring',
                    curveType: 'function',
                    legend: { position: 'bottom' },
                    width:600,
                    height:300,
                    hAxis: {
                    title: 'Date'
                    },
                    vAxis: {
                    title: 'Reading and Time'
                    }
                };

    var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
    chart.draw(data, options);
    }

Here is a fiddle:

http://jsfiddle.net/jay0316/pxcozvep/4/

Edited December 26, 2014 by jay0316

MartynLearnsPHP · December 26, 2014

Oh that works a treat. Thank you so much, Jay.

jay0316 · December 26, 2014

No problem Marty. Glad I could help!

Sign In

Google Charts

Recommended Posts

MartynLearnsPHP

Link to comment

Share on other sites

jay0316

Link to comment

Share on other sites

MartynLearnsPHP

Link to comment

Share on other sites

MartynLearnsPHP

Link to comment

Share on other sites

jay0316

Link to comment

Share on other sites

MartynLearnsPHP

Link to comment

Share on other sites

jay0316

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information