Reply To: Apply dropdown filter in php with database search

[Manoj Mohan]

@ganesh,

For creating a chart based on dropdown filter in php using data from database, you need to first create a php service getData.php which will provide data for any given year from database. Please take a below code snippet for getData.php service.

header('Content-Type: application/json');

$link = new \PDO(   'mysql:host=localhost;dbname=canvasjs_db;charset=utf8mb4', //'mysql:host=localhost;dbname=canvasjs_db;charset=utf8mb4',
                    'root', //'root',
                    '', //'',
                    array(
                        \PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                        \PDO::ATTR_PERSISTENT => false
                    )
                );				
$handle = $link->prepare("SELECT * FROM datapoints where YEAR(x) = :year order by x");
$handle->bindParam(":year",$_GET["year"], PDO::PARAM_INT);
$handle->execute(); 
$result = $handle->fetchAll(\PDO::FETCH_OBJ);
$data_points = array();
foreach($result as $row){
    // x column is in timestamp, convert unixtimestamp(seconds) to javascript timestamp(milliseconds) by multipying the x value by 1000 Please refer https://stackoverflow.com/a/15593812 for more information
    array_push($data_points, array("x"=> strtotime($row->x)*1000, "y"=> $row->y));
}
$link = null;

echo json_encode($data_points, JSON_NUMERIC_CHECK);

You can call getData.php service upon changing the year through dropdown and render the chart with data received from the service. Please take a look the below code snippet for the same.

$( ".dropdown" ).change(function() {
	chart.options.data[0].dataPoints = [];
	var e = document.getElementById("dd");
	var selectedYear = e.options[e.selectedIndex].value;
	if( !isNaN(Number(selectedYear)) ) {
		chart.options.title.text = "Year: " + selectedYear;
		chart.options.title.verticalAlign = "top";
		var data = { "year" : selectedYear }
		$.getJSON("getData.php", data, function(result){
			chart.options.data[0].dataPoints = result;
			chart.render();
		})
	} else {
		chart.options.title.text = "Select Year From Dropdown";
		chart.options.title.verticalAlign = "center";
	}
	chart.render()
});

Also, Please take a look at this sample project for an example on creating chart based on dropdown filter in php using data from database.

——
Manoj Mohan
Team CanvasJS