CODEWITHSUNDEEP
mysqljsond3.jsvisualization
BaRd
BaRd

Reputation: 127

How to pull data from mysql database and visualize with D3.JS?

I have a database in MySQL which I want to visualize in D3.JS. In order to do that, first I want to parse the data in JSON format, then write a basic code which pulls data from database and visualize with D3.JS. I look around but couldn't find what I want since I'm new to D3.JS.

How can I achieve that? Any help or clue appreciated.

Upvotes: 9

Views: 31061

Answers (6)

RezaNikfal
RezaNikfal

Reputation: 1013

You may use jQuery which is more popular as follows:

$.getJSON( "ajax/test.json", function( data ) {
console.log(data);
}

Upvotes: 0

Kevin Weinrich
Kevin Weinrich

Reputation: 79

To get d3.json to work, I had to call it the following way (my PHP file generates the JSON data):

d3.json("InventoryData.php").then(function(data){
  console.log(data);
});

Upvotes: 0

d3noob
d3noob

Reputation: 2151

The following is a php script that you should be able to save somewhere as a file (let's say you call it 'getdata.php') accessible from your HTML file with your D3 code in it. When called it will return data from your MySQL database in a json format (so long as the database server isn't outside your domain);

<?php
    $username = "******"; 
    $password = "******";   
    $host = "******";
    $database="***dbase_name***";

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

    $myquery = "
    query here
    ";

    $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);
?>

Obviously you would need to enter appropriate details for username, password, host and database. You would also need to include an appropriate query for your data so that it returned what you were looking for. Something along the lines of (and this is only a guess);

SELECT `dateTimeTaken`, `reading` FROM `tablename`

Which would return a list of time stamps and values from a table called tablename with columns called dateTimeTaken and reading. Then when you go to read in your json file you would use the following syntax for the code where you would be reading in your json;

d3.json("getdata.php", function(error, data) {

Hopefully that's close to what you're looking for. I've tested it locally and it all seems to work..

I've put together a post to go over local installation of a simple WAMP server and setting up a query on the MySQL database from d3.js here http://www.d3noob.org/2013/02/using-mysql-database-as-source-of-data.html

This is pretty much the same situation as Accessing MySQL database in d3 visualization

Upvotes: 14

Dan
Dan

Reputation: 701

I have faced problem myself with json_encode, in the end I wrote my own code instead of json_encode. Just you need to set the sql and yoru connection info. Hope you find it useful.

$conn = new mysqli(DB_Host, DB_User, DB_Password, DB_Name) or die("Connection failed: " . $conn->connect_error);
$sql = "select  AID as id, deDate_Value as date, nterTime AS counter 
        from xxxx";

//Get the Date and store it in array
$records = array();
if ( $result=mysqli_query($conn,$sql) )
    while ( $obj=mysqli_fetch_object($result) )
        $records[] = $obj;

//echo in json format on screen       
echo "[";
$comma_1 = "";
foreach($records as $obj)
{
    echo $comma_1."{";
    $comma_2 = "";
    foreach($obj as $key => $value)
    {
        echo $comma_2.'"'.$key."\": \"". $value . "\"";
        $comma_2 = ", ";
    }
    echo "}";
    $comma_1 = ", \n";
}

Upvotes: 1

Marjancek
Marjancek

Reputation: 238

Short answer: Web Service.

Basically, you'd want to make a web service that will return json data (for instance) to connect to d3.json() calls.

I would recommend you to use Python to quickly do something extending SimpleHTTPServer, or go with a web service framework such as web2py.

Upvotes: 7

Rich O&#39;Kelly
Rich O&#39;Kelly

Reputation: 41767

AFAIK there aren't any libraries that allow javascript to connect to a MySQL database directly. Instead consider exposing your data via a web service (using any number of server side technologies: ASP.Net, PHP, Ruby on Rails etc) and serialise your data to JSON in the response

Upvotes: 1

Related Questions