duanraotun1674
2013-11-11 18:15
采纳率: 100%
浏览 67

Highcharts从数据库中获取数据

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?

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

4条回答 默认 最新

  • doulangxun7769 2013-11-11 18:39
    已采纳

    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];
    }
    
    打赏 评论
  • dongwenxiu5200 2013-11-11 19:44

    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

    打赏 评论
  • doutuo3575 2013-11-12 00:04

    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.

    打赏 评论
  • dousao6313 2019-02-15 08:47

    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: enter image description here

    demo_click table: enter image description here

    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>
    
    打赏 评论

相关推荐 更多相似问题