Error showing data in Highstock chart

Asked

Viewed 60 times

1

I have a chart Highstock in which I create a CSV coming from my database to then show this data on the chart, but it turns out that the graph only recognizes the CSV if after the CSV is created and when I open the CSV. In addition, it also happens when I return to guard as CSV.

The script PHP that I have to create the CSV is this:

   <?php
    // mysql database connection details
    $host = "localhost";
    $username = "user";
    $password = "pass";
    $dbname = "basedados";

    // open connection to mysql database
    $connection = mysqli_connect($host, $username, $password, $dbname) or die("Connection Error " . mysqli_error($connection));

    // fetch mysql table rows
    $sql = "SELECT `tempo`, `s1`, `s2`, `s3` FROM `tabela`";
    $result = mysqli_query($connection, $sql) or die("Selection Error " . mysqli_error($connection));
    $f = "perfiskwh.csv";
    $fp = fopen('perfiskwh.csv', 'w+');


    while($row = mysqli_fetch_assoc($result))
    {
        fputcsv($fp,$row);
    }
    // read into array
    $arr = file($f);

    // edit first line
    $arr[0] = 'Data,KWh1,KWh2,KWh3,'. "\n";

    // write back to file
    file_put_contents($f, implode($arr));
    fclose($fp);
    //close the db connection
    mysqli_close($connection);

?>

As for the chart I have this code so far:

$.get('dados/perfiskwh.csv', function (csvFile) {
      var data = parseCSVData(csvFile);
      var highchartsOptions = Highcharts.setOptions({
        lang: {
          loading: 'Aguarde...',
          months: ['Janeiro', 'Fevereiro', 'Março', 'Abril', 'Maio', 'Junho', 'Julho', 'Agosto', 'Setembro', 'Outubro', 'Novembro', 'Dezembro'],
          weekdays: ['Domingo', 'Segunda-feira', 'Terça-feira', 'Quarta-feira', 'Quinta-feira', 'Sexta-feira', 'Sábado'],
          shortMonths: ['Jan', 'Fev', 'Mar', 'Abr', 'Mai', 'Jun', 'Jul', 'Ago', 'Set', 'Out', 'Nov', 'Dez'],
          exportButtonTitle: "Exportar",
          printButtonTitle: "Imprimir",
          rangeSelectorFrom: "De",
          rangeSelectorTo: "Até",
          rangeSelectorZoom: "Periodo",
          downloadPNG: 'Descarregar gráfico como imagem PNG',
          downloadJPEG: 'Descarregar gráfico como imagem JPEG',
          downloadSVG: 'Descarregar gráfico como imagem SVG',
          downloadPDF: 'Salvar em documento PDF',
          contextButtonTitle: 'Opções',
          noData: 'Sem dados para mostrar',
          printChart: 'Imprimir gráfico',
          numericSymbols: null,
          // resetZoom: "Reset",
          // resetZoomTitle: "Reset,
          thousandsSep: ".",
          decimalPoint: ','
          }
        }
      );
      $('#container').highcharts('StockChart', {
        navigation: {
          buttonOptions: {
          enabled: true,
          }
        },
        colors:[
     '#993333',
     '#404040',
     '#003366',
     '#006600',
     '#CC3300'
   ],
        credits: {
          enabled: false
        },
        rangeSelector : {
          selected: 1,
          allButtonsEnabled: true,
          buttons: [{
            type: 'day',
            count: 1,
            text: '1 dia'
          }, {
            type: 'week',
            count: 1,
            text: '1 Semana'
          }, {
            type: 'all',
            text: 'Tudo'
          }],
          buttonTheme: {
            width: 75,
            style: {
             color: '#333',
             fontWeight: 'bold',
             fontSize: '14px',
             fontFamily: 'Trebuchet MS, Verdana, sans-serif'

          },
          },

        },
        chart: {
          renderTo: 'container',
          backgroundColor: {
         linearGradient: [500, 400, 250, 0],
         stops: [
             [0, '#F0F7FF'],
             [1, '#F0F7FF']
         ]
     },
     style: {
      color: '#333',
      fontWeight: 'bold',
      fontSize: '14px',
      fontFamily: 'Trebuchet MS, Verdana, sans-serif'

   },
          type: 'spline'
        },

        title: {
          text: 'Perfis de Consumos',
          style: {
           color: '#333',
           fontWeight: 'bold',
           fontSize: '20px',
           fontFamily: 'Trebuchet MS, Verdana, sans-serif'

        }
        },
        xAxis: {
          labels: {
            style: {
              color: 'black',
              fontWeight: 'bold',
              fontSize: '12px',
              fontFamily: 'Trebuchet MS, Verdana, sans-serif'
            }
        },
          type: 'datetime',
          tickInterval: 3600 * 1000,
          categories: ['Mar','Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec', 'Jan', 'Feb']

        },
        tooltip: {
          valueDecimals: 1,
          valueSuffix: ' KWh'
      },
        yAxis: {
          labels: {
            style: {
              color: 'black',
              fontWeight: 'bold',
              fontSize: '12px',
              fontFamily: 'Trebuchet MS, Verdana, sans-serif'
            }
        },
          opposite: false,
          title: {
            text: 'Consumos da Energia [KWh]',
            style: {
             color: '#333',
             fontWeight: 'bold',
             fontSize: '16px',
             fontFamily: 'Trebuchet MS, Verdana, sans-serif'
          }
          }
        },
        navigator:{
          xAxis: {
            style: {
             color: '#333',
             fontWeight: 'bold',
             fontSize: '16px',
             fontFamily: 'Trebuchet MS, Verdana, sans-serif'
          }
        },
          enabled: true,
          handles: {
              backgroundColor: 'Black',
              borderColor: 'White'
          },
            series: {
              color: '#0000FF',
              lineWidth: 1
          },
          outlineColor: 'black',
          outlineWidth: 1,
        },
        series: [{
          name:'Distribuidor',
          data: data[0],
        }, {
          name: 'Edificios',
          data: data[1]
        }, {
          name: 'Avac',
          data: data[2]
        }]
      });
    });

    function parseCSVData(csvFile) {
      //Array para armazenar dados do Gráfico
      var Geral= [];
      var Distribuidor= [];
      var AVAC= [];

      var Data_inicio = "January 1, 2014";
      var Data_hoje = new Date();

      //Separar os dados do CSV em linhas
      var lines= csvFile.split("\n");

      //Verifica todas as linhas do CSV
      $.each(lines, function (lineNumber, line){
        if(lineNumber !=0){ //Salta a linha do cabeçalho
          var fields = line.split(",");
          if(fields.length==4 && Date.parse(fields[0]) >= Date.parse(Data_inicio) && Date.parse(fields[0]) <= Date.parse(Data_hoje)) {//Salta as linhas invalidas e começa na data especificada até ao presente dia
            var timestamp = Date.parse(fields[0]);
            var data1 = parseInt(fields[1]);
            var data2 = parseInt(fields[2]);
            var data3 = parseInt(fields[3]);

            Geral.push([timestamp, data1]);
            Distribuidor.push([timestamp, data2]);
            AVAC.push([timestamp, data3]);

          }
        }
      });
      return [Geral,Distribuidor,AVAC];
    }

And the CSV output is this:

Apr 28 2016 11:00,219,23,221
Apr 28 2016 11:15,221,28,222
Apr 28 2016 11:30,220,9,222
Apr 28 2016 11:45,221,366,223
Apr 28 2016 12:00,221,355,223
Apr 28 2016 12:15,221,381,222
Apr 28 2016 12:30,221,395,223
Apr 28 2016 12:45,222,115,223
Apr 28 2016 13:00,221,5,222
Apr 28 2016 13:15,222,21,223
Apr 28 2016 13:30,221,10,223
Apr 28 2016 13:45,222,5,224
Apr 28 2016 14:00,222,21,223
Apr 28 2016 14:15,222,27,223
php csv highcharts

  • Why not use Json? I don’t know about csv anymore Highcharts works great with Json.

  • I’ve used json but Xaxis never looked good, so since I already had this initial code for CSV I created a script to create Csvs automatically, but only when I open and wait again and it works

  • You can add to the question the output of this csv?

  • Yes I will put

  • Well, that’s the value of csvFile or of data? Already of a console.log(data) after Parse to see what comes out? See the result in F12 --> Console.

  • This is the value that leaves the database and php puts in CSV

  • Can you tell me what comes out in console.log(data)?

  • I don’t know how to use that command

  • after that line var data = parseCSVData(csvFile); you place console.log(date). Then you press F12 and go to the console tab. It will show you how the date variable is.

  • When I create only the CSV says I have no values in the ARRAY, but if I open the csv and save again values already appear in the ARRAY

Show 5 more comments

1 answer

0

The solution is in the script to create CSV to replace " by a space because when creating CSV the script included at the beginning " removing that the chart works perfectly I leave the code below:

<?php
   // mysql database connection details
   $host = "localhost";
   $username = "user";
   $password = "pass";
   $dbname = "basededados";

   // open connection to mysql database
   $connection = mysqli_connect($host, $username, $password, $dbname) or die("Connection Error " . mysqli_error($connection));

   // fetch mysql table rows
   $sql = "SELECT * FROM `tabela`";
   $result = mysqli_query($connection, $sql) or die("Selection Error " . mysqli_error($connection));
   $f = "ficheiro.csv";
   $fp = fopen('perfiskwh.csv', 'w+');


   while($row = mysqli_fetch_assoc($result))
   {
       fputcsv($fp,$row);
   }
   // read into array
   $arr = file($f);

   // edit first line
   $arr[0] = 'Data,KWh1,KWh2,KWh3,'. "\n";

   // write back to file
   file_put_contents($f, implode($arr));
   $path_to_file = 'ficheiro.csv';
   $file_contents = file_get_contents($path_to_file);
   $file_contents = str_replace('"'," ",$file_contents);
   file_put_contents($path_to_file,$file_contents);
   fclose($fp);
   //close the db connection
   mysqli_close($connection);

?>

Browser other questions tagged php csv highcharts

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