Reputation: 131

Highcharts - How to populate date with Mysql and PHP?

I'm new with highcharts and php. I tried to populate the highchart with the data from database. I tried to play the codes to get the result I want but I always failed. When I run, I always get blank page result.

Please help me. I need it so badly. Thanks!

Here's my full code of highchart

  <!DOCTYPE HTML>
  <html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <title>POS RESULT</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 db connection:

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

?>

Upvotes: 2

Answers (3)

Sebastian Bochan

Reputation: 37588

In the series you have something like

['<?php echo join($data2, "','"); ?>']

Which means that in first element of array you have string, so php is not called. I advice to familair with the article http://www.highcharts.com/docs/working-with-data/data-intro. Morever I advice to use json_encode which allow sto return json and then got in in javascript without joining / parsing.

Upvotes: 1

Gopal Joshi

Reputation: 2358

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

Table

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

js 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