json DataTable in googlecharts

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?

Answers


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).


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);

Need Your Help

Sitecore AdvancedDatabaseCrawler advantages/benefits

database search module sitecore web-crawler

I tried using Sitecore.Search namespace and it seems to do basic stuff. I am now evaluating AdvancedDatabaseCrawler module by Alex Shyba. What are some of the advantages of using this module instea...

How is it done “Manage this list” on amazon?

javascript jquery html dhtml

I'm specifically looking, how is the hover on done? and what did they use? JavaScript? Jquery, DHTML? and does anyone have example?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.