Re: php array json_encode to google charts

[asgallant]

Can you post the javascript that you do have and an example output of your json encoded array?

On Wednesday, July 4, 2012 5:59:53 PM UTC-4, Adam Hardarson wrote:

Hi, 

I have a json_encode array that stores values from selected row in a dropdown.

Now I want to visualize the values from columns 6,8,11,13,16 and 18 using google charts. It's. All the examples i've found don't take me all the way, it's like greek to me :-/

Does anyone here know of any more examples to get me going, the charttype I'm after right now is a simple Column Chart. I've been stuck for days but getting nowhere :(

Take care

Adam

[Adam Hardarson]

Ok, but I havn't gotten anywhere really... :-/

------------------------------------------------------------------ 

if(!empty($_POST['first']) && !empty($_POST['second']) && intval($_POST['first']) !== intval($_POST['second'])){

    $first = $_POST['first'];

    $second = $_POST['second'];

    $ord = ($second <= $first) ? 'DESC' : '';

    $r = mysql_query("SELECT * FROM MCI WHERE Id = $first OR Id = $second ORDER BY Id $ord");

    if(mysql_num_rows($r) == 2){

        while($d = mysql_fetch_array($r)){  

            $arr[] = $d;

        }

        $output = createHTML($arr);

    }else{

        $output = "The values must be different and exist in the list. " . $output; 

    }

}

    $first = $arr[0];

    $second = $arr[1];

$firstdata = json_encode($arr[0]);

$seconddata = json_encode($arr[1]);

------------------------------------------------------------------

My print json looks like this {"0":"7","Id":"7","1":"test","Foretag":"test","2":"44","Variabel_namn_4":"44","3":"34","Variabel_namn_5":"34","4":"64","Variabel_namn_6":"64"}

So I have to format it the right way but I can't figure out how...

If I want to show a simple bar chart with these columns "Variabel_namn_5":"34","Variabel_namn_6":"64" How should I proceed?

Many thanks for taking time helping me out! :)

Cheers

Adam

[asgallant]

I guess a better question is: what do you want your dropdown to list and what do you want to happen when someone selects something in the list?

[Allen Firstenberg]

Adam,

It depends what you want to do, but the easiest solution would be to target a data format specified at https://developers.google.com/chart/interactive/docs/datatables_dataviews

In particular, you might want to look at the arrays that can be fed directly into arrayToDataTable() - the example on that page should show what it needs to look like.

What I've done in this case is to take the associative array in PHP and iterate over all the keys. If the key is a number (tested by PHP's is_numeric() function), then throw it out. Otherwise, create a new array row with values being the key and value from the associative array and add this to the array to return.

Your needs may vary, but this has worked for the things I've tried to do.

Allen

Adam

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.

To view this discussion on the web visit https://groups.google.com/d/msg/google-visualization-api/-/atXAy-CU7aIJ.

To post to this group, send email to google-visua...@googlegroups.com.
To unsubscribe from this group, send email to google-visualizati...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/google-visualization-api?hl=en.

[Adam Hardarson]

Thanks for the reply!

I've looked at how the data should be formatted but I just cant wrap my head around on how to accomplish it!

To better illustrate what I'm going for I put in a spreadsheet: https://docs.google.com/spreadsheet/ccc?key=0AsRWz3wVoWuLdGwtckVHS2k0a09KbkZXc1VCUGR6VFE 

This is my code to get the approriate values from the two dropdowns ($first and $second):

$result124 = mysql_query("SELECT Matningsnamn AS Matning, 

Value1, 

Value2 , 

Value3 , 

Value4   

FROM myDB WHERE Id ='$first' OR Id ='$second'");

 $rows = array();

while($r = mysql_fetch_assoc($result124)) {

    $rows[] = $r;

}

$gn = json_encode($rows);

which results in:

[{"Matning":"blabla","Value1":"100","value2":"88","value3":"84","value4":"16"},

 {"Matning":"blabla 2","Value1":"100","value2":"78","value3":"34","value4":"6}]

I didn't really understand your example, this is all so new to me... :-/

If you look in the spreadsheet on how the graph looks, do you have any ideas on how to make it look like that or further reading suggestions?!

Thank you all for engaging in my silly problem :)

Adam

[Allen Firstenberg]

Given your spreadsheet, you treat rows 11 through 13 as an array of arrays. Each row is an array with the values in that row. You get something like this in JSON:

[

  ['Matn', 'value1', 'value2', 'value3', 'value4'].

  ['blabla', 100, 88, 84, 16],

  ['blabla 2', 100, 78, 34, 6]

]

This can be fed directly into arrayToDataTable() on the javascript side to generate the same table shown in your chart.

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.

To view this discussion on the web visit https://groups.google.com/d/msg/google-visualization-api/-/x5HHlSpyvO0J.

[asgallant]

That won't work to generate the same chart - you need to pivot that array.  Something like this:

$result124 = mysql_query("

SELECT Matningsnamn AS Matning, 

Value1, 

Value2 , 

Value3 , 

Value4   

FROM myDB WHERE Id ='$first' OR Id ='$second'"

);

$rawRows = array();

$cols = array();

// set up domain column

$cols[] = array(

'type' => 'string',

'label' => 'Value #'

);

while($r = mysql_fetch_assoc($result124)) {

$cols[] = array(

'type' => 'string',

'label' => $r['Matning']

);

    $rawRows[] = $r;

}

$outRows = array();

for ($i = 1; i < count($rawRows[0]); i++) {

$temp = array();

$temp[0] = array('v' => "Value$i");

for ($j = 0; j < count($rawRows); j++) {

if ($rawRows[$j]['Matning'] == $cols[1]['label']) {

$temp[1] = array('v' => $rawRows[$j]["Value$i"]);

}

else if ($rawRows[$j]['Matning'] == $cols[2]['label']) {

$temp[2] = array('v' => $rawRows[$j]["Value$i"]);

}

}

$outRows[] = array('c' => $temp);

}

$dataTable = json_encode(array(

'cols' => $cols,

'rows' => $outRows

));

You can then output this into the DataTable constructor:

var data = new google.visualization.DataTable(<?php echo $dataTable; ?>); 

Note that I wrote up the PHP without testing it, so it is quite possible that there are minor syntax errors or typos in it, but the essence of what you need is there.

To post to this group, send email to google-visualization-api@googlegroups.com.
To unsubscribe from this group, send email to google-visualization-api+unsub...@googlegroups.com.

[Allen Firstenberg]

Whoops! Drew is right, I got it sideways.

To view this discussion on the web visit https://groups.google.com/d/msg/google-visualization-api/-/MU-Ir1cx-B8J.

To post to this group, send email to google-visua...@googlegroups.com.
To unsubscribe from this group, send email to google-visualizati...@googlegroups.com.

[Adam Hardarson]

Hi again, thank you so much for your assistance!

However it's not working so I the page for syntax error and got a Parse error: syntax error, unexpected T_INC, expecting ')' in test.php on line 37.

That's where the for loop starts... I can't see what the problem might be?!

Seeing the end of the tunnel... :)

Adam 

[asgallant]

I forgot the $'s in front of the i's there, try this:

for ($i = 1; $i < count($rawRows[0]); $i++) { 

[Adam Hardarson]

That solved that problem but now i get  Table has no columns.×  

if I print the json I get:

{"cols":[{"type":"string","label":"Value #"},{"type":"string","label":"Test 1"},{"type":"string","label":"Test 2"}],

"rows":[{"c":[{"v":"Value1"},{"v":"61"},{"v":"66"}]},{"c":[{"v":"Value2"},{"v":"90"},{"v":"83"}]},{"c":[{"v":"Value3"},{"v":"37"},{"v":"71"}]},{"c":[{"v":"Value4"},{"v":"48"},{"v":"84"}]}]} 

For this to work I changed my original code like this:

$result124 = mysql_query("

SELECT Matningsnamn AS Matning, 

Top AS Value1, 

In AS Value2 , 

Pre AS Value3 , 

Int ASValue4   

FROM myDB WHERE Id ='$first' OR Id ='$second'"

So I realize that I wrote that the "columnnames" were Value1, Value2, Value3 and Value 4 but this was just to illustrate. The column names is totally different from that, will that screw the code up or is it easy to edit to display the real names? Or even better is it possible to enter the  labels manually and just getting the values from the database? So that when I choose a new row from the dropdown only the values changes?

Sorry for confusing everything!!!!

Adam

[asgallant]

I changed it up a bit to fit whatever column structure you like:

$sqlCols = array(

'Matning',

'Top',

'In',

'Pre',

'Int'

);

$result124 = mysql_query("

SELECT Matningsnamn AS Matning, 

Top, 

In, 

Pre, 

Int

FROM myDB WHERE Id ='$first' OR Id ='$second'"

);

$rawRows = array();

$cols = array();

// set up domain column

$cols[] = array(

'type' => 'string',

'label' => 'Foo (rename as you like)'

);

while($r = mysql_fetch_assoc($result124)) {

$cols[] = array(

'type' => 'string',

'label' => $r[$sqlCols[0]]

);

    $rawRows[] = $r;

}

$outRows = array();

for ($i = 1; $i < count($rawRows[0]); $i++) {

$temp = array();

$temp[0] = array('v' => $sqlCols[$i]);

for ($j = 0; j < count($rawRows); j++) {

if ($rawRows[$j]['Matning'] == $cols[1]['label']) {

$temp[1] = array('v' => $rawRows[$j][$sqlCols[$i]]);

}

else if ($rawRows[$j]['Matning'] == $cols[2]['label']) {

$temp[2] = array('v' => $rawRows[$j][$sqlCols[$i]]);

}

}

$outRows[] = array('c' => $temp);

}

$dataTable = json_encode(array(

'cols' => $cols,

'rows' => $outRows

));

//.... then in the javascript, use:

var data = new google.visualization.DataTable(<?php echo $dataTable; ?>);  

Make sure you are using a DataTable constructor and not the arrayToDataTable method.  I tried out the JSON string you posted and it works fine for me.

[Adam Hardarson]

Nice work! 

I am however still getting the  Table has no columns.× 

I copied the code from a google example but there is still something wrong! 

This is what the script looks like:

<script type="text/javascript" src="https://www.google.com/jsapi"></script>

    <script type="text/javascript">

      google.load("visualization", "1", {packages:["corechart"]});

      google.setOnLoadCallback(drawChart);

      function drawChart() {

        var data = new google.visualization.DataTable(<?php echo $dataTable; ?>);  

        var options = {

          title: 'MP'

        };

        var chart = new google.visualization.AreaChart(document.getElementById('chart_div'));

        chart.draw(data, options);

      }

    </script>

</head>

take care

Adam

[asgallant]

Is the json string being echo'd properly?  Take a look at the rendered HTML to see (open page, right-click, select "view source" or something similar, depending on your browser).  If it shows up, post the output HTML here.  If it doesn't show up, post your PHP script here.

[Adam Hardarson]

Ok, after fixing somethings on my page I get the graph to show but without any "content". And after digging around I think this is the issue:

{"c":[{"v":"Top"},{"v":"61"},{"v":"57"}]} 

The values (61 and 57) have " " beside them. If I hardcode a variable without the " " it works... 

So, how do I get rid of them? :)

Cheers

Adam

[Allen Firstenberg]

Use the JSON_NUMERIC_CHECK option to json_encode().

See http://php.net/manual/en/function.json-encode.php for prerequisites and details.

Allen

--

You received this message because you are subscribed to the Google Groups "Google Visualization API" group.

To view this discussion on the web visit https://groups.google.com/d/msg/google-visualization-api/-/vYbus56w8LQJ.

To post to this group, send email to google-visua...@googlegroups.com.
To unsubscribe from this group, send email to google-visualizati...@googlegroups.com.

[Adam Hardarson]

Yes, finally!!! You guys have been unbelievably patient and helpful, thank you SO MUCH!!!

Sinceraly

Adam

[asgallant]

JSON_NUMERIC_CHECK is a good basic tool for this, but if you have numbers that you intend to represent as strings, you need to manually typecast your data instead.  This is important in the Google API, as the data type for a cell passed via JSON must match the data type specified in the column's description (ie, if you have a column of type 'string' and you pass {"v":5} for a cell in that column in the json, it will throw an error). 

[Adam Hardarson]

Good to know!

Thank you again :)

/Adam