Reply To: Charts with drop down button to filter

[Manoj Mohan]

@ganesh,

To create a chart based on dropdown selection in PHP using data from database, you need to first create a PHP service that will provide data for a corresponding year from database. Please check the below code snippet to create such a service –

getData.php

<?php
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);

Then call the above service when an option is selected from a dropdown and render the chart based on the data received as a result from the service. Please check the below code snippet –

$( ".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, kindly take a look at this sample project for a working example.

Considering this as a duplicate of Apply dropdown filter in php with database search. Hence, closing the same.

——
Manoj Mohan
Team CanvasJS