0

I am new to web programming, I have personal project to track expenditure. Straight to the point, I have database data contains this month and last month total expenditure. I am able to show it manually (by echo). But i can't pass the value to highcharts series. Due to many small separate data, i want to populate it manually using php/mysql rather than json.

Graph2.php

<?php
$mysql_hostname = "localhost";
$mysql_user = "root";
$mysql_password = "mypassword";
$mysql_database = "mytraining";
$bd = mysqli_connect($mysql_hostname, $mysql_user, $mysql_password, $mysql_database);
if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }

$query2 = "select year(date) as y, month(date) as m, sum(amount) as p from expenditure_bak group by year(date), month(date) order by m ASC";  
$sth = mysqli_query($bd, $query2);
$rows = array();
$rows['name'] = 'Revenue';
while($r = mysqli_fetch_array($sth)) {
    $rows['data'][]= round($r['p'],2);
}
//echo join($rows['data'], ','); //able to display correct value 660.2,60

?>

PopupGraph.html

<?php
    include('graph2.php'); 
?>
<!DOCTYPE HTML>
<html>
    <head>

        <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
        <title>Monthly Expenditure</title>

        <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
        <style type="text/css">
        ${demo.css}
        </style>
        <script type="text/javascript">
        $(function () {
            $('#container').highcharts({
                title: {
                    text: 'Monthly Expenditure'
                },
                xAxis: {
                    categories: ['Jun', 'Jul']
                },
                yAxis: [{
                        min: 0,
                        title: {
                            text: ''
                        }
                    }, {
                        min: 0,
                        opposite: false, //optional, you can have it on the same side.
                        title: {
                            text: 'Average'
                        }
                    }
                ],
                labels: {
                    items: [{
                        html: 'Monthly Expenditure',
                        style: {
                            left: '50px',
                            top: '18px',
                            color: (Highcharts.theme && Highcharts.theme.textColor) || 'black'
                        }
                    }]
                },

                tooltip: {
                    formatter: function () {
                        return '<b>' + this.x + '</b><br/>' +
                            this.series.name + ': ' + this.y 
                            /*+ '<br/>' + 'Total: ' + this.point.stackTotal*/;
                    }
                },

                series: [{
                    type: 'spline',
                    name: 'Average',
                    yAxis: 1, //to make y axis
                    //data: [660.2,60], //able to show correct graph with static input  
                    data: [<?php echo join($rows[data], ',') ?>],
                    marker: {
                        lineWidth: 2,
                        lineColor: Highcharts.getOptions().colors[3],
                        fillColor: 'white'
                    }
                }]
            });
        });


        </script>
    </head>
    <body>
    <script src="https://code.highcharts.com/highcharts.js"></script>
    <script src="https://code.highcharts.com/modules/exporting.js"></script>

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

    </body>
</html>

So, what is the correct code to be put inside data: [] in the highcharts series.

Output should be like JSFiddle but using dynamic data from database obviously.

Finally, I want to display something like Expenditure Tracking

Bobby
  • 1,585
  • 3
  • 19
  • 42
lakon
  • 1
  • 3
  • please put quotes inside data like this data: [''] – Kaushal shah Jul 02 '16 at 06:17
  • maybe put $rows['data'] instead of $rows['data'][] in your while fetch loop. Could be causing it to be a 2d array. – applecrusher Jul 02 '16 at 06:22
  • Also you might need to encode json_encode(join($rows[data], ',')) your data – applecrusher Jul 02 '16 at 06:29
  • Hi @Kaushalshah when i put single quote, highcharts appear but no data displayed, [link](https://jsfiddle.net/mftzwv7q/6/). – lakon Jul 02 '16 at 16:29
  • Hi @applecrusher, i am not sure how to incorporate json actually, i prefer to use only mysql and pass on directly to highcharts if possible. – lakon Jul 02 '16 at 16:31
  • Have you already read Highcharts documentation connected with working with data from the database? It may help you with your problem: http://www.highcharts.com/docs/working-with-data/data-from-a-database – Grzegorz Blachliński Jul 04 '16 at 09:16
  • hi @GrzegorzBlachliński, in fact that was the first thing that i tried. Seems so straight forward solution. Unfortunately I still can't get it. I am trying different solutions now.. I got it for one parameter with json. Looking for solutions to pass many parameters into highcharts series. – lakon Jul 04 '16 at 15:00

1 Answers1

0

You will likely need to encode your data. I recommend that you take a look at the answer from here Pass a PHP array to a JavaScript function. Based on this, you should be able to do something like this:

JSON.parse(<?php echo json_encode(join($rows[data], ',')) ?>);

then you should be able to get the result that you want. You might not need the JSON.parse.

Community
  • 1
  • 1
applecrusher
  • 5,508
  • 5
  • 39
  • 89