Php and MySQL with Highchart

Question

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");

  ?>

If you view the source of the webpage your are trying to display this, does it show all the JavaScript correctly? That's an easy way to find out if you have a php problem or a JavaScript one — Arian Faurtosh, Dec 27 '13 at 02:29
How does your json look like? can show that what actually is being written? — SPandya, Dec 27 '13 at 07:03
I don't have json file, instead direct coding into mysql and php — user3098728, Dec 27 '13 at 08:46
Take look at the similiar topic http://www.highcharts.com/docs/working-with-data/preprocessing-data-from-a-database. I advice to return json in your php file, and then load json by jquery. You will be ensured that all values are correct. — Sebastian Bochan, Dec 27 '13 at 10:12

vee · Accepted Answer · 2013-12-27T03:05:39.160

2

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.

edited Dec 27 '13 at 03:05

answered Dec 27 '13 at 02:31

vee

38,255
7
74
78

I already did what you said, but nothing change. And I encountered new error --- Deprecated: Assigning the return value of new by reference is deprecated in C:\xampp\php\PEAR\Config.php on line 80 Deprecated: Assigning the return value of new by reference is deprecated in C:\xampp\php\PEAR\Config.php on line 166 Deprecated: Assigning the return value of new by reference is deprecated in C:\xampp\php\PEAR\Config\Container.php on line 111 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 – user3098728 Dec 27 '13 at 02:50
That error tells me that either the connection to the database is not successful or the query failed. Can you post your `Config.php`? – vee Dec 27 '13 at 02:54
Here's my config.php -- – user3098728 Dec 27 '13 at 02:56
I put the config.php in the folder where my index.php place and the error was gone, but when I reload the webpage, I got a blank web page again – user3098728 Dec 27 '13 at 03:01
Please update your question with the updated code you're using. – vee Dec 27 '13 at 03:02
@user3098728, wrap `` with single quotes like you've done for `` – vee Dec 27 '13 at 03:06
I did that, but nothing happened – user3098728 Dec 27 '13 at 03:12

Gopal Joshi · Answer 2 · 2016-10-28T07:14:31.343

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.

score 1 · Answer 3 · answered Dec 27 '13 at 02:32

1

I think your supposed to have single quotes around this

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

Should be

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

answered Dec 27 '13 at 02:32

Arian Faurtosh

17,987
21
77
115

score 0 · Answer 4 · edited May 23 '17 at 12:33

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.

Php and MySQL with Highchart

4 Answers4

Linked