Produce highcharts multiple line chart from JSON / MySQL data

Question

Firstly I apologise, I am not a programmer (but I am learning - slowly). I am interested in graphs for Horticultural applications. I have a database that gets data from sensors on an hourly basis and the query grabs the last 12 to 48 readings, according to the select statement. With help from your forum, I have created 3 files that together extract the data from MySQL to plot a graph with multiple series of: Timestamp (with various options for display), temperature and humidity.

I based my work on this example in fiddle https://jsfiddle.net/gh/get/library/pure/highcharts/highcharts/tree/master/samples/highcharts/demo/line-basic which uses data provided 'in the script' but after three weeks I can't inject the data from JSON

The first of my files makes the MySQL db connection, the second file extracts and formats the data into JSON data and the third is supposed to create the graph but doesn't :-(.

this is what is produced with a series label for each row, rather than a line for each series.

Can you help me? I would like a line graphs showing temperature and humidity. Time/date along the bottom x-axis, the left y-axis temperature in degrees and a right-hand y-axis showing percentage humidity. Am I asking too much?

Finally can I please request no Ajax, or other "stuff" other than php, html, JSON, and javascripts if possible?

Any help on formatting my question would be much appreciated :-)

<?php $json_data = include ('nw_database02.php');?> 
<!DOCTYPE HTML>
<html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Line Graph</title>

    </head>
    <body>
<script src="https://code.highcharts.com/highcharts.js"></script>
<script src="https://code.highcharts.com/modules/exporting.js"></script>
<script src="https://code.highcharts.com/modules/export-data.js"></script>
<script src="https://code.highcharts.com/modules/accessibility.js"></script>
<script src="https://code.highcharts.com/modules/series-label.js"></script>

<div id="container"></div>

<script type="text/javascript">

Highcharts.chart('container', {
 
    title: {
        text: 'Temperature and Humidity'
    },

    subtitle: {
        text: 'Source: Greenhouse1'
    },

    yAxis: {
        title: {
            text: 'Temperature'
        }
    },

    legend: {
        layout: 'vertical',
        align: 'right',
        verticalAlign: 'middle'
    },

    plotOptions: {
        series: {
            label: {
                connectorAllowed: false
            },
            pointStart: 2010
        }
    },

    series: <?= $json_data?> ,


    responsive: {
        rules: [{
            condition: {
                maxWidth: 500
            },
            chartOptions: {
                legend: {
                    layout: 'horizontal',
                    align: 'center',
                    verticalAlign: 'bottom'
                }
            }
        }]
    }

});
</script>
</body>
</html>

...

If I echo the $json_data I get this type of result, but bear in mind it is dynamic data and changes every hour so it must be read from json_data every time the page is accessed:

[{"Timestamp":"10:04 02/01/21","temperature":"5","humidity":"66"},{"Timestamp":"10:19 02/01/21","temperature":"6","humidity":"65"},{"Timestamp":"10:35 02/01/21","temperature":"6","humidity":"64"},{"Timestamp":"10:50 02/01/21","temperature":"6","humidity":"64"},{"Timestamp":"11:06 02/01/21","temperature":"6","humidity":"64"},{"Timestamp":"11:21 02/01/21","temperature":"7","humidity":"63"},{"Timestamp":"11:34 02/01/21","temperature":"10","humidity":"66"},{"Timestamp":"04:21 02/01/21","temperature":"15","humidity":"64"},{"Timestamp":"04:36 02/01/21","temperature":"16","humidity":"61"},{"Timestamp":"04:51 02/01/21","temperature":"15","humidity":"59"},{"Timestamp":"05:07 02/01/21","temperature":"15","humidity":"60"},{"Timestamp":"05:22 02/01/21","temperature":"14","humidity":"61"}]

score 0 · Answer 1 · answered Feb 23 '21 at 12:18

you can achive with below code but when you choosing chart first know the purpose and create json data accroding to the series use apexcharts for free

<script type="text/javascript">
    var data=[{"Timestamp":"10:04 02/01/21","temperature":"5","humidity":"66"},{"Timestamp":"10:19 02/01/21","temperature":"6","humidity":"65"},{"Timestamp":"10:35 02/01/21","temperature":"6","humidity":"64"},{"Timestamp":"10:50 02/01/21","temperature":"6","humidity":"64"},{"Timestamp":"11:06 02/01/21","temperature":"6","humidity":"64"},{"Timestamp":"11:21 02/01/21","temperature":"7","humidity":"63"},{"Timestamp":"11:34 02/01/21","temperature":"10","humidity":"66"},{"Timestamp":"04:21 02/01/21","temperature":"15","humidity":"64"},{"Timestamp":"04:36 02/01/21","temperature":"16","humidity":"61"},{"Timestamp":"04:51 02/01/21","temperature":"15","humidity":"59"},{"Timestamp":"05:07 02/01/21","temperature":"15","humidity":"60"},{"Timestamp":"05:22 02/01/21","temperature":"14","humidity":"61"}];
   console.log(data);
   var timestamp=[];
   var temperature=[];
   var humidity=[];
   $.each(data, function(i, item) {
   console.log(data[i]);
    timestamp.push(data[i].Timestamp);
    temperature.push(data[i].temperature);
    humidity.push(data[i].humidity);
   
});

 Highcharts.chart('container', {

  title: {
    text: 'Solar Employment Growth by Sector, 2010-2016'
  },

  subtitle: {
    text: 'Source: thesolarfoundation.com'
  },

  yAxis: {
    title: {
      text: 'Number of Employees'
    }
  },

  xAxis: {
     categories: timestamp,
  },

  legend: {
    layout: 'vertical',
    align: 'right',
    verticalAlign: 'middle'
  },

  plotOptions: {
    series: {
      label: {
        connectorAllowed: false
      },
      pointStart: 2010
    }
  },

  series: [{
    name: 'temperature',
    data: temperature
  }, {
    name: 'humidity',
    data: humidity
  }],

  responsive: {
    rules: [{
      condition: {
        maxWidth: 500
      },
      chartOptions: {
        legend: {
          layout: 'horizontal',
          align: 'center',
          verticalAlign: 'bottom'
        }
      }
    }]
  }

});

</script>

Thank you very much for your answer. I'm sorry to say that you have taken static data in your line var data=[{"Timestamp .... But the $json_data is dynamic and changes every time you access the page.. I'm sure we're nearly there but I'm looking for an answer something like var data=[$json_data..] which reads the data at the time and then creates the graph from the latest json_data. Also it uses the doesn't answer the questions about the axes and titles used in my code or will they come from the data?. can you please try again to help? Thanks — HuggieDuggie, Feb 23 '21 at 13:17
Hi again - I tried your method using var data = = $json_data?> but I can't make it work, any ideas?? Many thanks for your help, Huggie Duggie — HuggieDuggie, Feb 24 '21 at 11:57

score 0 · Accepted Answer · answered Feb 23 '21 at 14:41

0

You need to format your data to the series structure required by Highcharts. The rest of the requirements are achievable by using chart options - please check xAxis and yAxis properties in API.

var data = <?= $json_data?>

var series = [{
        name: "temperature",
        data: []
    },
    {
        name: "humidity",
        data: [],
        yAxis: 1
    }
];

data.forEach(function(dataEl) {
    series[0].data.push([
        new Date(dataEl.Timestamp).getTime(),
        Number(dataEl.temperature)
    ]);
    series[1].data.push([
        new Date(dataEl.Timestamp).getTime(),
        Number(dataEl.humidity)
    ]);
});

Highcharts.chart('container', {
    series: series,
    yAxis: [{
            title: {
                text: 'temperature'
            }
        },
        {
            title: {
                text: 'humidity'
            },
            opposite: true
        }
    ],
    xAxis: {
        type: 'datetime'
    }
});

Live demo: http://jsfiddle.net/BlackLabel/aw95vek6/

API Reference: https://api.highcharts.com/highcharts/series.line.data

answered Feb 23 '21 at 14:41

ppotaczek

36,341
2
14
24

Fantastic, thank you so very much - worked first time. The only problem I have now is getting the x-axis date / time right, especially when there are 24 or more results spread over several days - any ideas? I also have a problem formatting the json data to regular date time and not Microsoft of "Timestamp":"10:04 02\/01\/21" - I would prefer "Timestamp":"10:04 02/01/21" Thanks again Huggie Duggie – HuggieDuggie Feb 24 '21 at 11:52
Hi @HuggieDuggie, Could you provide me with an example of problematic data? – ppotaczek Feb 24 '21 at 12:41
Thanks to ppotaczek, subbu ravi and Bhesh Gurung, I now have all of the answers I need and have produced the graphs I was looking for. The Y-axes, show temperature on the left and humidity on the right. The graph shows data at each of the points and now the x-axis shows the date and time corresponding with the data. The final part of formatting the x-axis came from Bhesh Gurung here: https://stackoverflow.com/questions/7101464/how-to-get-highcharts-dates-in-the-x-axis. So, thanks again for the kind help I received Huggie Duggie One day I too will be able to program :-) – HuggieDuggie Feb 24 '21 at 18:56

Produce highcharts multiple line chart from JSON / MySQL data

2 Answers2