Problem summing column values and displaying in highcharts

Asked

Viewed 403 times

5

I have the table: 

 numitens  |  data    |  email
 1         | 01/01/15 | [email protected]
 1         | 01/01/15 | [email protected]
 2         | 01/01/15 | [email protected]
 1         | 02/01/15 | [email protected]

The format you would like to display the data on the chart would be the total number of items from the day.

Code.

<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/css/bootstrap.min.css">
<script src="//code.jquery.com/jquery-1.11.2.min.js"></script>
<script src='highcharts.js' type='text/javascript'> </script>      
<script src='exporting.js' type='text/javascript'> </script>
<meta charset="utf-8">


</head> 
<body> 


<?php

$con = mysql_connect('localhost', 'root', '') or die('Error connecting to server');
       mysql_select_db("sandbox", $con);           
       $SQL1 = "SELECT * FROM highchart";



       $result1 = mysql_query($SQL1);
       $data1 = array();
       $data2 = array();
       while ($row = mysql_fetch_array($result1)) {
           $data1[] = $row['data'];
           $data2[] = ($row['numitens']);
       } 
?> 
<script type="text/javascript">
$(document).ready(function() {
    var chart = new Highcharts.Chart({
          chart: {
             renderTo: 'container',
             type: 'line' //spline, line, column
          }, 
          credits: {
      enabled: false
  },
  exporting: { 
    enabled: false 
  },
        title:  { text: 'Estatísticas' }, 
        xAxis:  { categories: ['<?php echo join("','", $data1) ?>'],}, 
        yAxis:  { min:0, }, 
        legend: {
                    layout: 'vertical',
                    backgroundColor: '#FFFFFF',
                    align: 'left',
                    verticalAlign: 'top',
                    x: 50,
                    y: 35,
                    floating: true,
                    shadow: true
                }, 
        plotOptions: { column: { pointPadding: 0.3, borderWidth: 0 }}, 
        series: [{ name: 'Data', data: [<?php echo join(',',$data2) ?>],
                       // pointStart: 0
                        //pointInterval
                    },]
    });
});
</script> 

<div id="container" style="min-width: 1024px; height: 400px; margin: 0 auto; max-width: 800px;"></div>


</body>
</html>

Gráfico gerado

php mysql highcharts

1 answer

1

If I understand your request correctly, the most appropriate would be to count the number of items per day using SQL itself:

$SQL1 = "SELECT sum(numitens) as numitens,data FROM highchart group by data";

Once this is done you can plot a graph with dates on the X-axis and quantities on the Y-axis.

HOWEVER, if you want Highcharts to interpret your X-axis as truth dates instead of equally spaced data (if you had 3 dates 01/01/2015,02/01/2015,10/01/2015 they would be equally spaced on the X-axis.... well maybe you want it yourself), you need to pass the date information like this (detail, the months start from 0):

Date.UTC(2015,0,1)

And you need to put this attribute in Xaxis:

type: 'datetime',

This example might help: http://jsfiddle.net/hdd8zjkn/

Browser other questions tagged php mysql highcharts

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