Home Forums Chart Support Apply dropdown filter in php with database search Reply To: Apply dropdown filter in php with database search

#25203

@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.

Chart with dropdown filter in php using data from database

——
Manoj Mohan
Team CanvasJS