Search for data in MYSQL and display in the Highcharts chart

Navigation
2

Person!

I'm starting in the development area and I have at the moment the need to work with the javascript library Highcharts. However, I am not aware of how to search for this data in MYSQL and present it on the chart, on my machine I have the "Sakila" database that I will use to generate the data in the graph for the purpose of learning.

I come here to seek the necessary knowledge and good practices for using Web development.

Below is the Javascritp code of the chart to be implemented to fetch the data in "Sakila from MYSQL". 

<html>
<head>
<title>Highcharts Tutorial</title>
   <script src="http://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script><scriptsrc="http://code.highcharts.com/highcharts.js"></script> 
</head>
<body>
<div id="container" style="width: 550px; height: 400px; margin: 0 auto"></div>
<script language="JavaScript">
$(document).ready(function() {
   var title = {
      text: 'Monthly Average Temperature'   
   };
   var subtitle = {
      text: 'Source: WorldClimate.com'
   };
   var xAxis = {
      categories: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
         'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
   };
   var yAxis = {
      title: {
         text: 'Temperature (\xB0C)'
      },
      plotLines: [{
         value: 0,
         width: 1,
         color: '#808080'
      }]
   };   

   var tooltip = {
      valueSuffix: '\xB0C'
   }

   var legend = {
      layout: 'vertical',
      align: 'right',
      verticalAlign: 'middle',
      borderWidth: 0
   };

   var series =  [
      {
         name: 'Tokyo',
         data: [7.0, 6.9, 9.5, 14.5, 18.2, 21.5, 25.2,
            26.5, 23.3, 18.3, 13.9, 9.6]
      }, 
      {
         name: 'New York',
         data: [-0.2, 0.8, 5.7, 11.3, 17.0, 22.0, 24.8,
            24.1, 20.1, 14.1, 8.6, 2.5]
      }, 
      {
         name: 'Berlin',
         data: [-0.9, 0.6, 3.5, 8.4, 13.5, 17.0, 18.6,
            17.9, 14.3, 9.0, 3.9, 1.0]
      }, 
      {
         name: 'London',
         data: [3.9, 4.2, 5.7, 8.5, 11.9, 15.2, 17.0, 
            16.6, 14.2, 10.3, 6.6, 4.8]
      }
   ];

   var json = {};

   json.title = title;
   json.subtitle = subtitle;
   json.xAxis = xAxis;
   json.yAxis = yAxis;
   json.tooltip = tooltip;
   json.legend = legend;
   json.series = series;

   $('#container').highcharts(json);
});
</script>
</body>
</html>
    
asked by anonymous 27.05.2016 / 02:31

2 answers

1

The example below generates a sector chart from a back -end PHP, getting a parameter of a input of type text, which is passed to the query for you to filter in the database if you want, if not necessary just remove everything that is related to the form and also the bindParam of PDO in addition to%% SQL clause:)

PHP: 

<?php

//recebe o parametro vindo do JavaScript

$param = $_GET['param'];

//abre conexao PDO

try {
    $con = new PDO("mysql:host=MeuServidor;dbname=MeuBancoDeDados", $usuario, $senha);
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (Exception $ex) {
    print_r('Error: ' . $ex->getMessage());
}

$query = "select Coluna_1, sum(valor) as valor "
        . "from MinhaTabela "
        . "where Parametro = :param";

$prepare = $con->prepare($query);

try{
    $prepare->bindParam(':param', $param, PDO::PARAM_STR);
    $prepare->execute;

    $resultado = $prepare->fetchAll(PDO::FETCH_ASSOC);

    //retorna o JSON
    echo json_encode($resultado, JSON_NUMERIC_CHECK);
} catch (Exception $ex) {
    print_r('Error: ' . $ex->getMessage());
}

HTML markup: 

<input id="meu-input" placeholder="Digite um filtro...">

<div id="grafico-setor"></div>

JavaScript: 

'use strict';
$(function () {
    var meu_filtro = $('#meu-input'),
            param = $('#meu-input').val();

    //atualiza a variável passada como parâmetro e a função toda vez que um caracter é adicionado ou removido do filtro
    meu_filtro.on('keyup paste change focus blur keydown', function () {
        param = $('#meu-input').val();
        hc_grafico_setor(param);
    });
  
    //define a função que gera o gráfico
    function hc_grafico_setor(param) {
        $.getJSON('link/para/o/json.php', {
            //parametros
            param: param
        }, function (data) {
            //cria um objeto Highcharts chamado de 'chart'
            var chart = new Highcharts.Chart({
                chart: {
                    renderTo: "grafico-setor", //#id da div que vai renderizar o gráfico
                    type: 'pie', // tipo do grafico. Pode ser setado em series também
                  
                    //para o grafico ocupar todo o espaço. Pode ser removido
                    marginTop: 0,
                    marginBottom: 0,
                    marginRight: 0,
                    marginLeft: 0
                },
                credits: {
                    enabled: false,  //Setar para true se quiser mostrar os créditos
                    text: 'Quem fez o gráfico...'
                },
                title: {
                    text: "Títuo do meu gráfico",
                    verticalAlign: 'top', //alinha o titulo verticalmente
                    floating: true
                },
                tooltip: {
                    useHTML: true, //formatar a tooltip usando elementos HTML
                  
                    //o Highcharts tem uma função própria para formatar numeros: Highcharts.numberFormat()
                    formatter: function () {
                        return '<div style="text-align: center"><b>' + this.point.name + '</b><br>' + Highcharts.numberFormat(this.percentage, 1) + ' %<br />' +
                                'R$ ' + Highcharts.numberFormat(this.point.y) +
                                '<br />Total: R$ ' + Highcharts.numberFormat(this.point.total, 2) + '</div>';
                    }
                },
                legend: {
                    enabled: false //desabilita a legenda. Setar para true se quiser habilitar a legenda
                },
                plotOptions: {
                    pie: {
                        innerSize: 0 //se quiser criar um donut, aumentar esse valor
                    }
                },
                series: [{
                        data: data, //dados vindos do JSON
                        size: '100%'
                    }]
            });
        });
    };
    //inicializa a função que gera o grafico
    hc_grafico_setor(param);
});

Required plugins: 

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.2/jquery.min.js"></script><scriptsrc="http://code.highcharts.com/highcharts.js"></script>
    
27.05.2016 / 03:02
1

flow, html + js (defined graphics structure), ajax request to a php file, select php file in echo bank json_encode (data), back to js where the graphics is as object structure you implement the data. OBS: 

var title = {
      text: 'Monthly Average Temperature'   
   };
   var subtitle = {
      text: 'Source: WorldClimate.com'
   };
   var xAxis = {
      categories: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
         'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
   };
   var yAxis = {
      title: {
         text: 'Temperature (\xB0C)'
      },
      plotLines: [{
         value: 0,
         width: 1,
         color: '#808080'
      }]
   };

This will be built in json ...

    
27.05.2016 / 15:49
work with value in google charts Summing consecutive integers in a list - Python