Using d3 with a mySql database

Creating visualizations from static files is fine and dandy but sometimes you need to be able to access dynamic data. And some other times, you may want to somehow record interactions from your users. One way to do that is by interacting with a mySql database.

Without further ado here is the demo:

How does it work?

There are several parts to that.

First, one html file which holds everything together. By the way, for the styling I used Twitter’s bootstrap which makes it easy for all elements to find their place, and look at those purty buttons.

Second, one javascript file which contains the visualization proper.  If you have some familiarity with d3, there is really nothing scary in this script. I’ll go back to the parts where the script interacts with databases in detail.

Here’s what the rest does at a high level.

  1. We give some behaviors to the buttons
  2. Then we create a grid of small squares. All of these squares are positionned and given a class name, so that the square with class “r32″ and “c17″ is the 18th square from the left and 33th from the top (the class names start at 0).
  3. We catch the clicks on each square with a “clickme” function. In d3 logic, what is passed to that function is the underlying data of the element, in this case a 2-dimensional array with the x and y coordinates of the square which is being clicked on. In turn, the clickme function is going to update the data of the square, and those of the 4 surrounding squares (the one to the top, the bottom, the left and the right) by either increasing or decreasing the elevation of the terrain they represent

When it gets interesting is how the data is initialized and how it is updated.

d3.text("mapread.php", function(txt) {
	d3.selectAll("#loading").remove();
	txt.split("\n").forEach(function(line,i) {
		line.split(",").forEach(function(d,j) {
			data[i][j]=parseFloat(d);
			d3.selectAll(".r"+i+".c"+j).style("fill",function() {return cScale(data[i][j]);});
		})
	});
})

What’s really interesting here is the first line. We are asking d3 to go fetch a text file sitting at mapread.php, then do something with this file. The second part of the line, function(txt), calls a function with the contents of this text as argument.

The second line just removes the loading message box. Then, d3 splits the text in lines, and each line being a string of comma-separated values, it splits that too and feeds a variable, data, with the result of all of this splitting. Then, it formats the squares by coloring them according to the retrieved values.

At this stage you may think: but shouldn’t you load the data before drawing the scene? Well, what happens here is that loading the data takes much more time than drawing the scene, so it makes more sense to draw it first as an empty shell, load the data and then update the scene.

And as you may have guessed, this mapread.php is no ordinary text file, but a dynamically-generated file from a mySql database. I won’t cover setting up a mySql database. Tutorials on the subject abound, there are ISPs that offer free mySql hosting, and if you can also install a local server on your computer, for instance EasyPHP for windows users. And, if your ISP limits the number of mySql databases you can have, you don’t need to create a new one, just creating a new table within one will be fine. All you have to do really is find your mySql credentials.

Next, you want to create a PHP file that goes like this:

<?php
$username="username"; //replace with your mySql username
$password="password";  //replace with your mySql password
$dsn="database";  //replace with your mySql database name
$host="host";  //replace with the name of the machine your mySql runs on
$link=mysql_connect($host,$username,$password);
?>

You can call this: mysqlConfig.php or whatever, this  is a convenience file so you don’t have to type in your credentials each time you need to connect to your mySql database.

Next, here is the script that reads the database and outputs a text file:


<?php
// load in mysql server configuration (connection string, user/pw, etc)
include 'mysqlConfig.php';
// connect to the database
@mysql_select_db($dsn) or die( "Unable to select database");

// reads the map db

$query="SELECT `height` FROM `v_map` ORDER BY `row`, `col`";
mysql_query($query);

$result = mysql_query($query,$link) or die('Errant query: '.$query);

// outputs the db as lines of text.
header('Content-type: text/plain; charset=us-ascii');
$i=0;
$line="";

if(mysql_num_rows($result)) {
 while($value = mysql_fetch_assoc($result)) {

$line=$line.$value["height"];
 $i=$i+1;
 if ($i==52) {
 $i=0;
 echo $line."\n";
 $line="";}
 else {$line=$line.",";}
 }
}
mysql_close();
?>

And by the way, I am by no means a php expert. I hadn’t written a line of php in almost 10 years, so there may well be more effective ways to do that but the above works. The more interesting part is that we write an sql query which we store in $query and then we execute this query. Then, we loop over the results and echo the output.

Back to our javascript file, we also interact with another php file when we update the data.

function update(r,c,v) {
	if(r>=0 && r<y && c>=0 && c<x) {
		data[r]1=d3.max([d3.min([100,data[r]1+v*build]),0]);
		d3.selectAll(".r"+r+".c"+c).style("fill",function() {return cScale(data[r]1);});
		d3.text("mapupdate.php?height="+data[r]1+"&col="+c+"&row="+r,function() {console.log("cell on row "+r+" and col"+c+" updated to "+data[r]1);});
	}
}

Here the last line is the interesting one. What it does is that, again, it attempts to fetch a text file from a url. In fact, there is no text there but just accessing this url will trigger an interaction with the database. (I guess it would be good practice to actually get some text in return, but hey).

The program tries to read an url of the form mapupdate.php?height=20&col=10&row=32. By calling this url, we are actually passing these parameters to a php file, which will read them and use them to construct a query to the mySql database.

Here goes:

<?php

// load in mysql server configuration (connection string, user/pw, etc)
include 'mysqlConfig.php';
// connect to the database
@mysql_select_db($dsn) or die( "Unable to select database");

// updates the map db

$query="UPDATE `v_map` SET `height`=".$_GET["height"]." WHERE `col`= ".$_GET["col"]." and `row`= ".$_GET["row"];
mysql_query($query);
mysql_close();
?>

Here, the line that starts with $query is doing just that. The dot “.” is PHP concatenation operator, and the $_GET variable returns an associative array with the parameters passed to the script.

For the sake of completeness, I had two other php scripts, one to initiate the table to begin with, and one to reset it if something went wrong. Those are just plain SQL queries so no need to reproduce them here.

And voilà! now all of you can interact with this terrain builder, create islands, forests, mountains etc. The graphics are kind of crude, because when I was looking for an example I decided to recreate one of my earliest attempts in creative coding. In 1990 upon the release of Powermonger I was so fascinated by the algorithmically-generated maps the game used as copy protection that I tried to code my own terrain generator, that was a time where 320x240x16 resolution was considered generous. Only here, it’s your clicks that replace the algorithm!

I hope you enjoy the tutorial and working with persistant data with d3!

 

9 thoughts on “Using d3 with a mySql database

  1. Jerome -

    Very interesting, and look forward to experimenting with MySQL connections to d3. The d3 charts are exceptional, but static data makes for a lot of work to create multiple visualizations.

    Thanks for your work!

  2. Jerome,

    This is probably a bit advanced from where you are at the moment, but it would be worth looking at Backbone.js as a pseudo MVC framework to handle updates to data and subsequent rendering of views in Html and D3.

    A simple example of where such a framework may start to be useful is in a long lived polling/websocket for social media visualisations.

    Slippy map style visualisations are another area where data updates based on drags and subsequent bounds setting would be useful.

    Also – In terms of size of data payload that was mentioned in the google groups for data, if you can configure it on your web server, you could gzip the data files and push them back to the client that way thus reducing data sent.

    Ben

  3. Hi Ben, thanks for the tip, especially since I’m considering doing this type of map some time soon, the dataset I’m looking at has 450k entries so loading them all in memory just won’t do…
    re the other problem, I hadn’t tried this, since the data file was generated with python I tried using pickle but the size gains were really minimal. I’ll see if it helps!

  4. Also Jerome, with most if the custom slippy map libraries e.g. Leaflet.js, polymaps etc. you can get the bounds of the visible map region and fire an ajax request for new data for the region/tile each time a drag happens. This means you should be able to only load the data points you need for your geo-spatial map on demand. Shoot me a mail if you need some code samples closer to the date.

  5. Thank you for the link to sqlmap.css. Is it possible to get the data points so that I can create a mysql database in order to duplicate the example on my machine?

  6. Hi, This is very nice. I got stuck with the php code. Any chance you could tell me how to connect to an ms sql or oracle database? Has this advanced at all in the last year?

Leave a Reply