Google charts from MySQL

Question

I have tried for several hours to get MySQL data in a Google charts but I can't wrap my head around how to make a working page from the examples I've come across on the internet.

To start fresh I took a example from Google charts and manually filled it with data. This gives me the graph I want to have. the Google charts graph is generated by a simple HTML PAGE (JUST THE VARIABLE PART:

....
      function drawChart() {
        var data = google.visualization.arrayToDataTable([
          ['date',          'Baxico'    ,   'Degriek'   ,    'Foldman'  ,   'Madmeijs'  ,   'Marcello'  ,   'Meijster'  ,   'Pokermom'],
          ['110415180035',  38,             1,              16,             10,             6,              4,              25        ],
          ['110415190222',  38,             16,             6,              4,              1,              25,             10        ],
          ['110415200747',   6,             38,             25,             10,             1,              16,             4         ],
          ['110415211933',  10,             38,             6,              25,             4,              16,             1         ],
          ['110415222033',  16,             1,              10,             6,              38,             25,             4         ],
          ['110415232833',  38,             4,              1,              25,             10,             6,              16        ]
        ]);

        

I made the same data output in MySQL:

select tournamentid
,(select points  from pokermax_scores as t2 where playerid = 'Baxico' and t1.tournamentid = t2.tournamentid) as Baxico
,(select points  from pokermax_scores as t2 where playerid = 'Degriek' and t1.tournamentid = t2.tournamentid) as Degriek
,(select points  from pokermax_scores as t2 where playerid = 'Foldman' and t1.tournamentid = t2.tournamentid) as Foldman
,(select points  from pokermax_scores as t2 where playerid = 'Madmeijs' and t1.tournamentid = t2.tournamentid) as Madmeijs
,(select points  from pokermax_scores as t2 where playerid = 'Marcello' and t1.tournamentid = t2.tournamentid) as Marcello
,(select points  from pokermax_scores as t2 where playerid = 'Meijster' and t1.tournamentid = t2.tournamentid) as Meijster
,(select points  from pokermax_scores as t2 where playerid = 'Pokermom' and t1.tournamentid = t2.tournamentid) as Pokermom
from pokermax_scores as t1
group by tournamentid

which results in same data: http://i60.tinypic.com/6nqp76.png

But I can't get the data loaded as shown in this example: http://datamakessense.com/google-charts-api-from-your-sql-database-to-a-live-chart-with-no-coding-skills/

I can make the database connection, and paste in the SQL, but I'm unclear how to set the script so it takes the data from the SQL.

Answer 1

Hey buddy i had the same problem, what you are trying to do is to take the tournamentid and draw for every data column a line, bar, column or whatever this problem called "pivot table" try to search over the internet, there is two different solutions for this :

1. You can solve this in google charts api using javascript.
2. You can solve this by nested loops(for loops) + using two selects.

Check the code below: using the second solution.

<?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), JSON_NUMERIC_CHECK);
        // print_r($googleData);
?>



<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1/jquery.min.js"></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", {packages:["corechart"]});
google.setOnLoadCallback(drawChart);

function drawChart() {

    var data = new google.visualization.arrayToDataTable(<?=$googleData?>);

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

    var chart = new google.visualization.ColumnChart(document.getElementById('columnchart_material'));

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