Highcharts get data from database

Question

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?

Bagwell · Accepted Answer · 2013-11-11 18:39:17Z

3

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];
}

answered Nov 11, 2013 at 18:39

Bagwell

2,2366 gold badges20 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sebastian Bochan · Accepted Answer · 2013-11-12 00:04:46Z

1

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.

answered Nov 12, 2013 at 0:04

Sebastian Bochan

37.6k3 gold badges51 silver badges75 bronze badges

Comments

elyasnew · Accepted Answer · 2013-11-11 19:44:01Z

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

Sanpas · Accepted Answer · 2019-02-15 09:22:30Z

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>

While this may does solve the problem, it's difficult to see why. Please consider editing your answer to include an explanation of what you changed, why you changed it, and why it solves the problem.

Collectives™ on Stack Overflow

Highcharts get data from database

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related