Highcharts get data from database

I'm trying to graph some data from my database on a Highchart, but I can't seem to get the data to show.

I have the following PHP (snippet) that gets the data from the database and json_encodes it:

<?php

    $result = mysqli_query($cxn,"SELECT * FROM counter");

    while($row = mysqli_fetch_array($result)) {
        $value = $row['value'];
        $timestamp = strtotime($row['datetime']);
        $data[] = "[$value, $timestamp]";
    }   

    json_encode($data);

?>

The json_encode prints the following (I'm using datetime):

["[500, 1384122794]","[600, 1384153203]"]

I then have the following to graph the data:

<html>
    <body>
        <div id="container" style="height: 500px; min-width: 500px"></div>

        <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
        <script src="http://code.highcharts.com/stock/highstock.js"></script>
        <script>
            $(function() {
                $.getJSON('http://www.website.com/graph.php', function(data) { // I put website.com on purpose
                    // Create the chart
                    $('#container').highcharts('StockChart', {
                        rangeSelector : {
                            selected : 1
                        },

                        title : {
                            text : 'Title'
                        },

                        series : [{
                            name : 'AAPL',
                            data : data,
                            tooltip: {
                                valueDecimals: 2
                            }
                        }]
                    });
                });

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

The problem is that the graph doesn't actually show the points, it's just a blank graph.

What am I doing wrong?

标签： javascript php jquery json highcharts

4条回答

唯我独甜

2楼-- · 2019-04-14 12:09

The problem is that you return string, you need to return data as numbers. I recommend to familar with json_encode() flags, like JSON_NUMERIC_CHECK. Secondly $data[] = "[$value, $timestamp]"; line needs to be array not string with printed bracket.

0人赞添加讨论(0) 举报

我欲成王，谁敢阻挡

3楼-- · 2019-04-14 12:19

To answer my own question, I changed the while loop to:

while($row = mysqli_fetch_array($result)) {
    $value = $row['value'];
    $timestamp = strtotime($row['datetime']) * 1000;
    $data[] = [$timestamp, (int)$value];
}

0人赞添加讨论(0) 举报

霸刀☆藐视天下

4楼-- · 2019-04-14 12:21

Step-1 Create Database [highchartdemo]

Step-2 Create Two tables [demo_viewer, demo_click]

CREATE TABLE IF NOT EXISTS `demo_viewer` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `numberofview` int(11) NOT NULL,

  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;


CREATE TABLE IF NOT EXISTS `demo_click` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `numberofclick` int(12) NOT NULL,

  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

demo_viewer table:

demo_click table:

Step3- Create Database Configuration File config.php

    $dbHost = "localhost";

    $dbDatabase = "highchartdemo";

    $dbPasswrod = "root";

    $dbUser = "root";


    $mysqli = mysqli_connect($dbHost, $dbUser, $dbPasswrod, $dbDatabase);

?>

Step-4 Create Index.php file index.php

<?php


    require('config.php');


    /* Getting demo_viewer table data */

    $sql = "SELECT SUM(numberofview) as count FROM demo_viewer 

            GROUP BY YEAR(created_at) ORDER BY created_at";

    $viewer = mysqli_query($mysqli,$sql);

    $viewer = mysqli_fetch_all($viewer,MYSQLI_ASSOC);

    $viewer = json_encode(array_column($viewer, 'count'),JSON_NUMERIC_CHECK);


    /* Getting demo_click table data */

    $sql = "SELECT SUM(numberofclick) as count FROM demo_click 

            GROUP BY YEAR(created_at) ORDER BY created_at";

    $click = mysqli_query($mysqli,$sql);

    $click = mysqli_fetch_all($click,MYSQLI_ASSOC);

    $click = json_encode(array_column($click, 'count'),JSON_NUMERIC_CHECK);


?>


<!DOCTYPE html>

<html>

<head>

    <title>Highcharts get data from database</title>

    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">

    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.1.1/jquery.js"></script>

    <script src="https://code.highcharts.com/highcharts.js"></script>

</head>

<body>


<script type="text/javascript">


$(function () { 


    var data_click = <?php echo $click; ?>;

    var data_viewer = <?php echo $viewer; ?>;


    $('#container').highcharts({

        chart: {

            type: 'column'

        },

        title: {

            text: 'Yearly Website Ratio'

        },

        xAxis: {

            categories: ['2013','2014','2015', '2016']

        },

        yAxis: {

            title: {

                text: 'Rate'

            }

        },

        series: [{

            name: 'Click',

            data: data_click

        }, {

            name: 'View',

            data: data_viewer

        }]

    });

});


</script>


<div class="container">

    <br/>

    <h2 class="text-center">Highcharts get data from database</h2>

    <div class="row">

        <div class="col-md-10 col-md-offset-1">

            <div class="panel panel-default">

                <div class="panel-heading">Graphical View</div>

                <div class="panel-body">

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

                </div>

            </div>
        </div>
    </div>
</div>
</body>
</html>

0人赞添加讨论(0) 举报

beautiful°

5楼-- · 2019-04-14 12:25

you want to assign an array of "data" to the single column data which is wrong

see here :

$(function () {
        $('#container').highcharts({
            title: {
                text: 'Monthly Average Temperature',
                x: -20 //center
            },
            subtitle: {
                text: 'Source: WorldClimate.com',
                x: -20
            },
            xAxis: {
                categories: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
                    'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
            },
            yAxis: {
                title: {
                    text: 'Temperature (°C)'
                },
                plotLines: [{
                    value: 0,
                    width: 1,
                    color: '#808080'
                }]
            },
            tooltip: {
                valueSuffix: '°C'
            },
            legend: {
                layout: 'vertical',
                align: 'right',
                verticalAlign: 'middle',
                borderWidth: 0
            },
            series: [{
                name: 'Tokyo',
                data: [7.0, 6.9, 9.5, 14.5, 18.2, 21.5, 25.2, 26.5, 23.3, 18.3, 13.9, 9.6]
            }, {
                name: 'New York',
                data: [-0.2, 0.8, 5.7, 11.3, 17.0, 22.0, 24.8, 24.1, 20.1, 14.1, 8.6, 2.5]
            }, {
                name: 'Berlin',
                data: [-0.9, 0.6, 3.5, 8.4, 13.5, 17.0, 18.6, 17.9, 14.3, 9.0, 3.9, 1.0]
            }, {
                name: 'London',
                data: [3.9, 4.2, 5.7, 8.5, 11.9, 15.2, 17.0, 16.6, 14.2, 10.3, 6.6, 4.8]
            }]
        });
    });

one solution is to use a for loop and assign data and name in each loop

0人赞添加讨论(0) 举报

Highcharts get data from database

采纳回答

编辑标签

举报内容

检举类型

检举原因

检举说明(必填)

打开微信“扫一扫”，打开网页后点击屏幕右上角分享按钮

付费偷看金额在0.1-10元之间