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}
]