How to show JSON in datatable format?

Question

I want to create a Google graph from some data in my database, now I have this code:

<?php
$q = mysql_connect('localhost', 'xxx', 'xxx');
mysql_select_db('xxx', $q);
?>
<?php
$sth = mysql_query("SELECT inkoop, verkoop FROM prijs WHERE inkoop >= 1 ");
$rows = array();
while($r = mysql_fetch_assoc($sth)) {
    $rows[] = $r;
}
print json_encode($rows);
?>
<?php
$sth = mysql_query("SELECT tijd FROM prijs WHERE inkoop >= 1");
$cols = array();
while($c = mysql_fetch_assoc($sth)) {
    $cols[] = $c;
}
print json_encode($cols);
?>

And it gives me this output:

[{"inkoop":"516","verkoop":"484"},{"inkoop":"515","verkoop":"488"},{"inkoop":"515","verkoop":"487"}][{"tijd":"2013-12-17 23:45:00"},{"tijd":"2013-12-17 23:48:00"},{"tijd":"2013-12-17 23:52:00"}]

What I need is something like this, to create a line graph:

{
  "cols": [
        {"id":"","label":"Topping","pattern":"","type":"string"},
        {"id":"","label":"Slices","pattern":"","type":"number"}
      ],
  "rows": [
        {"c":[{"v":"Mushrooms","f":null},{"v":3,"f":null}]},
        {"c":[{"v":"Onions","f":null},{"v":1,"f":null}]},
        {"c":[{"v":"Olives","f":null},{"v":1,"f":null}]},
        {"c":[{"v":"Zucchini","f":null},{"v":1,"f":null}]},
        {"c":[{"v":"Pepperoni","f":null},{"v":2,"f":null}]}
      ]
}

Edit: I need a line graph with 2 lines one for 'inkoop' and one for 'verkoop', if I can use something like this link it would be even better.

Thanks in advance!

possible duplicate of [Pretty-Printing JSON with PHP](http://stackoverflow.com/questions/6054033/pretty-printing-json-with-php) — Pekka, Dec 17 '13 at 23:06
Thanks for your response, but i need rows and cols, this only prints it prettier.. — Mwisho, Dec 17 '13 at 23:20

score 0 · Accepted Answer · answered Jan 17 '14 at 15:54

I'm not sure I completely understand your question, but it sounds like you already have the object, you just need them formatted a little bit better so you can put them into your charting software?

If that's what you're wanting, I think you're already on the right track. I'd create a new object at the beginning of the script with:

$json = new stdClass();

and then as you get your $rows and $cols arrays set, assign them to the object:

$json->rows = $rows;
...
$json->cols = $cols;

and when you json_encode($json); it should look something like your example.

If you're needing to do additional manipulation to the data from SQL before it's in your ideal form, it should all by doable by creating objects and adding properties to them.

I hope this helps!

How to show JSON in datatable format?

1 Answers1