Forum Replies Created

Viewing 4 posts - 1 through 4 (of 4 total)
  • in reply to: Apply dropdown filter in php with database search #25225

    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
    in reply to: Apply dropdown filter in php with database search #25215

    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>
    • This reply was modified 1 year, 9 months ago by Ganesh.
    in reply to: Charts with drop down button to filter #25186

    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 id_skill“);
    $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 id_skill, YEAR ORDER BY YEAR“);

    // 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 id_skill“);
    $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 id_skill“);

    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://canvasjs.com/assets/script/canvasjs.min.js”></script&gt;
    </body>
    </html>

    in reply to: How can I use PHP MySQL Dynamic data #25145

    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://canvasjs.com/assets/script/canvasjs.min.js”></script&gt;

    <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://canvasjs.com/assets/script/canvasjs.min.js”></script&gt;

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

Viewing 4 posts - 1 through 4 (of 4 total)