Php mysql canvas js populating graph

I have created the following code to test canvas.js graphing using Php and MySql.
I followed the various samples from the download but cannot seem to make the graph work.
It produces a blank screen. I have a sample of the data displayed before the graph to verify that it is not a problem in the Php / MySql code.


0       2301       2020-01-02       18
1       2302       2020-01-05       13
2       2303       2020-01-09       4
3       2304       2020-01-12       2
4       2305       2020-01-16       18
5       2306       2020-01-19       10
6       2307       2020-01-23       5
7       2308       2020-01-26       1
8       2309       2020-01-30       18
9       2310       2020-02-02       12
10       2311       2020-02-06       6
11       2312       2020-02-09       1
12       2313       2020-02-13       19
13       2314       2020-02-16       18
14       2315       2020-02-20       1

I am only using the date and the last column of numbers for the test.
I tried to use multiple datapoints but that did work either.

<?php
$data_points = array();
$data_points_1 = array();
$data_points_2 = array();
$point = array();

// populate array with columns
while ($row2 = mysqli_fetch_array($result2, MYSQL_BOTH)) {
	
	if ($row2["draw_id"] > 2300) { 	// only get a small sampling
		
		//$dataPoints = array($row2["date"][$i], $row2["num06"][$i]);
		$dataPoints = array( $row2["date"], $row2["num06"] );
		//$dataPoints_1 = array($row2["date"]);
		//$dataPoints_2 = array($row2["num06"]);
		//$point = array( "x" => $row2["date"], "y" => $row2["num06"] );
		//array_push($dataPoints, $point);
		
		$i++;
	}
} // End While

mysqli_free_result($result2);
mysqli_close($link2);
?>

<!DOCTYPE HTML>
<html>
<head>
<title></title>

<script src="jquery-1.11.1.min.js"></script>
<script src="canvasjs.min.js"></script>

<style>
#chartContainer {
	width: 100%;
	height: auto;
	/*height: 500px; width: 100%;*/
}

</style>

<script>
window.onload = function () {

var chart = new CanvasJS.Chart("chartContainer", {
	
	var chart = new CanvasJS.Chart("chartContainer", {
	animationEnabled: true,
	zoomEnabled: true,
	//exportEnabled: true,
	theme: "light1", // "light1", "light2", "dark1", "dark2"
	title:{
		text: "PHP Column Chart from Database"
	},
	axisX: {	//Try Changing to MMMM
		interval: 1,
		valueFormatString: "DD MMM YY",
		titleFontColor: "#4F81BC", 
		lineColor: "#4F81BC", 
		labelFontColor: "#4F81BC", 
		tickColor: "#4F81BC"
	},
	axisY: { 
		interval: 1,
		titleFontColor: "#4F81BC", 
		lineColor: "#4F81BC", 
		labelFontColor: "#4F81BC", 
		tickColor: "#4F81BC"
	}, 	
	data: [
	{
		type: "bar", //change type to bar, line, area, pie, etc
		showInLegend: true,
		name: "date",
		color: "gold",
		dataPoints: <?php echo json_encode($dataPoints, JSON_NUMERIC_CHECK); ?>
	}]
	});
	
	chart.render();
	
} 
</script>

</head>
<body>
<h3>Draw a graph representing a small sampling</h3>
<div id="chartContainer"></div>

</body>
</html>

Can someone point out what I might be doing wrong?

Thanks

You get that the above will overwrite $dataPoints every time the loop runs, so by the time you call it in your JavaScript, it will only have the single date and number?

Hello droopsnoot,
Ahhh, no I didn’t understand that.
I don’t really know how canvas.js works but am experimenting with it.

So what you are saying is that I should only have these 3 lines to populate:
$result2->data_seek(0);
$row2 = mysqli_fetch_array($result2, MYSQL_BOTH);
$dataPoints = array( $row2[“date”], $row2[“num06”] );

Just tried that but the graph still didn’t display.

What else could it be?

No, what I was saying is that you’re only sending one value into your $dataPoints variable, which will be whatever the last value in the foreach() loop. I suspect you were trying to build an array so that you have more than one point in the graph.

Well after playing around with the script, I fixed an error and got it to work.
Here is the corrected code short version, I hope it helps others:

<?php
	//-----------------------------------------------------------------------
	// php area  -  populate array
	//-----------------------------------------------------------------------		
		$dataPoints = array();
		// populate jtickets variable to compare columns 1 to 5
		while ($row2 = mysqli_fetch_array($result2, MYSQL_BOTH)) { // joker
			
			if ($row2["id"] > 2300) { 	// only get a small sampling
			
				$point = array( "label" => $row2["date"], "y" => $row2["num06"] );
				array_push($dataPoints, $point);
				
				$i++;
			}
		} // End While
		
		echo json_encode($dataPoints, JSON_NUMERIC_CHECK);
		mysqli_free_result($result2);
		mysqli_close($link2);
?>
	<!---------------------------------------------------------------------
			html area  -  begin chart
	----------------------------------------------------------------------->
<script>
	window.onload = function () {		// canvas js javascript processing

	var chart = new CanvasJS.Chart("chartContainer", {// canvas js create chart object
		
		animationEnabled: true,
		exportEnabled: true,
		//zoomEnabled: true,
		theme: "light1", // "light1", "light2", "dark1", "dark2"
		title: {
			text: "PHP Column Chart from Database"
		},
		subtitles: [{
			text: "Click on Legends to Enable/Disable Data Series"
		}],
		data: [{
			type: "line", //change type to bar, line, area, pie, etc
			dataPoints: <?php echo json_encode($dataPoints, JSON_NUMERIC_CHECK); ?>
		}]
		
	}); // new CanvasJS.Chart end
	
	chart.render();
	
	} // window.onload end
</script>

It’s a bit difficult to read your code (surround it with three backticks, or use the formatting button in the editor to make it clearer), but what is the $i variable used for? If it’s just so you can tell something how many data points there are, you could just use the count() function for that.

Also you could simplify it a bit - instead of:

$point = array( "label" => $row2["date"], "y" => $row2["num06"] ); 
array_push($dataPoints, $point);

you could use a single line:

$dataPoints[] = array("label" => $row2["date"], "y" => $row2["num06"] ); 

As you see, it’s very close to your first code, but those square-brackets make the array add to the end of the array, rather than overwrite the variable.

There’s a shorter way of doing it, but I can’t remember the syntax. You can do away with the array() keyword though.