Feeding data into more than 2 columns in Google Column Chart from MYSQL.

Question

was wondering if I have the following table from the query

SELECT kodnegeri.KodNegeriText AS Negeri, kategorisukan.KategoriSukantext AS Kategori,
COUNT(*) AS Total
FROM association 
INNER JOIN kodnegeri ON association.KodNegeri = kodnegeri.KodNegeri 
INNER JOIN kategorisukan ON association.KodKategoriSukan = kategorisukan.KategoriSukan
GROUP BY kodnegeri.KodNegeriText, kategorisukan.KategoriSukantext

enter image description here

How can i feed the query directly into google column chart like the one in example below : enter image description here

I understand that the column chart data format requires the first column to be type string and the remaining type number. I've also done some charts that only consisted of two columns so there's no problem.

array('label' => 'Tahun', 'type' => 'string'),
array('label' => 'Jumlah Persatuan', 'type' => 'number')

Can anyone help on how to produce such graph like in the example where it shows for each year (Negeri in my case), there are a few other data (ie : country, whereas in my case is Kategori)

asgallant asgallant · Accepted Answer · 2014-01-17T17:19:25

You need to pivot your data so that each "Kategori" is its own column of data. MySQL doesn't support pivots natively, but you can fake them like this:

SELECT
    kodnegeri.KodNegeriText AS Negeri,
    SUM(IF(kategorisukan.KategoriSukantext = 'Sukan Kecergasan', 1, 0)) AS Sukan_Kecergasan,
    SUM(IF(kategorisukan.KategoriSukantext = 'Sukan Paralimpik', 1, 0)) AS Sukan_Paralimpik,
    SUM(IF(kategorisukan.KategoriSukantext = 'Sukan Prestasi Tinggi', 1, 0)) AS Sukan_Prestasi_Tinggi,
    SUM(IF(kategorisukan.KategoriSukantext = 'Sukan Recreasi', 1, 0)) AS Sukan_Recreasi,
    SUM(IF(kategorisukan.KategoriSukantext = 'Sukan Seni Mempertahankan Diri', 1, 0)) AS Sukan_Seni_Mempertahankan_Diri,
    SUM(IF(kategorisukan.KategoriSukantext = 'Sukan Tradisional', 1, 0)) AS Sukan_Tradisional
    etc...
FROM association 
INNER JOIN kodnegeri ON association.KodNegeri = kodnegeri.KodNegeri 
INNER JOIN kategorisukan ON association.KodKategoriSukan = kategorisukan.KategoriSukan
GROUP BY kodnegeri.KodNegeriText

You will need to add a column for every possible value of kategorisukan.KategoriSukantext (and check over the code to make sure I didn't misspell something).

Feeding data into more than 2 columns in Google Column Chart from MYSQL.

1 Answers