asked on

HOW TO FETCH DATA FROM MYSQL DATABASE INTO NV.D3 CHART LIBRARY

can some please show me how to use mysql database as data source for nv.d3 chart library. i want to use data from mysql database as values for the chart. please see nv.d3 sample chart, but the values are not from database.

nv.addGraph(function() {
  var chart = nv.models.discreteBarChart()
      .x(function(d) { return d.label })    //Specify the data accessors.
      .y(function(d) { return d.value })
      .staggerLabels(true)    //Too many bars and not enough room? Try staggering labels.
      .tooltips(false)        //Don't show tooltips
      .showValues(true)       //...instead, show the bar value right on top of each bar.
      .transitionDuration(350)
      ;

  d3.select('#chart svg')
      .datum(exampleData())
      .call(chart);

  nv.utils.windowResize(chart.update);

  return chart;
});

//Each bar represents a single discrete quantity.
function exampleData() {
 return  [ 
    {
      key: "Cumulative Return",
      values: [
        { 
          "label" : "A Label" ,
          "value" : -29.765957771107
        } , 
        { 
          "label" : "B Label" , 
          "value" : 0
        } , 
        { 
          "label" : "C Label" , 
          "value" : 32.807804682612
        } , 
        { 
          "label" : "D Label" , 
          "value" : 196.45946739256
        } , 
        { 
          "label" : "E Label" ,
          "value" : 0.19434030906893
        } , 
        { 
          "label" : "F Label" , 
          "value" : -98.079782601442
        } , 
        { 
          "label" : "G Label" , 
          "value" : -13.925743130903
        } , 
        { 
          "label" : "H Label" , 
          "value" : -5.1387322875705
        }
      ]
    }
  ]

}

Open in new window

Chris Stanyon

Sure. It looks like the data for the charts needs to be JSON, and I'm guessing in a particular format. You'd basically connect to your database, retrieve the records, and then encode the results into a format suitable for your Chart.

Here's a very quick demo so get you started:

<?php 
error_reporting(E_ALL);
ini_set('display_errors', 1);
 
$hostname = 'localhost';
$username = 'yourUserName';
$password = 'yourPassword';
$database = "yourDb";

try {
    $dbh = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
    $dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
    $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
} catch(PDOException $e) {
    echo $e->getMessage();
}

// Prepare your DB query
$stmt = $dbh->prepare("SELECT someField AS label, anotherField AS value FROM yourTable");
$stmt->execute();

// Build an object to hold the data
$returnData = new stdClass();
$returnData->key = "Cumulative Return";
$returnData->values = $stmt->fetchAll();

// JSON Encode the data for use in the chart
$chartData = json_encode(array($returnData));

Open in new window

You can now use $chartData for your chart.

Moses Dwana

ASKER

i am a bit confuse. can u please show me where to put thejson_encode(array($returnData)); in the chart template above? pleas just edit the template above let me see how it looks like

ASKER CERTIFIED SOLUTION

Chris Stanyon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Moses Dwana

ASKER

sorry for the late reply. however, i have been unable to get mysql data in the chart using this method. maybe further simplifying the solution or recommending different chart library that allow me to simply add mysql data to it, will help me. my understanding is actually low when it comes to jzon objects. thank you very mush for the support.

Chris Stanyon

No worries. The JSON part of it is very easy - in PHP you just json_encode() an array - it's that simple.

The harder part is connecting to, and retrieveing records from, your DB. Just remember that the DB part runs on the server, and the Javascript part runs on the client. Whichever charting library you run, you will still have to do something similar.

Good luck with your project

Moses Dwana

ASKER

thanks very mush, i was able to get it done!!!!!