Accessing MySQL database in d3 visualization

Question

I need some help with d3 and MySQL. Below is my question:

I have data stored in MySQL (eg: keywords with their frequencies). I now want to visualize it using d3. As far as my knowledge of d3 goes, it requires json file as input. My question is: How do I access this MySQL database from d3 script? One way which i could think of is:

1. Using Python, connect with database and convert the data in json format. Save this in some .json file.

2. In d3, read this json file as input and use it in visualization.

Is there any other way to convert the data in MySQL into .json format directly using d3? Can we connect to MySQL from d3 and read the data?

Thanks a lot!

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 ( ! $myquery ) {
        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 data for 'dateTimeTaken' and 'reading'. Something along the lines of (and this is only a guess);

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

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

Answer 2

d3 is a javascript library that run on client-side, while MySQL database is supposed to run on server-side.
d3 can't connect to MySQL database, let alone conversion to json format. The way you thought it was possible (steps 1 and 2) is what you should do.