Draw a column chart using Google Charts for this JSON var_dump(); data

Question

How can I put the below PHP-JSON code into a column chart using Google-Visualization?

<?php
    /* Connect  to database */
    $mysqli = new mysqli("localhost","root","123","charts");
    if(mysqli_connect_errno()){
      trigger_error('Connection failed: '.$mysqli->error);
    }

    /* Build the query */
    $query = "SELECT a.item_code,a.total,a.date FROM chart_values a, (SELECT DISTINCT item_code FROM chart_values GROUP BY item_code,date) b WHERE a.item_code = b.item_code";

    /* Loop through the results and build a JSON array for the data table */
    $result = $mysqli->query($query);

    $table = array();
    while ($row = $result->fetch_array(MYSQLI_ASSOC)) {

                if (!isset($table[$row['item_code']])) {
                    $table[$row['item_code']] = array(
                          'total' => array(),
                          'date' => array()
              );
           }

            $table[$row['item_code']]['total'][] = $row['total'];
            $table[$row['item_code']]['date'][] = $row['date'];
    }       
    echo var_dump($table);
    $data = json_encode($table);
?>

The var_dump($table); result is this:

array(3) { ["Scratch card 1.0 JD"]=> array(2) { ["total"]=> array(3) { [0]=> string(3) "411" [1]=> string(3) "333" [2]=> string(3) "123" } ["date"]=> array(3) { [0]=> string(10) "2013-04-01" [1]=> string(10) "2014-03-01" [2]=> string(10) "2015-02-01" } } ["Scratch card 2.0 JD"]=> array(2) { ["total"]=> array(3) { [0]=> string(3) "212" [1]=> string(3) "500" [2]=> string(3) "608" } ["date"]=> array(3) { [0]=> string(10) "2013-04-01" [1]=> string(10) "2014-03-01" [2]=> string(10) "2015-02-01" } } ["Scratch card 3.0 JD"]=> array(2) { ["total"]=> array(3) { [0]=> string(3) "234" [1]=> string(3) "345" [2]=> string(3) "456" } ["date"]=> array(3) { [0]=> string(10) "2013-04-01" [1]=> string(10) "2014-03-01" [2]=> string(10) "2015-02-01" } } }

Thanks ZygD, Do you have any idea's of how can i do this ? – Hisham May 10 '15 at 00:55 — Hisham, May 10 '15 at 00:55

Josua Marcel C · Accepted Answer · 2015-05-11T01:30:36.430

<?php
    /* Connect  to database */
    $mysqli = new mysqli("localhost","root","123","charts");
    if(mysqli_connect_errno()){
      trigger_error('Connection failed: '.$mysqli->error);
    }

    /* Build the query */
    $query = "SELECT a.item_code,a.total,a.date FROM chart_values a, (SELECT DISTINCT item_code FROM chart_values GROUP BY item_code,date) b WHERE a.item_code = b.item_code";

    /* Loop through the results and build a JSON array for the data table */
    $result = $mysqli->query($query);

    $table = array();
    while ($row = $result->fetch_array(MYSQLI_ASSOC)) {

                if (!isset($table[$row['item_code']])) {
                    $table[$row['item_code']] = array(
                          'total' => array(),
                          'date' => array()
              );
           }

            $table[$row['item_code']]['total'][] = $row['total'];
            $table[$row['item_code']]['date'][] = $row['date'];
    }       
    //echo var_dump($table);
    $datas = $table;
   // echo $datas; // insert this data to your database, cause this $data is string.

    $googleData = array('Date');
    foreach($datas as $key => $data){
        $googleData[] = $key;
    }

    for($i=0;$i<count($datas);$i++){
        foreach($datas as $key => $data){
            if(!in_array($data['date'][$i], $googleData)){
                $googleData[] = $data['date'][$i];
            }
            $googleData[] = $data['total'][$i];
        }
    }

    $googleData = json_encode(array_chunk($googleData,count($datas)+1));
    print_r($googleData);
?>

just remove comment from your js code and change it to $googleData

var data = google.visualization.arrayToDataTable(JSON.parse('<?php echo $googleData; ?>'));

this is my code

<script src="https://www.google.com/jsapi"></script>
<body>
    <div style="width:90%; height:500px;" id="columnchart_material" style="width: 1000px; height: 500px;"></div>
</body>

<?php 

$datas = '{"Scratch card 1.0 JD":{"total":["411","333","123"],"date":["2013-04-01","2014-03-01","2015-02-01"]},"Scratch card 2.0 JD":{"total":["212","500","608"],"date":["2013-04-01","2014-03-01","2015-02-01"]},"Scratch card 3.0 JD":{"total":["234","345","456"],"date":["2013-04-01","2014-03-01","2015-02-01"]}}';

$datas = json_decode($datas,true);
// echo '<pre>';
// print_r($datas);

        $googleData = array('Date');
        foreach($datas as $key => $data){
            $googleData[] = $key;
        }

        for($i=0;$i<count($datas);$i++){
            foreach($datas as $key => $data){
                if(!in_array($data['date'][$i], $googleData)){
                    $googleData[] = $data['date'][$i];
                }
                $googleData[] = $data['total'][$i];
            }
        }

        $googleData = json_encode(array_chunk($googleData,count($datas)+1));
        // print_r($googleData);
?>
<script type="text/javascript">
google.load("visualization", "1.1", {
    packages: ["bar"]
});
google.setOnLoadCallback(drawChart);

function drawChart() {

    var data = google.visualization.arrayToDataTable(JSON.parse('<?php echo $googleData; ?>'));

    var options = {
        chart: {
            title: 'Company Performance',
            subtitle: 'Sales, Expenses, and Profit: 2014-2017',
        }
    };

    var chart = new google.charts.Bar(document.getElementById('columnchart_material'));

    chart.draw(data, options);
}
</script>

NEW ANSWER

<?php
    /* Connect  to database */
    $mysqli = new mysqli("localhost","root","123","charts");
    if(mysqli_connect_errno()){
      trigger_error('Connection failed: '.$mysqli->error);
    }

    /* Build the query */
    $query = "SELECT a.item_code,a.total,a.date FROM chart_values a, (SELECT DISTINCT item_code FROM chart_values GROUP BY item_code,date) b WHERE a.item_code = b.item_code";

    /* Loop through the results and build a JSON array for the data table */
    $result = $mysqli->query($query);

    $table = array();
    while ($row = $result->fetch_array(MYSQLI_ASSOC)) {

                if (!isset($table[$row['item_code']])) {
                    $table[$row['item_code']] = array(
                          'total' => array(),
                          'date' => array()
              );
           }

            $table[$row['item_code']]['total'][] = $row['total'];
            $table[$row['item_code']]['date'][] = $row['date'];
    }       
    echo var_dump($table);
    $datas = json_encode($table);

$datas = json_decode($datas,true);
// echo '<pre>';
// print_r($datas);

        $googleData = array('Date');
        foreach($datas as $key => $data){
            $googleData[] = $key;
        }

        for($i=0;$i<count($datas);$i++){
            foreach($datas as $key => $data){
                if(!in_array($data['date'][$i], $googleData)){
                    $googleData[] = $data['date'][$i];
                }
                $googleData[] = $data['total'][$i];
            }
        }

        $googleData = json_encode(array_chunk($googleData,count($datas)+1));
        // print_r($googleData);
?>



<script src="https://www.google.com/jsapi"></script>
<body>
    <div style="width:90%; height:500px;" id="columnchart_material" style="width: 1000px; height: 500px;"></div>
</body>


<script type="text/javascript">
google.load("visualization", "1.1", {
    packages: ["bar"]
});
google.setOnLoadCallback(drawChart);

function drawChart() {

    var data = google.visualization.arrayToDataTable(JSON.parse('<?php echo $googleData; ?>'));

    var options = {
        chart: {
            title: 'Company Performance',
            subtitle: 'Sales, Expenses, and Profit: 2014-2017',
        }
    };

    var chart = new google.charts.Bar(document.getElementById('columnchart_material'));

    chart.draw(data, options);
}
</script>

still nothing happened :( omg i closed my eyes for moments.. could you please check this : http://stackoverflow.com/questions/18763900/using-google-charts-with-php-mysqli-cannot-get-it-working i read that for using google charts you must use those arrays table['cols'] and table['rows'] idk.. for some kind of google api only recognize those i think :/ or maybe i'm wrong because i've seen a lot of examples for people using the same method, this method in link — Hisham, May 10 '15 at 03:21
can you alert the $googleData in your javascript file? if can this should be work, var data = google.visualization.arrayToDataTable(JSON.parse()); — Josua Marcel C, May 10 '15 at 03:25
check this, https://jsfiddle.net/0a2maksv/7/ already worked right? its only because the $googleData cannot be transfered to your javascript file — Josua Marcel C, May 10 '15 at 03:28
i guess it's because we didn't use the syntax in the example that i sent to you, could you take a look at this : https://developers.google.com/chart/interactive/docs/reference — Hisham, May 10 '15 at 03:35
can i see all your files and how you put it on directory? its already done with our code. only one that still missing, how you parse your data from php to js? — Josua Marcel C, May 10 '15 at 03:41
in the same file named : josua_code.php , not separated files. — Hisham, May 10 '15 at 03:55
hey, now put this on your code var data = google.visualization.arrayToDataTable(JSON.parse('')); — Josua Marcel C, May 10 '15 at 05:04
i did man but still blank page thanks josua .. sorry man i g2g to work i will look into it and try again later. ;) — Hisham, May 10 '15 at 05:11
Josua I know man but the problem in how you write the nested loops and how the multiple arrays works .. just have a look into this : http://stackoverflow.com/questions/18763900/using-google-charts-with-php-mysqli-cannot-get-it-working please man .. you must use those names for the arrays to make it useable and readable by the google charts by JSON ! — Hisham, May 10 '15 at 12:15
no, you can send different format to google chart API, it's ok with your code. one day you will understand. i updated this answer, try that one, if not working with you, im surrender. — Josua Marcel C, May 11 '15 at 01:32
Thanks Josua anyway, you are the only one that tried to help me.. i took your code and i will keep trying to achieve this appreciate your time :) — Hisham, May 12 '15 at 05:01
THANKS JOSUA I EDITED THE NEW ANSWER AND WORKED FOR ME *THANKS MAN* YOU ARE AWESOME ! — Hisham, May 14 '15 at 13:30

Draw a column chart using Google Charts for this JSON var_dump(); data

1 Answers1