Convert mySQL pivot table into string for google visualization

Question

Thanks to a lot of different resources both on SO (This one specifically) and the intrawebs in general, I've managed to create a pivot table with mySQL.

You can see a working example (limited data) here: SQL Fiddle

Now I want to get that data out, through PHP and convert it into a json string so I can send it as an ajax response to an HTML page where I'm trying to display a chart (column chart) via google visualization just like this one: https://code.google.com/apis/ajax/playground/?type=visualization#column_chart

My question is: How do I transform the pivot table into the correct format for the chart to understand it?

The echo(ed) results from my php file (sph.php) end up with data structured as so:

[{"ps_job_serial_num":"888888","105":null,"104":"4.00","400":null,"101":"5.00","102":"3.00","204":"6.00","103":"2.00","399":"1.00","300":"1.00","205":"7.00","203":"2.00","404":null,"405":null,"202":null,"301":null,"106":null,"304":null,"401":null,"201":null,"402":null,"403":null,"303":null},
{"ps_job_serial_num":"999999","105":null,"104":"2.00","400":null,"101":"1.00","102":"0.00","204":null,"103":null,"399":"3.00","300":"2.00","205":"3.00","203":null,"404":null,"405":null,"202":null,"301":null,"106":null,"304":null,"401":null,"201":null,"402":null,"403":null,"303":null},
{"ps_job_serial_num":"111111","105":"1.00","104":"3.00","400":null,"101":"5.00","102":"4.00","204":"10.00","103":"7.00","399":"1.00","300":"2.00","205":null,"203":null,"404":null,"405":null,"202":null,"301":null,"106":null,"304":"1.00","401":null,"201":null,"402":null,"403":null,"303":null},
{"ps_job_serial_num":"222222","105":null,"104":"1.00","400":null,"101":"1.00","102":"1.00","204":"3.00","103":"1.00","399":null,"300":null,"205":null,"203":null,"404":"3.00","405":null,"202":null,"301":null,"106":null,"304":null,"401":null,"201":null,"402":null,"403":null,"303":null},
{"ps_job_serial_num":"333333","105":"2.00","104":"8.00","400":null,"101":"8.00","102":"9.00","204":"10.00","103":"8.00","399":"2.00","300":"5.00","205":"8.00","203":"8.00","404":null,"405":"7.00","202":"8.00","301":null,"106":"1.00","304":null,"401":null,"201":"6.00","402":null,"403":"6.00","303":null},
{"ps_job_serial_num":"444444","105":"2.00","104":"5.00","400":null,"101":"8.00","102":"9.00","204":"10.00","103":"8.00","399":"4.00","300":"3.00","205":"8.00","203":"5.50","404":"2.00","405":"8.00","202":"8.00","301":"2.00","106":"4.00","304":null,"401":"10.00","201":"10.00","402":"7.00","403":"7.00","303":"2.00"},

etc

But I really want the data formatted like so:

([
['ps_job_serial_num', '101', '102', '103', '104', '105', '106'],
['888888',  500,   381,   381,   110,   665,  157],
['999999',  750,   396,   381,   115,   594,  173],
['111111',  120,   406,   381,   115,   571,  167],
['222222',  100,   460,   381,   116,   619,  185],
['333333',  430,   401,   381,   120,   642,  195],
['444444',  120,   679,   381,   128,   624,  198]
]);

How can I make my data look like it needs to look?

Here is my full PHP code:

<?php
    $mysqli = new mysqli("localhost", "user", "password", "database");
    $query = "CALL new_procedure()";
    $result = $mysqli->query($query);

    while ($row = $result->fetch_assoc()) {$results_array[] = $row;}
    $jsontable = json_encode($results_array);
    echo $jsontable;
?>

Here is the full HTML page:

<head>
    <title>My realtime chart</title>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
        <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
        <script type="text/javascript">
                    // Load the Visualization API and the piechart package.
        google.load('visualization', '1', {'packages':['corechart']});

        // Set a callback to run when the Google Visualization API is loaded.
        google.setOnLoadCallback(drawChart);

        function drawChart() {
          var jsonData = $.ajax({
              url: "sph.php",
              dataType:"json",
              async: false
              }).responseText;

          // Create our data table out of JSON data loaded from server.
          var data = new google.visualization.DataTable(jsonData);

          // Instantiate and draw our chart, passing in some options.
          var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
          chart.draw(data, {width: 1200, height: 600});
        }
        </script>  
</head>

<body>
  <div id="chart_div" style="width: 1200px; height: 600px;"></div>
</body>

</html>

The error that is displayed on the HTML page is "Table has no columns"...

Update #1

After making the changes suggested by @asgallant (below), the data returned from PHP now looks like this:

[
["ps_job_serial_num",105,104,400,101,102,204,103,399,300,205,203,404,405,202,301,106,304,401,201,402,403,303],
["888888",null,4,null,5,3,6,2,1,1,7,2,null,null,null,null,null,null,null,null,null,null,null],
["999999",null,2,null,1,0,null,null,3,2,3,null,null,null,null,null,null,null,null,null,null,null,null],
["111111",1,3,null,5,4,10,7,1,2,null,null,null,null,null,null,null,1,null,null,null,null,null],
["222222",null,1,null,1,1,3,1,null,null,null,null,3,null,null,null,null,null,null,null,null,null,null],
[333333,2,8,null,8,9,10,8,2,5,8,8,null,7,8,null,1,null,null,6,null,6,null],
[444444,null,3,null,2,2,8,3,1,1,null,null,2,6,null,null,null,null,null,null,null,null,null],
[555555,null,2,null,2,2,8,3,2,1,null,null,2,3,null,null,null,null,null,null,null,null,null],
[666666,null,2,null,2,2,7,3,1,2,null,null,2,8,null,null,null,null,null,null,null,null,null],
[777777,null,2,null,2,2,8,3,1,1,null,null,2,7,null,null,null,null,2,null,null,null,null]
]

Which looks correct. However I noticed starting after the 5th line, there are no longer quotes around the ps_job_serial_number. I'm wondering if this is what's making it bonk?

The error message from the Chrome console is:

Uncaught Error: Not an array
(anonymous function) 
drawChart

Update #2 Changed the query to only return the rows that had quotes around the ps_job_serial_num (to trouble shoot if that was causing the error). it made no difference. The error remains the same:

Uncaught Error: Not an array
(anonymous function) 
drawChart

Updated answer below to fix the "not an array" error. The quotes issue is a bit trickier to solve. I added a `JSON_NUMERIC_CHECK` to the `json_encode` call, which should have turned all of the data values that are numbers to numbers instead of strings (which is what MySQL outputs numbers as); I'm more puzzled that the first few rows didn't convert to numbers (unless you are masking the data, and those values contain non-numeric characters). (cont...) — asgallant, Nov 14 '13 at 15:50
The Visualization API requires numbers to be input properly, if you add them as strings, the charts will break. Are all of your columns (other than ps_job_serial_number) going to be "number" type? — asgallant, Nov 14 '13 at 15:50

asgallant · Accepted Answer · 2013-11-14T15:51:02.003

0

Try this in your PHP:

<?php
    $mysqli = new mysqli("localhost", "user", "password", "database");
    $query = "CALL new_procedure()";
    $result = $mysqli->query($query);

    $results_array = Array(Array());
    $fillKeys = true;
    while ($row = $result->fetch_assoc()) {
        $temp = Array();
        foreach ($row as $key => val) {
            if ($fillKeys) {
                $results_array[0][] = $key;
            }
            $temp[] = $val;
        }
        $results_array[] = $temp;
        $fillKeys = false;
    }
    $jsontable = json_encode($results_array, JSON_NUMERIC_CHECK);
    echo $jsontable;
?>

and in your javascript, change the DataTable construction to use the #arrayToDataTable method (which is required if you want to put the data in this particular format - if you want to use the standard constructor, you have to know what the data type of each column is):

[edit - added JSON.parse below]

var data = google.visualization.arrayToDataTable(JSON.parse(jsonData));

edited Nov 14 '13 at 15:51

answered Nov 13 '13 at 23:00

asgallant

26,060
6
72
87

thanks for the help on this. I've updated the question to include new results. – Edward Nov 14 '13 at 14:37
Thanks again for all the help! Your solution worked and I'm not displaying charts exactly how I wanted! Side ntoe: To eliminate the quoted vs unquoted results, I did a concat('#', ps_job_serial_num) in the query so they all come out as strings. – Edward Nov 14 '13 at 16:00
That last one should read "... I'm *now* displaying charts exactly how I wanted!...." – Edward Nov 14 '13 at 16:16

Convert mySQL pivot table into string for google visualization

1 Answers1