Display Data from mysql database in Chart

Question

I'm using AmCharts to create a pie chart.
I'm trying to assign data from my mysql database to the variable chartData that has the fields country and liters. How can I assign my mysql data to chartdata?

 <script>
    var chart;
    var legend;



    var chartData = [{
    "country": "Czech Republic",
    "litres": 156.9
    },
    {
    "country": "Ireland",
    "litres": 131.1
    },
    {
    "country": "Germany",
    "litres": 115.8
    },
    {
    "country": "Australia",
    "litres": 109.9
    },
    {
    "country": "Austria",
    "litres": 108.3
    },
    {
    "country": "UK",
    "litres": 65
    },
    {
    "country": "Belgium",
    "litres": 50
    }
    ];

     AmCharts.ready(function () {
     // PIE CHART
    chart = new AmCharts.AmPieChart();
    chart.dataProvider = chartData;
    chart.titleField = "country";
    chart.valueField = "litres";



  // LEGEND
   legend = new AmCharts.AmLegend();
        legend.align = "center";
        legend.markerType = "circle";
        chart.balloonText = "[[title]]<br><span style='font-size:14px'><b>[[value]]</b> ([[percents]]%)</span>";
        chart.addLegend(legend);

        // WRITE
        chart.write("chartdiv");
    });
// changes label position (labelRadius)
function setLabelPosition() {

if (document.getElementById("rb1").checked) {


 chart.labelRadius = 30;


 chart.labelText = "[[title]]: [[value]]";


     } else {


 chart.labelRadius = -30;

    chart.labelText = "[[percents]]%";
    }
    chart.validateNow();
}


 // makes chart 2D/3D


function set3D() {
   if (document.getElementById("rb3").checked) {
        chart.depth3D = 10;
        chart.angle = 10;
    } else {
        chart.depth3D = 0;
        chart.angle = 0;
    }
    chart.validateNow();
}

// changes switch of the legend (x or v)
  function setSwitch() {
    if (document.getElementById("rb5").checked) {
    legend.switchType = "x";
 } else {
   legend.switchType = "v";
    }
 legend.validateNow();

}



     </script>

http://stackoverflow.com/questions/3020751/can-javascript-connect-with-mysql — Kalle, Jun 30 '15 at 07:41

score 0 · Answer 1 · answered Jun 30 '15 at 08:47

You cannot directly access your mysql database from javascript. You will have to ask your server for some data. Typicaly, you will want to get JSON formated data (using ajax) :

Javascript (using .getJSON()):

$.getJSON('/get-my-data.php', function(json) {
    var chart;
    var legend;

    var chartData = json;

    AmCharts.ready(function () {
        chart = new AmCharts.AmPieChart();
        chart.dataProvider = chartData;

        // code ...
    });
});

get-my-data.php (using mysqli) :

$mysqli = new mysqli("localhost", "my_user", "my_password", "my_database");

$stats = array();

$query = "
    SELECT `country`, `litres`
    FROM `mytable`
";
$statement = $mysqli->prepare($query);

$result = $statement->get_result();
while ($data = $result->fetch_assoc())
{
    $stats[] = $data;
}

echo json_encode($stats);

score 0 · Answer 2 · answered Jun 30 '15 at 11:25

If you don't want or can't use jQuery, there's a built-in solution using amCharts' own Data Loader plugin. To use it simply include plugins/dataloader/dataloader.min.js file from the same directory you include the rest of the amCharts js files, then add the following directive to your chart config:

AmCharts.makeChart("chartdiv", {
  "type": "pie",
  "dataLoader": {
    "url": "data.php"
  },
  // the reset of your chart config
  // ..
});

On the server-side you can use PHP function json_encode() to format your data as JSON. I.e.:

<?php
// load your data
// ...

// format as JSON
echo json_encode( $data );
?>

Here's more info about the plugin and how to get your data from MySQL server:

http://www.amcharts.com/tutorials/using-data-loader-to-connect-charts-to-mysql-data-base/

Display Data from mysql database in Chart

2 Answers2