Hello to the whole community! I would be very grateful if someone with more experience could give me an orientation on the next topic ... I want to show data from mysql to a highcharts graphic ... But I would like you to show me the data in a range of dates selected by the user, so which I have two inputs of type date and one button to generate the graph. Next I show the query, the graph, the function that "Should" fill the graph and the 'case' ajax that calls the query. I work with php, javascript and ajax. However, I have not managed to graph the JSON, in the "Graph" function, I have two different graphs drawn, one with the JSON and the other with solid data, the solid data is drawn perfectly, but the JSON obtained, I do not think I must separate the array obtained with the JSON, but honestly I do not know how to do it, I am quite new to the topic of arrays and JSON, I could use some advice ... I am currently reading the documentation, both Highcharts, and JSON. If someone has gone through this problem and has a solution, I would greatly appreciate it.
Query:
public function promedioPorDia($fecha_inicio,$fecha_fin)
{
$sql="SELECT (SUM(caudcruda)/count(caudcruda)) as caudalcruda,fecha FROM planta_n WHERE fecha BETWEEN '$fecha_inicio' AND '$fecha_fin' GROUP BY DAY(fecha) ORDER BY DAY(fecha) DESC";
return ejecutarConsulta($sql);
}
Case AJAX that sends request to the query:
case 'promedioPorDia':
$fecha_inicio=$_REQUEST["fecha_inicio"];
$fecha_fin=$_REQUEST["fecha_fin"];
$rspta=$consulta->promedioPorDia($fecha_inicio,$fecha_fin);
$data= array();
while ($reg=$rspta->fetch_object())
{
$data[]=array(
"0"=>$reg->fecha,
"1"=>$reg->caudalcruda
);
}
$results = array("aaData"=>$data);
echo json_encode($results);
break;
Function that "Should" show me the graph:
<script type="text/javascript">
function graficar()
{
var fecha_inicio = $("#fecha_inicio").val();
var fecha_fin = $("#fecha_fin").val();
var opciones = {
chart: {
renderTo: 'container',
type: 'spline'
},
subtitle: {
text: 'Caudal Agua Cruda',
x: -20
},
xAxis: {
categories: []
},
yAxis: {
title: {
text: 'Merguevo ps / Segundos (Lts/Seg)'
},
plotLines: [{
value: 0,
width: 1,
color: '#808080'
}]
},
tooltip: {
valueSuffix: ' Lts/Seg'
},
legend: {
layout: 'vertical',
align: 'right',
verticalAlign: 'middle',
borderWidth: 0
},
series: [{
name: '',
data: []
},
{
name: '',
data: []
}]
}
$.ajax({
url: "../ajax/consultas.php?op=promedioPorDia",
data:{fecha_inicio: fecha_inicio, fecha_fin: fecha_fin},
type: "get",
success: function(data){
console.log(data)
opciones.series[0].name = 'Linea 1';
opciones.series[0].data = [data]; //No me grafica nada!
opciones.series[1].name = 'Linea 2';
opciones.series[1].data = [1,2,3,4]; //Estos datos se grafican perfectamente.
chart = new Highcharts.Chart(opciones);
alert("Hola")
}
});
} '
Code of the inputs Date, Show and Graph button:
<div class="form-group col-lg-2 col-md-2 col-sm-2 col-xs-12">
<label>Fecha Inicio:</label>
<input type="date" class="form-control" name="fecha_inicio" id="fecha_inicio" value="<?php echo DATE("Y-m-d"); ?>">
</div>
<div class="form-group col-lg-2 col-md-2 col-sm-2 col-xs-12">
<label>Fecha Fin:</label>
<input type="date" class="form-control" name="fecha_fin" id="fecha_fin" value="<?php echo DATE("Y-m-d"); ?>">
</div>
<div class="form-group col-lg-2 col-md-2 col-sm-2 col-xs-12">
<label></label><br>
<button class="btn btn-success" onclick="hacer()">Mostrar</button>
</div>
<div class="col-lg-12 col-md-12 col-sm-12 col-xs-12">
<script type="text/javascript">
$(function () {
$('#container').highcharts({
title: {
text: '',
x: -20 //center
},
subtitle: {
text: 'Caudal Agua Cruda',
x: -20
},
xAxis: {
categories: []
},
yAxis: {
title: {
text: 'Litros / Segundos (Lts/Seg)'
},
plotLines: [{
value: 0,
width: 1,
color: '#808080'
}]
},
tooltip: {
valueSuffix: ' Lts/Seg'
},
legend: {
layout: 'vertical',
align: 'right',
verticalAlign: 'middle',
borderWidth: 0
},
series: [{
name: 'Caudal Agua Cruda',
data: []
}]
});
});
</script>
<div id="container" style="min-width: 310px; height: 400px; margin: 0 auto"></div>
</div>
In summary, I need that when the user selects a range of dates and press the Show button, the average graph will be generated according to the selected date range.
With the code I have once I select a range of dates, the following JSON is generated:
{"aaData":[["2018-07-18","6.0000"],["2018-07-17","12.6667"],["2018-07-16","3.0000"],["2018-07-13","6.4615"],["2018-07-10","127.5000"]]}
What I want to do with this JSON is to plot that data in Highcharts, the dates must go in the X axis and the average values in the Y axis, with their respective dates. I can not get the graph to be drawn in the Container.
Thanks in advance for all the support you can give me.