Thanks so much! It worked now because I forgot to remove the strtotime() conversion code in getData.php file which I don’t need in my case
array_push($data_points, array("label"=> strtotime($row->skill), "y"=> $row->count)); //mistake array_push($data_points, array("label"=> ($row->skill), "y"=> $row->count)); // corrected
Thank you so much Manoj Mohan. It worked great with canvasjs_db for column chart :) But when I tried to change to pie chart with my database data, the data displayed is correct but the legend value for each of my topic is displayed as “false”. Where do I need to change/modify in order to get the correct label?
Much thanks for your help!
<script type="text/javascript"> $(document).ready(function () { var dataPoints = []; var chart = new CanvasJS.Chart("chartContainer", { title: { text: "Skills Distribution: Select Year From Dropdown", verticalAlign: "top" }, data: [{ type: "pie", //change type to bar, line, area, pie, etc startAngle: 25, toolTipContent: "<b>{label}</b>: {y}", showInLegend: "true", legendText: "{label}", indexLabelFontSize: 16, indexLabelPlacement: "inside", indexLabel: "{y}", dataPoints: dataPoints }] }); chart.render() $.getJSON("getYear.php", function(result){ $.each(result, function(i, field){ $(".dropdown").append("<option value='"+field+"'>"+field+"</option>"); }); }); $( ".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 = "Skills Distribution for 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 = "top"; } chart.render() }); }); </script>
Hi, I am looking for similar drop down filter in php but with database search. I want to filter my charts using Year-wise that will display total number of appointments in each skill for that selected year.
Here is my php code where I tried to plot two graphs, one for all years and next for selection by year.
Much thanks for your help please.
<?php $dataPoints = array(); $dataPoints2018 = array(); //Best practice is to create a separate file for handling connection to database try{ // Creating a new connection. // Replace your-hostname, your-db, your-username, your-password according to your database $link = new \PDO( 'mysql:host=localhost;dbname=project;charset=utf8mb4', //'mysql:host=localhost;dbname=canvasjs_db;charset=utf8mb4', 'root', //'root', '', //'', array( \PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, \PDO::ATTR_PERSISTENT => false ) ); // qurey for chart render for total appointments for all time topic-wise $handle = $link->prepare("SELECT COUNT(*) as count, a.id_skill, a.ap_meet_date, s.skill FROM appointment a,skills_data s WHERE a.id_skill=s.id_skill and a.ap_status = 'complete' GROUP BY <code>id_skill</code>"); $handle->execute(); $result = $handle->fetchAll(\PDO::FETCH_OBJ); foreach($result as $row){ array_push($dataPoints, array("label"=> $row->skill, "y"=> $row->count)); } $link = null; // OTHER USEFUL QUERIES // qurey for disticnt years in appointment table $year = ("SELECT DISTINCT YEAR(ap_meet_date) FROM appointment As YEAR"); // qurey for chart render for total appointments topic-wise in each year $sesult_by_year = ("SELECT a.id_skill, YEAR(a.ap_meet_date) As YEAR, s.skill,COUNT(*) as count FROM appointment a,skills_data s WHERE a.id_skill=s.id_skill and a.ap_status = 'complete' GROUP BY <code>id_skill</code>, <code>YEAR</code> ORDER BY <code>YEAR</code>"); // example query for 2017 and 2018 that can be used to print directly for 2017 and 2018 $result_2017 = ("SELECT a.id_skill, YEAR(a.ap_meet_date) As YEAR, s.skill,COUNT(*) as count FROM appointment a,skills_data s WHERE a.id_skill=s.id_skill AND a.ap_status = 'complete' AND YEAR(a.ap_meet_date) = '2017' GROUP BY <code>id_skill</code>"); $result_2018 = ("SELECT a.id_skill, YEAR(a.ap_meet_date) As YEAR, s.skill,COUNT(*) as count FROM appointment a,skills_data s WHERE a.id_skill=s.id_skill AND a.ap_status = 'complete' AND YEAR(a.ap_meet_date) = '2018' GROUP BY <code>id_skill</code>"); foreach($result as $row){ array_push($dataPoints2018, array("label"=> $row->skill, "y"=> $row->count)); } $link = null; } catch(\PDOException $ex){ print($ex->getMessage()); } ?> <!DOCTYPE HTML> <html> <head> <!-- Script to render pie chart for total count in all years --> <script> window.onload = function () { var chart = new CanvasJS.Chart("chartContainer", { animationEnabled: true, exportEnabled: true, theme: "light1", // "light1", "light2", "dark1", "dark2" title:{ text: "Skills Distribution" }, data: [{ type: "pie", //change type to bar, line, area, pie, etc startAngle: 25, toolTipContent: "<b>{label}</b>: {y}", // toolTipContent: "<b>{label}</b>: {y}%", showInLegend: "true", legendText: "{label}", indexLabelFontSize: 16, indexLabelPlacement: "inside", indexLabel: "{y}", // indexLabel: "{label} - {y}", // indexLabel: "{label} - {y}%", dataPoints: <?php echo json_encode($dataPoints, JSON_NUMERIC_CHECK); ?> }] }); chart.render(); } </script> </head> <body> <!-- Renders pie chart for total count in all years --> <div id="chartContainer" style="height: 370px; width: 900px;"></div> <!-- To render pie chart based on Dropdown filter using database --> <!-- Dropdown filter using database --> <br/> <select class="dropdown" id="dd"> <option value="" selected="selected">Select Year</option> <?php if (! empty($distinct_years)) { foreach ($distinct_years as $key => $value) { echo '<option value="' . $distinct_years[$key]['YEAR'] . '">' . $distinct_years[$key]['YEAR'] . '</option>'; } } ?> </select> <!-- To render pie chart based on Dropdown filter using database --> <script type="text/javascript"> var dataPoints = []; var chart = new CanvasJS.Chart("chartContainer2018", { animationEnabled: true, exportEnabled: true, theme: "light1", // "light1", "light2", "dark1", "dark2" }, data: [{ showInLegend: true, type: 'pie', startAngle: 25, toolTipContent: "<b>{label}</b>: {y}", showInLegend: true, legendText: "{label}", dataPoints2018: <?php echo json_encode($dataPoints2018, JSON_NUMERIC_CHECK); ?> // dataPoints: dataPoints }] }); $( ".dropdown" ).change(function() { chart.options.data[0].dataPoints2018 = []; var e = document.getElementById("dd"); var selected = e.options[e.selectedIndex].value; YEAR = jsonData[selected]; // dps = jsonData[selected]; for(var i in YEAR) { var xVal = dps[i].x; chart.options.data[0].dataPoints2018.push({x: new Date(YEAR), y: YEAR[i].y}); } chart.render(); }); </script> <div id="chartContainer2018" style="height: 370px; width: 900px;"></div> <script src="https://cdn.canvasjs.com/canvasjs.min.js"></script> </body> </html>
Hi, I am trying to implement CanvasJS in my code so that I can display my mysql data in different charts and apply filter later like Select results Year wise, Department wise
But I am getting the plain text displayed in webpage when I follow this code like this :
<!DOCTYPE html> <html> <head> </head> <body> [{"label":"Sample size calculation","y":42},{"label":"Statistical analysis","y":206},{"label":"Data management","y":25},{"label":"Research method","y":7},{"label":"Writing a manuscript","y":2},{"label":"Health technology assessment \/ Economic Evaluation","y":7},{"label":"Data Logical","y":5},{"label":"Others","y":2}]<div id="chartContainer" style="height: 380px; width: 800; margin: 0px auto;"></div> <script src="https://cdn.canvasjs.com/canvasjs.min.js"></script> <script src="jquery.js"></script> <script src="canvasjs.js"></script> <script type="text/javascript"> $(document).ready(function () { $.getJSON("getjson.php", function (result) { var chart = new CanvasJS.Chart("chartContainer", { data: [ { //type: 'pie', dataPoints: result } ] }); chart.render(); }); }); </script> </body> </html>
I have two php files like you suggested: 1. getjson.php 2. render.php
1. getjson.php
<?php header('Content-Type: application/json'); include "console/connect_db.php"; $data_points = array(); $sql = "SELECT COUNT(*) as count,a.id_skill,s.skill FROM appointment a,skills_data s WHERE a.id_skill=s.id_skill and a.ap_status ='complete' GROUP BY `id_skill`"; $result = $conn->query($sql); while($row = $result->fetch_assoc()) { $point = array("label" => $row['skill'] , "y" => $row['count']); array_push($data_points, $point); } echo json_encode($data_points, JSON_NUMERIC_CHECK); ?>
2. render.php
<!DOCTYPE html> <html> <head> </head> <body> <?php require "getjson.php"; ?> <div id="chartContainer" style="height: 380px; width: 800; margin: 0px auto;"></div> <script src="https://cdn.canvasjs.com/canvasjs.min.js"></script> <script src="jquery.js"></script> <script src="canvasjs.js"></script> <script type="text/javascript"> $(document).ready(function () { $.getJSON("getjson.php", function (result) { var chart = new CanvasJS.Chart("chartContainer", { data: [ { //type: 'pie', dataPoints: result } ] }); chart.render(); }); }); </script> </body> </html>