load mysql to json for NVD3

Question

    <?php
    $username = "root"; 
    $password = "pw";   
    $host = "localhost";
    $database="dbname";

    $server = mysql_connect($host, $username, $password);
    $connection = mysql_select_db($database, $server);

    $myquery = "
    SELECT  `name`, `useTime`, `e_usage` FROM `electric_usage`
    ";
    $query = mysql_query($myquery);

    if ( ! $query ) {
        echo mysql_error();
        die;
    }

    $data = array();

    for ($x = 0; $x < mysql_num_rows($query); $x++) {
        $data[] = mysql_fetch_assoc($query);
    }

    echo json_encode($data);     

    mysql_close($server);
?>

I use upper php source to load mysql to JSON

the result of php page is

[{"name":"LED STAND","useTime":"2015-09-10 14:17:33","e_usage":"0.0599970581514709"},
{"name":"LED STAND","useTime":"2015-09-10 14:20:33","e_usage":"1.10919825898689"},
{"name":"LED STAND","useTime":"2015-09-10 14:23:33","e_usage":"1.9208018439918"}]

how to change this format to like this(for nvd3 line graph)?

function cumulativeTestData() {
        return [
            {
                key: "LED STAND",
                values: [ [ 2015-09-10 14:17:33 , 2.974623048543] ,
                [ 2015-09-10 14:20:33 , 1.7740300785979]]
            }
}

or theres easy way to make NVD3 line graph from mySql DB?

edit :

 d3.json("./mysqljson/dbread.php", function(data) {
        var dataLoad =  d3.nest()
                        .key(function(d){return d.name})
                        .rollup(function(u){
                            return u.map(function(x){return [x.useTime,x.e_usage]})})
                        .entries(data);


    nv.addGraph(function() {
    var chart = nv.models.multiBarChart()
                  .x(function(d) {return d[0]})
                  .y(function(d) {return d[1]})
                  .margin({top: 50, bottom: 30, left: 40, right: 10});

    chart.xAxis
        .tickFormat(function(d) {
        return d3.time.format('%x')(new Date(d))
    });

    d3.select('#mainExample')
      .datum(dataLoad)
      .transition().duration(500)
      .call(chart);

    nv.utils.windowResize(chart.update);



    });

working code to use d3.json and d3.nest function

score 2 · Accepted Answer · answered Sep 21 '15 at 07:53

2

You can achieve that by using d3's d3.nest() method as follows:

Assuming your data structure as you received it using d3.json is data, then if you do:

x=d3.nest()
    .key(function(d){return d.name})
    .rollup(function(u){
        return u.map(function(x){return [x.useTime,x.e_usage]})})
    .entries(data);

Then the result would be:

{
  key = "LED STAND"
  values =  [
         ["2015-09-10 14:17:33", "0.0599970581514709"], 
         ["2015-09-10 14:20:33", "1.10919825898689"], 
         ["2015-09-10 14:23:33", "1.9208018439918"]
  ]
}

I hope this helps.

answered Sep 21 '15 at 07:53

Nikos

3,267
1
25
32

can you look my edit source? i got some trouble at d3.nest function – user2637015 Sep 21 '15 at 08:35
No, you have to put the `d3.nest()` part inside the `d3.json` method, immediately after `dataLoad=data`. The `d3.json` makes an AJAX call, and the part inside runs when the data is fetched. You run the `d3.nest()` without waiting for the data to arrive. – Nikos Sep 21 '15 at 08:37
ok i undertand that. more question is draw graph also need put inside d3.json function? – user2637015 Sep 21 '15 at 08:47
Either put the drawing code within the `d3.json`, or create a method eg `draw(data)`, that you execute from `d3.json` once you get the data. Otherwise, there will be nothing to draw. – Nikos Sep 21 '15 at 08:49
really thanks about your help every thing work fine. i add working code :) – user2637015 Sep 21 '15 at 08:50

load mysql to json for NVD3

1 Answers1