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

Question

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.

Answer 1

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