Error creating php mysql line graph

0

I'm trying to make a line chart with php and mysql like this: jsfiddle based on that tutorial .

My graph only returns empty with undefined columns:

The idea is to make a graph where it contains 1 line per technician.

Follow the files:

  

index.php

<!DOCTYPE html>
<html>
    <head>
        <title>Data</title>
        <style>
            .chart-container {
                width: 640px;
                height: auto;
            }
        </style>
    </head>
    <body>
        <div class="chart-container">
            <canvas id="mycanvas"></canvas>
        </div>

        <!-- javascript -->
        <script type="text/javascript" src="js/jquery.min.js"></script>
        <script type="text/javascript" src="js/Chart.min.js"></script>
        <script type="text/javascript" src="js/linegraph.js"></script>
    </body>
</html>
  

data.php

<?php
   header('Access-Control-Allow-Origin: *');

   // Define database connection parameters
   $hn      = '127.0.0.1';
   $un      = 'root';
   $pwd     = '';
   $db      = 'master';
   $cs      = 'utf8';

   // Set up the PDO parameters
   $dsn     = "mysql:host=" . $hn . ";port=3306;dbname=" . $db . ";charset=" . $cs;
   $opt     = array(
                        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
                        PDO::ATTR_EMULATE_PREPARES   => false,
                       );
   // Create a PDO instance (connect to the database)
   $pdo     = new PDO($dsn, $un, $pwd, $opt);
   $data        = array();

   // Attempt to query database table and retrieve data
   try {
      $stmt    = $pdo->query("SELECT   
                               DAY( o.data_fechamento)AS dia,
                               u.nome    AS tecnico,  
                               count(*)  AS total
                                  FROM
                                  ocorrencias AS o
                                      LEFT JOIN usuarios AS u ON u.user_id = o.operador
                                   WHERE
                                        o.status IN (4)
                                        AND DAY( o.data_fechamento) <= DAY(NOW()) 
                                        AND MONTH( o.data_fechamento)= MONTH(NOW())
                                        AND YEAR( o.data_fechamento) = YEAR(NOW())
                                        GROUP BY  DAY( o.data_fechamento),u.nome");

       while($row  = $stmt->fetch(PDO::FETCH_OBJ))
      {

         $data[] = $row;
      }


      echo json_encode($data);

   }
   catch(PDOException $e)
   {
      echo $e->getMessage();
   }


?>
  

linegraph.js

$(document).ready(function(){
    $.ajax({
        url : "http://localhost:8080/cha/data.php",
        type : "GET",
        success : function(data){
            console.log(data);

            var dia     = [];
            var tecnico = [];
            var total   = [];

            for(var i in data) {
                dia.push(data[i].dia);
                tecnico.push(data[i].tecnico);
                total.push(data[i].total);
            }

            var chartdata = {
                labels: dia,
                datasets: [
                    {
                        label: "Tecnico",
                        fill: false,
                        lineTension: 0.1,
                        backgroundColor: "rgba(59, 89, 152, 0.75)",
                        borderColor: "rgba(59, 89, 152, 1)",
                        pointHoverBackgroundColor: "rgba(59, 89, 152, 1)",
                        pointHoverBorderColor: "rgba(59, 89, 152, 1)",
                        data: tecnico
                    },
                    {
                        label: "Total",
                        fill: false,
                        lineTension: 0.1,
                        backgroundColor: "rgba(29, 202, 255, 0.75)",
                        borderColor: "rgba(29, 202, 255, 1)",
                        pointHoverBackgroundColor: "rgba(29, 202, 255, 1)",
                        pointHoverBorderColor: "rgba(29, 202, 255, 1)",
                        data: total
                    }
                ]
            };

            var ctx = $("#mycanvas");

            var LineGraph = new Chart(ctx, {
                type: 'line',
                data: chartdata
            });
        },
        error : function(data) {

        }
    });
});
  

Example of how json is returning:

[
 {"dia":2,"tecnico":"Maria","total":2},
 {"dia":2,"tecnico":"Marcos","total":6},
 {"dia":2,"tecnico":"Joao","total":2},
 {"dia":2,"tecnico":"Carlos","total":4},
 {"dia":2,"tecnico":"Fernando","total":1},
 {"dia":2,"tecnico":"Luiz","total":5},
 {"dia":2,"tecnico":"Portuga","total":1},
 {"dia":2,"tecnico":"Caio","total":1},
 {"dia":2,"tecnico":"Time Ass. Tec.","total":2},
 {"dia":3,"tecnico":"Marcos","total":2},
 {"dia":3,"tecnico":"Caio","total":1},
 {"dia":4,"tecnico":"Leonardo","total":1},
 {"dia":4,"tecnico":"Marcos","total":8},
 {"dia":4,"tecnico":"Joao","total":2},
 {"dia":4,"tecnico":"Caio","total":27},
 {"dia":5,"tecnico":"Maria","total":1},
 {"dia":5,"tecnico":"Marcos","total":1},
 {"dia":5,"tecnico":"Joao","total":9},
 {"dia":5,"tecnico":"Luiz","total":3},
 {"dia":5,"tecnico":"Caio","total":3},
 {"dia":6,"tecnico":"Luiz","total":2},
 {"dia":8,"tecnico":"Marcos","total":10},
 {"dia":8,"tecnico":"Luiz","total":5},
 {"dia":8,"tecnico":"Caio","total":1},
 {"dia":9,"tecnico":"Antonio","total":3},
 {"dia":9,"tecnico":"Leonardo","total":1},
 {"dia":9,"tecnico":"Marcos","total":2},
 {"dia":9,"tecnico":"Luiz","total":4},
 {"dia":9,"tecnico":"Leandro","total":7},
 {"dia":9,"tecnico":"Rodrigo","total":4},
 {"dia":10,"tecnico":"Maria","total":1},
 {"dia":10,"tecnico":"Antonio","total":2},
 {"dia":10,"tecnico":"Leonardo","total":5},
 {"dia":10,"tecnico":"Marcos","total":8},
 {"dia":10,"tecnico":"Luiz","total":5},
 {"dia":10,"tecnico":"Caio","total":1}
  ]
    
asked by anonymous 11.05.2017 / 01:10

1 answer

1

A dataType: "json" was missing from your AJAX request. The way you did, data comes as string , not a JavaScript object. It should look like:

$.ajax({
    url : "http://localhost:8080/cha/data.php",
    type : "GET",
    dataType : "json",    // <~~~ Adicionar esta linha!
    success : function(data){ ... },
    error : function(data){ ... }
});

See:

$(document).ready(function() {
  $.ajax({
    url: "https://gist.githubusercontent.com/acwoss/702b3ed3282305cbe348411dcec00cf3/raw/be8ed3eabf763deaced935a4727aaeda00504487/graphjs-test.json",
    type: "GET",
    dataType: "json",
    success: function(data) {

      var dia = [];
      var tecnico = [];
      var total = [];

      for (var i in data) {
        dia.push(data[i].dia);
        tecnico.push(data[i].tecnico);
        total.push(data[i].total);
      }

      var chartdata = {
        labels: dia,
        datasets: [{
            label: "Tecnico",
            fill: false,
            lineTension: 0.1,
            backgroundColor: "rgba(59, 89, 152, 0.75)",
            borderColor: "rgba(59, 89, 152, 1)",
            pointHoverBackgroundColor: "rgba(59, 89, 152, 1)",
            pointHoverBorderColor: "rgba(59, 89, 152, 1)",
            data: tecnico
          },
          {
            label: "Total",
            fill: false,
            lineTension: 0.1,
            backgroundColor: "rgba(29, 202, 255, 0.75)",
            borderColor: "rgba(29, 202, 255, 1)",
            pointHoverBackgroundColor: "rgba(29, 202, 255, 1)",
            pointHoverBorderColor: "rgba(29, 202, 255, 1)",
            data: total
          }
        ]
      };

      var ctx = $("#mycanvas");

      var LineGraph = new Chart(ctx, {
        type: 'line',
        data: chartdata
      });
    },
    error: function(data) {

    }
  });
});
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script><scriptsrc="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.1.4/Chart.bundle.min.js"></script>

<div class="chart-container">
  <canvas id="mycanvas"></canvas>
</div>
    
11.05.2017 / 02:18