Populating Highcharts chart with data received from MySQL

Question

I'm new to web programming and this thing here is for my bachelor's degree project. I am doing an electrical energy meter and I want to see the measured data between 2 dates I choose, data which is stored in a database. I managed to pass the data through AJAX from the form to the PHP file that reads the database and I successfully receive the JSON encoded data into my console log.

The problem is that the chart is not populated with any data, it only shows an awkward time range.

JavaScript - included in index.php, not *.js file

$(document).ready(function() {

$('form.energy_consumption_datepicker').on('submit', function() {

    var that = $(this),
    url = that.attr('action'),
    type = that.attr('method'),
    data = {};

    that.find('[name]').each(function(index, value) {

    var that = $(this),
    name = that.attr('name'),
    value = that.val();

    data[name] = value;
    });

    $.ajax ({

        url: url,
        type: type,
        data: data,
        dataType: "json",
        success: function (response) {
            console.log(response);

            $(function() {
        $('#custom_energy_consumption_chart').highcharts({
            chart: {
                zoomType: 'x'
            },
            title: {
                text: 'Instantaneous absorbed power'
            },
            xAxis: {
                type: 'datetime'
            },
            yAxis: {
                title: {
                    text: 'kW'
                },
                labels: {
                    formatter: function() {
                        return this.value / 1000; // formatted number for kW
                    }
                }
            },

            credits: {
                enabled: false
            },
            tooltip: {
                pointFormat: '<b>{point.y} W </b>'
            },
            plotOptions: {
                area: {
                    fillColor: {
                        linearGradient: {
                            x1: 0,
                            y1: 0,
                            x2: 0,
                            y2: 1
                        },
                        stops: [
                            [0, Highcharts.getOptions().colors[0]],
                            [1, Highcharts.Color(Highcharts.getOptions().colors[3]).setOpacity(0).get('rgba')]
                        ]
                    },
                    marker: {
                        enabled: true,
                        symbol: 'circle',
                        radius: 3,
                        states: {
                            hover: {
                                enabled: true
                            }
                        }
                    }
                }
            },
            series: [{
                color: '#00d5ff',
                type: 'area',
                name: 'Watts',
                data: response
            }]
        });

        });

        $(".custom_energy_consumption").show(350);

        }

    });
    return false;
});

});

index.php - parts

    <script src="../../js/highcharts.js"></script>
    <script src="../../js/highcharts-more.js"></script>
    .
    .
    <div class="energyConsumption" style="width: auto; height: 400px; margin: 80px auto auto 80px; display:none;">

        <h3 align="left">Choose the period to display the energy consumption</h3>

        <form class="energy_consumption_datepicker" action="custom_chart.php" method="post">

            <div class="input-control text" id="fromDatepicker">

                <input name="fromDate" id="fromDatepicker_input" type="text" data-validate-func="date" data-validate-func="required" data-validate-hint="Please select a date" data-validate-hint-position="bottom">
                <span class="input-state-error mif-warning"></span>
                <span class="input-state-success mif-checkmark"></span>
                <button class="button"><span>From</span></button>

            </div>

            <div class="input-control text" id="toDatepicker">

                <input name="toDate" id="toDatepicker_input" type="text" data-validate-func="date" data-validate-func="required" data-validate-hint="Please select a date" data-validate-hint-position="bottom">
                <span class="input-state-error mif-warning"></span>
                <span class="input-state-success mif-checkmark"></span>
                <button class="button"><span>To</span></button>

            </div>

            <input type="submit" class="button primary" value="Show">

        </form>

        <div class="custom_energy_consumption" id="custom_energy_consumption_chart" style="width: auto; height: 400px; margin: 50px auto; display:none"></div>

    </div>

custom_chart.php

<?php

    $fromDate = $_POST["fromDate"];
    $toDate = $_POST["toDate"];

    header('Content-Type: application/json');

    $servername = "localhost";
    .
    .
    $dbname = "smart_meter";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 

    $sql = "SELECT EXTRACT(year FROM date) 'year', EXTRACT(month FROM date)-1 'month', EXTRACT(day FROM date) 'day', EXTRACT(hour FROM date) 'hour', EXTRACT(minute FROM date) 'minute', energy FROM energy_data WHERE date BETWEEN '$fromDate' AND '$toDate'";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            $customPc[] = "[Date.UTC(" . $row["year"] . "," . $row["month"] . "," . $row["day"] . "," .  $row["hour"] . "," .  $row["minute"] . ")," . $row["energy"] . "]";
        }
    } else {
        echo 0;
    }
    $conn->close();

    echo json_encode(join($customPc, ','));

    ?>

Data received in console log:

[Date.UTC(2016,2,8,14,35),1534],[Date.UTC(2016,2,8,14,35),1534],[Date.UTC(2016,2,8,14,35),1534],[Date.UTC(2016,2,8,14,35),1534], ... [Date.UTC(2016,2,9,18,18),1534],[Date.UTC(2016,2,9,18,18),1534],[Date.UTC(2016,2,9,18,19),1534],[Date.UTC(2016,2,9,18,19),1534]

I replaced response in 'data: response' with the received data but writing 'data: [console data here]' and it works great.

Does anyone have an idea what might be happening here? Any suggestions would be helpful.

Thank you!

Answer 1

The problem is that functions in JSON are not evaluated. It means that your Date.UTC() is not triggered, so timestamps are not returned. The fix is remove Date.UTC() from your JSON and put there timestamps.

In PHP you can use the date function and multiply UNIX timestamps by 1000 ( convert to javascript timestamps)

Answer 2

I guess this is wrong.

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        $customPc[] = "[Date.UTC(" . $row["year"] . "," . $row["month"] . "," . $row["day"] . "," .  $row["hour"] . "," .  $row["minute"] . ")," . $row["energy"] . "]";
    }
} else {
    echo 0;
}

Try with this.

 if ($result->num_rows > 0) {
    // output data of each row
    $counter = 0;
    while($row = $result->fetch_assoc()) {
        $customPc[$counter] = array("year"=>date('Y',strtotime($row["year"])), "month"=>date('F',strtotime($row["month"])),"day"=>$row["day"],"hour"=>$row["hour"], "minute"=>$row["minute"],"energy"=> $row["energy"] );
        $counter ++;
    }
} else {
    echo 0;
}

Answer 3

There is a bit too much here to check everything, but one big problem is the way you send the data back:

    while($row = $result->fetch_assoc()) {
        $customPc[] = "[Date.UTC(" . $row["year"] . "," . $row["month"] . "," . $row["day"] . "," .  $row["hour"] . "," .  $row["minute"] . ")," . $row["energy"] . "]";
    }

...

echo json_encode(join($customPc, ','));

Using string concatenation and join will result in one big string enclosed in quotes like "all the data concatenated here".

You need an actual nested array with all your datapoints so one long string does not result in anything useful for highcharts.

You should generate a nested array structure and send that as json to your javascript. Something like:

    while($row = $result->fetch_assoc()) {
        // Add an array with 2 values to your data array
        $customPc[] = array(
            "Date.UTC(" . $row["year"] . "," . $row["month"] . "," . $row["day"] . "," .  $row["hour"] . $row["minute"],
            $row["energy"]
        );
    }

...

// Encode the data array as json
echo json_encode($customPc);

I am not sure if the generated date will work like this, perhaps you need to generate the date itself directly in php instead, but this is a lot closer to what you need.