Php and MySQL with Highchart

2020-06-28 04:40发布

站内问答 / PHP
1100 4 6

Somebody can help me. I'm new in php and highcharts. I tried to populate my chart using mysql and php, but when I tried to run it, the chart didn't appear, I only sse a blank web page. And there's no error appeared.

Her's my codes (sorry for messy code):

<!DOCTYPE HTML>
 <html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <title>Highcharts Example</title>

       <script type="text/javascript"    src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
    <script src="../../js/highcharts.js"></script>
    <script src="../../js/modules/exporting.js"></script>

</head>

        <body>

    <?php
include "config.php";

$SQL1 =     "SELECT * FROM pos";

$result1 = mysql_query($SQL1);
$data1 = array();
while ($row = mysql_fetch_array($result1)) {
   $data1[] = $row['name'];
   $data2[] = $row['Qty'];

}
?>

<script type="text/javascript">
$(function () {
    $('#container').highcharts({
        chart: {
            type: 'column',
            margin: [ 50, 50, 100, 80]
        },
        title: {
            text: 'List of POS'
        },
    credits: {
    enabled: false
    },
        xAxis: {
            categories: [<?php echo join($data1, "','"); ?>],
            labels: {
                rotation: -45,
                align: 'right',
                style: {
                    fontSize: '13px',
                    fontFamily: 'Verdana, sans-serif'
                }
            }
        },
        yAxis: {
            min: 0,
            title: {
                text: 'No. of Ticket'
            }
        },
        legend: {
            enabled: false,
    layout: 'vertical',
                        backgroundColor: '#FFFFFF',
                        align: 'left',
                        verticalAlign: 'top',
                        x: 50,
                        y: 35,
                        floating: true,
                        shadow: true
        },
        tooltip: {
            pointFormat: '<b>{point.y:.1f} tickets</b>',
        },
     plotOptions: {
                            column: {
                                        pointPadding: 0.2,
                                        borderWidth: 0
                                    }
                        },
        series: [{
            name: 'Qty',
            data: ['<?php echo join($data2, "','"); ?>'],
    dataLabels: {
                enabled: true,
                rotation: -90,
                color: '#FFFFFF',
                align: 'right',
                x: 4,
                y: 10,
                style: {
                    fontSize: '13px',
                    fontFamily: 'Verdana, sans-serif',
                    textShadow: '0 0 3px black',

                }
            }
        }]
    });
});

    </script>

   <div id="container" style="min-width: 500px; height: 400px; margin: 0 auto"></div>

</body>
    </html>

And here's my config.php

 <?php
 $mysql_hostname = "localhost";
 $mysql_user = "root";
 $mysql_password = "";
 $mysql_database = "pos";
 $prefix = "";
 $bd = mysql_connect($mysql_hostname, $mysql_user, $mysql_password) or die("Could not  connect database");
 mysql_select_db($mysql_database, $bd) or die("Could not select database");

  ?>

4条回答
一夜七次
2楼-- · 2020-06-28 05:18

Please Try Example as below. I think it can help you

SQL Table

CREATE TABLE IF NOT EXISTS `sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `month` varchar(200) DEFAULT NULL,
  `amount` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`)

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


INSERT INTO `sales` (`id`, `month`, `amount`) VALUES
(24, 'Apr', '15'),
(25, 'May', '40'),
(26, 'Jun', '26'),
(27, 'Jul', '31'),
(28, 'Aug', '39'),
(29, 'Sep', '25'),
(30, 'Oct', '27'),
(31, 'Nov', ' 32'),
(32, 'Dec', NULL);

Here we have Create new table and insert some data in it. Now Data will look as below

Mysql

index.php

<head>
    <meta name="Gopal Joshi" content="Highchart with Mysql" />
    <meta http-equiv="content-type" content="text/html; charset=utf-8" />
    <title>Highchart with Mysql Database</title>
    <script type="text/javascript" src="js/jquery.min.js"></script>
    <script type="text/javascript" src="js/setup.js"></script>
    <script type="text/javascript" src="js/test.js"></script>    
</head>

<body>
    <script src="js/highcharts.js"></script>
    <div id="sales" style="min-width: 310px; height: 400px; margin: 0 auto"></div>
</body>

setup.js

var chart;
 $(document).ready(function() {
        var cursan = {
   chart: {
    renderTo: 'sales',
    defaultSeriesType: 'area',
    marginRight: 10,
    marginBottom: 20
   },
   title: {
    text: 'Highchart With Mysql',
   },
   subtitle: {
    text: 'www.spjoshis.blogspot.com',
   },
   xAxis: {
    categories: ['Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec', 'Jan', 'Feb', 'Mar']
   },
   yAxis: {
    title: {
     text: 'Average'
    },
    plotLines: [{
     value: 0,
     width: 1,
     color: '#808080'
    }]
   },
   tooltip: {
       crosshairs: true,
                shared: true
   },
   legend: {
    layout: 'vertical',
    align: 'right',
    verticalAlign: 'top',
    x: -10,
    y: 30,
    borderWidth: 0
   },

            plotOptions: {

                series: {
                    cursor: 'pointer',  
                    marker: {
                        lineWidth: 1
                    }
                }
            },

         series: [{
       color: Highcharts.getOptions().colors[2],
    name: 'Test Colomn',
                marker: {
                    fillColor: '#FFFFFF',
                    lineWidth: 3,
                    lineColor: null // inherit from series
                },
                dataLabels: {
                    enabled: true,
                    rotation: 0,
                    color: '#666666',
                    align: 'top',
                    x: -10,
                    y: -10,
                    style: {
                        fontSize: '9px',
                        fontFamily: 'Verdana, sans-serif',
                        textShadow: '0 0 0px black'
                    }
                }
   }],
        }

        //Fetch MySql Records
        jQuery.get('js/data.php', null, function(tsv) {
   var lines = [];
   traffic = [];
   try {
    // split the data return into lines and parse them
    tsv = tsv.split(/\n/g);
    jQuery.each(tsv, function(i, line) {
     line = line.split(/\t/);
     date = line[0] ;
                                        amo=parseFloat(line[1].replace(',', ''));
                                        if (isNaN(amo)) {
                                                   amo = null;
                                        }
     traffic.push([
      date,
      amo
     ]);
    });
   } catch (e) {  }
   cursan.series[0].data = traffic;
   chart = new Highcharts.Chart(cursan);
  });
 });

s will import data from mysql from data.php and add to chart which is create previously in js.

data.php

$con=mysql_connect('localhost','root','');
mysql_select_db("test", $con);
$result=mysql_query('select * from sales order by id');
while($row = mysql_fetch_array($result)) {
  echo $row['month'] . "\t" . $row['amount']. "\n";
}

our chart is fully loaded .with mysql records and output will look li below

Output output

its example of area chart, you can change type of chart by changing defaultSeriesType: 'area'

Click Here for more Example with source.

查看更多
0人赞 添加讨论(0) 举报
Ridiculous、
3楼-- · 2020-06-28 05:19

Even if this is an old thread, this might have nothing to do with Highcharts, I'v just noted the final phrase in your error message:

"...Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given
in C:\xampp\htdocs\otrs\chart\graphs\pos\index.php on line 22"

tipically has to do with a query that fails returning FALSE instead of a rowset. You should take a look at this related question.

查看更多
0人赞 添加讨论(0) 举报
虎瘦雄心在
4楼-- · 2020-06-28 05:21

Blank pages usually mean syntax errors. You should switch error_reporting on.

The errors are in the use of your echo statements where you construct the json. The error is that you are missing semi colons in both the echo statements.

Replace <?php echo join($data1, ',') ?> with <?php echo join($data1, ','); ?>

Similarly for $data2:

Replace <?php echo join($data2, ',') ?> with <?php echo join($data2, ','); ?>

Another improvement you could make in the following block:

    <?php
include "config.php";

$SQL1 =     "SELECT * FROM pos";

$result1 = mysql_query($SQL1);
$data1 = array();
while ($row = mysql_fetch_array($result1)) {
   $data1[] = $row['name'];
}

$result2 = mysql_query($SQL1);
$data2 = array();
while ($row = mysql_fetch_array($result2)) {
   $data2[] = $row['Qty'];
}
?>

Instead of executing query twice to build two arrays, you could get rid of one of the queries and build both the arrays from the same query result:

<?php
include "config.php";

$SQL1 =     "SELECT * FROM pos";

$result1 = mysql_query($SQL1);

$data1 = array();
$data2 = array();

while ($row = mysql_fetch_array($result1)) {
   $data1[] = $row['name'];
   $data2[] = $row['Qty'];
}
?>

Note: The php mysql extension is deprecated as of PHP 5.5.0, you should be using either MySQLi or PDO_MySQL.

查看更多
0人赞 添加讨论(0) 举报
萌系小妹纸
5楼-- · 2020-06-28 05:27

I think your supposed to have single quotes around this

categories: [<?php echo join($data1, ',') ?>],

Should be 

categories: ['<?php echo join($data1, ',') ?>'],
查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(6)