Search data in MYSQL and show in Highcharts chart

Asked

Viewed 2,283 times

2

Person!

I’m starting in the development area and I have at the moment the need to work with the Highcharts javascript graphics library. However, I have no knowledge of how to search this data in MYSQL and present in the graph, on my machine I have the database "Sakila" that I will use to generate the data in the graph for learning.

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

Below is the Javascritp code of the graph 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>
   <script src="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>

javascript jquery

2 answers

1

flow, html+js(defined graph structure), ajax request to a php file, php select file in the echo json_encode(data) database, back to the js where the graphs are the 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 structure will be built in json...

1


The example below generates a chart of sectors from a PHP back-end, receiving a parameter from a input text type, 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 the PDO in addition to the clause where of SQL :)

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);
});

Necessary plugins:

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.2/jquery.min.js"></script>

<script src="http://code.highcharts.com/highcharts.js"></script>

  • Humn?! I’m not Daniel!

  • Eduardo Almeida! 1. I create a table in MYSQL called "first chart" CREATE TABLE primeiro_grafico ( name varchar(50) DEFAULT NULL, val decimal(10,2) DEFAULT NULL ) ENGINE=Innodb DEFAULT CHARSET=latin1; /*Table data primeiro_grafico */ Insert int for table data.

    1. Recover data as JSON using PHP - Note: Save php with the "Index.php" separated code called (data;php). <? php //takes the parameter from Javascript $param = $_GET['param']; //opens PDO { $con = new PDO("mysql:host=Myuser;dbname=Myconnection", $user, $password); $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    1. Implement the required plugins in index.php <!DOCTYPE HTML> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>Highcharts Pie Chart</title> <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js"></script> <script type="text/javascript"> - Along with Javascript 'use Strict'; $(Function() ! - I reference the.php date with the code: $.getJSON("data.php", Function(json) { options.series[0]. data = json; Chart = new Highcharts.Chart(options);

  • Eduardo Almeida! Correct me if I’m wrong, please.

Browser other questions tagged javascript jquery

You are not signed in. Login or sign up in order to post.