Victor Briones Carmona Victor Briones Carmona - 10 days ago 5
Javascript Question

How can I modify the display of this mySQL query in JSON with PHP?

I'm stuck with a chart, because I can't put the values like the chart requires. For example:

['Year', 'Sales'],
['2004', 1000],
['2005', 1170],
['2006', 660],
['2007', 1030]


And the query result in JSON is like this:

{"Date":"Nov 23, 2016","Total":"2","8AM-9AM":"0","9AM-10AM":"0","10AM-11AM":"0","11AM-12PM":"0","12PM-1PM":"0","1PM-2PM":"0","2PM-3PM":"0","3PM-4PM":"0","4PM-5PM":"0","5PM-6PM":"0","6PM-7PM":"0","7PM-8PM":"0","8PM-9PM":"0","9PM-10PM":"0","10PM-11PM":"0","11PM-12AM":"0","12AM-1AM":"1","1AM-2AM":"1","2AM-3AM":"0","3AM-4AM":"0","4AM-5AM":"0","5AM-6AM":"0","6AM-7AM":"0","7AM-8AM":"0"}


So I was thinking, Is the SQL query the problem? or Can I modify this query to adapt it in PHP?

This is the rest of the code involved:

$SQLDatos = "select DATE_FORMAT(hora, '%b %d, %Y') Date,
count(hora) as Total,

SUM(CASE WHEN HOUR(hora) = 08 THEN 1 ELSE 0 END) '8AM-9AM',
SUM(CASE WHEN HOUR(hora) = 09 THEN 1 ELSE 0 END) '9AM-10AM',
SUM(CASE WHEN HOUR(hora) = 10 THEN 1 ELSE 0 END) '10AM-11AM',
SUM(CASE WHEN HOUR(hora) = 11 THEN 1 ELSE 0 END) '11AM-12PM',
SUM(CASE WHEN HOUR(hora) = 12 THEN 1 ELSE 0 END) '12PM-1PM',
SUM(CASE WHEN HOUR(hora) = 13 THEN 1 ELSE 0 END) '1PM-2PM',
SUM(CASE WHEN HOUR(hora) = 14 THEN 1 ELSE 0 END) '2PM-3PM',
SUM(CASE WHEN HOUR(hora) = 15 THEN 1 ELSE 0 END) '3PM-4PM',
SUM(CASE WHEN HOUR(hora) = 16 THEN 1 ELSE 0 END) '4PM-5PM',
SUM(CASE WHEN HOUR(hora) = 17 THEN 1 ELSE 0 END) '5PM-6PM',
SUM(CASE WHEN HOUR(hora) = 18 THEN 1 ELSE 0 END) '6PM-7PM',
SUM(CASE WHEN HOUR(hora) = 19 THEN 1 ELSE 0 END) '7PM-8PM',
SUM(CASE WHEN HOUR(hora) = 20 THEN 1 ELSE 0 END) '8PM-9PM',
SUM(CASE WHEN HOUR(hora) = 21 THEN 1 ELSE 0 END) '9PM-10PM',
SUM(CASE WHEN HOUR(hora) = 22 THEN 1 ELSE 0 END) '10PM-11PM',
SUM(CASE WHEN HOUR(hora) = 23 THEN 1 ELSE 0 END) '11PM-12AM',
SUM(CASE WHEN HOUR(hora) = 00 THEN 1 ELSE 0 END) '12AM-1AM',
SUM(CASE WHEN HOUR(hora) = 01 THEN 1 ELSE 0 END) '1AM-2AM',
SUM(CASE WHEN HOUR(hora) = 02 THEN 1 ELSE 0 END) '2AM-3AM',
SUM(CASE WHEN HOUR(hora) = 03 THEN 1 ELSE 0 END) '3AM-4AM',
SUM(CASE WHEN HOUR(hora) = 04 THEN 1 ELSE 0 END) '4AM-5AM',
SUM(CASE WHEN HOUR(hora) = 05 THEN 1 ELSE 0 END) '5AM-6AM',
SUM(CASE WHEN HOUR(hora) = 06 THEN 1 ELSE 0 END) '6AM-7AM',
SUM(CASE WHEN HOUR(hora) = 07 THEN 1 ELSE 0 END) '7AM-8AM'
from user_log
WHERE
Date(`user_log`.`hora`) = Date_Add(CurDate(), INTERVAL -1 DAY) AND user_log.servicio = (select CodServicio from servicios where CodUsuario = 1024)
group by DATE_FORMAT(hora, '%b %d, %Y');";

//ejecutamos la consulta
$result = mysql_query($SQLDatos);
//obtenemos número filas
$numFilas = mysql_num_rows($result);

//cargamos array con los nombres de las métricas a visualizar
$datos = array();

//recorremos filas
while($r = mysql_fetch_assoc($result)) {
$rows = $r;
}
echo json_encode($rows);


And the Google Chart code:

var jsonData = $.ajax({
url: "aux_11.php",
dataType: "json",
async: true
}).responseText;

// Create our data table out of JSON data loaded from server.
var data = new google.visualization.DataTable(jsonData);
data.addColumn('number', 'Hora');
data.addColumn('number', 'Cantidad');
var options = {
title: '',
curveType: 'function',
legend: { position: 'right' }
};

var chart = new google.visualization.LineChart(document.getElementById('curve_chart'));

chart.draw(data, options);


Thanks.

Answer

You can also convert the json object to the array and then use it to create google chart

    var jsonData = $.ajax({
      url: "aux_11.php",
      dataType: "json",
      async: true
    }).responseText;

    // split the json object into the array 
    var dataArray = [];
    var i = 0;
    $.each(jsonData, function(index, value){
        if((index!='Date') && (index!='Total')){
            value = parseInt(value);
            var row = [index, value];
            dataArray[i] = row;
            i++;
          }
    });

    // Create our data table out of JSON data loaded from server.
    var data = new google.visualization.DataTable();
    data.addColumn('string', 'Hora'); // this should be string 
    data.addColumn('number', 'Cantidad');
    data.addRows(dataArray);

    var options = {
        title: '',
        curveType: 'function',
        legend: { position: 'right' }
    };

    var chart = new google.visualization.LineChart(document.getElementById('curve_chart'));

    chart.draw(data, options);
Comments