json DataTable in googlecharts

Question

I am having a bit of trouble getting the proper format of json string.

I have a database table that looks something like this:

Table Columns: emp   month   sales 
Table rows:    Bob    1       100
               Bob    2       150
               Jane   1       125
               Jane   2       130
               Mary   1       110
               Mary   2       130

Within drawChart(), I can create something like this statically:

var data = google.visualization.arrayToDataTable([
    ['Month', 'Bob', 'Jane', 'Mary],
    ['Jan',  100, 125, 110],
    ['Feb',  150, 130, 130]
    ]);

In the end, the json string needs to look like this:

{"cols":[{"label":"Month","type":"string"},
        {"label":"Bob","type":"number"},
        {"label":"Jane","type":"number"},
        {"label":"Mary","type":"number"}],
"rows":[{"c":[{"v":100},{"v":125},{"v":110}]},
        {"c":[{"v":150},{"v":130},{"v":130}]}]}

But I am having trouble pulling from the table to come up with proper json formatting that is equivalent to the above. I am following the steps from here... PHP MySQL Google Chart JSON - Complete Example

But that example is only for a single data set. if you were to add multiple weeks instead of having just one data set, how do run the query?

Could you elaborate more on what is missing from the example that you need to know? What do you mean by "if you were to add multiple weeks instead of having just one data set"? Looking at your example database table and the way you have constructed your JSON (which is malformed, by the way), the two layouts are completely different (necessitating some finagling in the query if you really want to reorganize the data structure). What is the end goal for the chart? — asgallant, Dec 09 '13 at 15:37
Nothing is "missing" From the example; rather, I want to expand upon the example -- instead of just one week of data, what if you had 2+ weeks of data? In other words, add a 3rd column (call it 'week' - which represents the week the work was performed). Then add add the 4 rows again for week 1; then add another 4 rows for week 2, etc. The query with just one week of data works as is, but as soon you add the 3rd column and multiple weeks of data, the query is not so straight forward. The end goal is I need to end up being the same as the static example in my call to arrayToDataTable(). — g t, Dec 09 '13 at 18:22
On the malformed json string: yes, that's not unlikely as I stripped down the actual string to make it as simple an example as possible, and may have mucked up the string when editing — g t, Dec 09 '13 at 18:25
-- "necessitating some finagling in the query..." - yes, exactly and that's what I am having trouble with is getting the query to output the proper formatted data. — g t, Dec 09 '13 at 19:31
also Note: I fixed the json string above: which the json string above is the end goal — g t, Dec 09 '13 at 19:33

score 0 · Answer 1 · answered Dec 09 '13 at 21:53

To get your data in the format you want, you have to pivot your data. Some databases support pivotting, but others like MySQL don't. If you are stuck without pivot support, then you have to resort to trickery to make it happen. Here's one way you could do it:

SELECT
    month,
    SUM(if(employee = "Bob", sales, 0)) AS Bob,
    SUM(if(employee = "Jane", sales, 0)) AS Jane,
    SUM(if(employee = "Mary", sales, 0)) AS Mary
FROM myTable
GROUP BY month

This requires that you know ahead of time what the employee names are so that you can write the SQL statement (either when you write the code, or you could pull them from another SQL query and write a dynamic SQL query).

g t · Answer 2 · 2013-12-13T21:05:11.583

Asgallent, thank you. Your response gave me the direction I needed. I was able to do it all dynamically via SQL. I made two queries: 1 to the "saleperson" table to get the names, and then another to pivot the data as you suggested. For anyone else that might find this helpful, here is the code I have.

The queries (Note: I am using codeigniter):

$sp_qry = $this->db->query('select * from salespeople');

        $qryString="";
        foreach ($sp_qry->result_array() as $row)
        {
            $qryString.= ",SUM( IF(  `salespeople_id` =" . $row['salespeople_id'] . ",  `num_sold` , 0 ) ) AS " . $row['name'];
        }

        $qry= "SELECT  `month` " . $qryString . " FROM  `product_sales` 
              GROUP BY  `month`";

        $query = $this->db->query($qry);
        return $query->result_array();

and in my viewing page

$rows = array();
$table = array();
$cols = array();
$cols[] = array('label' => 'Month', 'type' => 'string');
foreach ($salespeople as $sp)
   {
       $cols[] = array('label' => $sp['name'], 'type' => 'number');
   }

$table['cols'] = $cols;


foreach ($sales as $chart_item)
{  
    $tmp=array();
    $tmp[] = array('v' => (string) $chart_item['month']);
    foreach ($salespeople as $sp)
    {
        $name=$sp['name'];
        $tmp[] = array('v' => (int) $chart_item[$name]);
    }

    $rows[] = array('c' => $tmp);
}

$table['rows'] = $rows;
$jsonTable = json_encode($table);

json DataTable in googlecharts

2 Answers2