Friday, 8 February 2013

Using a MySQL database as a source of data

The following post is a portion of the D3 Tips and Tricks document which is free to download. To use this post in context, consider it with the others in the blog or just download the pdf  and / or the examples from the downloads page:-)

PHP is our friend

As outlined at the start of the book, PHP is commonly used to make web content dynamic. We are going to use it to do exactly that by getting it to glue together our d3,js JavaScript and a MySQL Database. The end result should be a web page that will leverage the significant storage capability of a MySQL database and the ability to vary different aspects of returned data.

If you're wondering what level we're going to approach this at, let me reassure (or horrify) you that it will be in the same vein as the rest of this book. I am no expert in MySQL databases, but through a bit of trial and error and I have been able to achieve a small measure of success. Hopefully the explanation is sufficient for beginners like myself and doesn't offend any best practices :-).


I'm not one to dwell on the command line for too long if it can be avoided (sorry). So in this section you'll see me delving into a really neat program for managing your MySQL database called phpMyAdmin (

As the name would suggest, it's been written in PHP and as we know, that's a sign that we're talking about a web based application. In this case phpMyAdmin is intended to allow a wide range of administrative operations with MySQL databases via a web browser. You can find a huge amount of information about it on the web as it is a freely available robust platform that has been around for well over a decade.

If you have followed my suggestion earlier in the book to install WAMP ( or you have phpMyAdmin installed already you're in luck. If not, I'm afraid that I won't be able to provide any guidance on it's installation. I just don't have the experience to provide that level of support.

Create your database

Assuming that you do have WAMP installed, you will be able to access a subset of its functions from the icon on your system tray in the lower right hand corner of your screen.

 Clicking on this icon will provide you with a range of options, including opening phpMyAdmin.

Go ahead and do this and the phpMyAdmin page will open in your browser.

The page you're presented with has a range of tabs, and we want to select the 'Databases' tab.

From here we can create ourselves a new database simply by giving it a name and selecting 'Create'. I will create one called 'homedb'.

That was simple!

So now on the panel on the left hand side of the screen is our new database. Go on and click on it.

Cool, now we get to create a table. What's a table? Didn't we create our database already?

Ahh yes... Think of databases as large collections of data (yes, I can smell the irony). Databases can have a wide range of different information stored in them, but sometimes the data isn't strictly connected. For instance, a business might want to store it's inventory and personnel records in a database. Trying to mash all that together would be a bit of a nightmare to manage. Instead, we can create two different tables of information. Think of a table as a spreadsheet with rows of data for specific columns. If we want to connect the data at some point we can do that via the process of querying the database.
So, lets create a table called data2 with three columns.

I've chosen data2 as a name since we will put the same data as we have in the data2.tsv file in there. That's why there are three columns for the date, close and open columns that we have in the data2.tsv file.

So, after clicking on the 'Go' button, I get the following screen where I get to enter all the pertinent details about what I will have in my table.

I'm keeping it really simple by setting the 'data' column to be plain text (I make the presumption that it could be a date format, but as it gets parsed into a date/time value when its ingested into D3, I'm fairly comfortable that we can get away with formatting it as 'TEXT'), and the two numeric columns to be decimals with 8 digits overall and 2 of those places for the digits to the right of the decimal point.

The selection of the most efficient data type to maximise space or speed is something of an obsession (as it sometimes needs to be) where databases are large and need to have fast access times, but in this case we're more concerned with getting a result than perfection.

Once entered, you can scroll down to the bottom of that window and select the 'Save' button.
Cool, now you are presented with your table (click on the table name in the left hand panel) and the details of it in the main panel.

Sure it looks snazzy, but there's something missing..... Hmm.....

Ah Ha! Data!

Importing your data into MySQL

So, you've got a perfectly good database and an impeccably set up table looking for some data.
It's time we did something about that.

In the vein of “Here's one I prepared earlier”, what we will do is import a csv (Comma Seperated Value) file into our database. To do this I prepared our data2.tsv file by replacing all the tabs with commas and removing the header line (with date, close and open on it), so it looks like this;


I know it doesn't look quite as pretty, but csv files are pretty ubiquitous which is why so many different programs support them as an input and output file type. (To save everyone some time and trouble I have saved the data.csv file into the D3 Tips and Tricks example files folder (under data)).

So armed with this file, click on the 'Import' tab in our phpMyAdmin window and choose your file.

 The format should be automatically recognised and the format specific options at the bottom of the window should provide sensible defaults for the input. Let's click on the 'Go' button and give it a try.

Woo Hoo!

Now if you click on the browse tab, there's your data in your table!


The last thing that we should do is add a user to our database so that we don't end up accessing it as the root user (not too much of a good look).

So select the 'homedb' reference at the top of the window (between 'localhost' and 'data2').

Then click on the 'Privileges' tab to show all the users who have access to 'homedb' and select 'Add a new user' 

Then on the new user create a user, use the 'Local' host and put in an appropriate password.

In this case, the user name is 'homedbuser' and the password is 'homedbuser' (don't tell).

The other thing to do is restrict what this untrusted user can do with the database. In this case we can fairly comfortably restrict them to 'SELECT' only;

Click on 'Go' and you have yourself a new user.

Believe it or not, that's pretty much it. There were a few steps involved, but they're hopefully fairly explanatory and I don't imagine there's anything too confusing that a quick Googling can't fix.

Querying the Database

OK, are you starting to get excited yet? We're just about at the point where we can actually use our MySQL database for something useful!

To do that we have to ask the database for some information and have it return that information in a format we can work with.

The process of getting information from a database is called 'querying' the database, or performing a 'query'.
Now this is something of an art form in itself and believe me, you can dig some pretty deep holes performing queries. However, we're going to keep it simple. All we're going to do is query our database so that it returns the 'date' and the 'close' values.

We'll start by selecting our 'data2' table and going to the 'Browse' tab.

We actually already have a query operating on our table. It's the bit kind of in the middle that looks like;
FROM `data2`
LIMIT 0, 30
This particular query is telling the database `homedb` (since that's where the query was run from) to `SELECT` everything (`*`) `FROM` the table `data2` and when we return the data, to `LIMIT` the returned information so those starting at record `0` and to only show `30` at a time.

You should also be able to see the data in the main body of the window.

So, let's write our own query. We can ask our query in a couple of different ways. Either click on the 'SQL' tab and you can enter it there, or click on the menu link that says 'Edit' in the current window. I prefer the 'Edit' link since it opens a separate little window which let's you look at the returned data and your query at the same time.

So here's our window and in it I've written the query we want to run.
SELECT `date`, `close` FROM `data2`
You will of course note that I neglected to put anything about the `LIMIT` information in there. That's because it gets added automatically to your query anyway using phpMyAdmin unless you specify values in your query.

So in this case, our query is going to `SELECT` all our values of `date` and `close` `FROM` our table `data2`.

Click on the 'Go' button and let's see what we get.

There we go!

If you're running the query as 'root' you may see lots of other editing and copying and deleting type options. Don't fiddle with them and they won't bite.

Righto... That's the query we're going to use. If you look at the returned information with a bit of a squint, you can imagine that it's in the same type of format as the *.tsv or *.csv files. (header at the top and ordered data underneath).

All that we need to do now is get out MySQL query to output data into d3.js.

Enter php!

Using php to extract json from MySQL

Now's the moment we've been waiting for to use php!

What we're going to do is use a php script that performs the query that we've just identified to extract data out of the database and to format it in a way that we can input it into D3 really easily. The data format that we're going to use for presenting to D3 is json (JavaScript Object Notation). You might remember it from the earlier chapter on types of data that could be ingested into D3.

Our php script is going to exist as a separate file which we will name `data2.php` and we will put it in a folder called `php` which will be in our webs root directory (alongside the `data` directory).

Here's the contents of our data.php file;
    $username = "homedbuser"; 
    $password = "homedbuser";   
    $host = "localhost";
    $server = mysql_connect($host, $username, $password);
    $connection = mysql_select_db($database, $server);

    $myquery = "
SELECT  `date`, `close` FROM  `data2`
    $query = mysql_query($myquery);
    if ( ! $query ) {
        echo mysql_error();
    $data = array();
    for ($x = 0; $x < mysql_num_rows($query); $x++) {
        $data[] = mysql_fetch_assoc($query);
    echo json_encode($data);     
It's pretty short, but it packs a punch. Let's g through it and see what it does.

The `<?php` line at the start and the `?>` line at the end form the wrappers that allow the requesting page to recognise the contents as php and to execute the code rather than downloading it for display.

The following lines set up a range of important variables;
    $username = "homedbuser"; 
    $password = "homedbuser";   
    $host = "localhost";
Hopefully you will recognise that these are the configuration details for the MySQL database that we set up. There's the user and his password (don't worry, because the script isn't returned to the browser, the browser doesn't get to see the password and in this case our user has a very limited set of privileges remember). There's the host location of our database (in this case it's local, but if it was on a remote server, we would just include it's address) and there's the database we're going to access.

Then we use those variables to connect to the server...
    $server = mysql_connect($host, $username, $password);
… and then we connect to the specific database;
    $connection = mysql_select_db($database, $server);
Then we have our query that we want to run in a form that we can just paste into the right spot and it's easy to use.
    $myquery = "
SELECT  `date`, `close` FROM  `data2`
I have it like this so all I need to do to change the query I use is paste it into the middle line there between the speech-marks and I'm done. It's just a convenience thing.

The query is then run against the database with the following command;
    $query = mysql_query($myquery);
… and then we check to see if it was successful. If it wasn't, we output the MySQL error code;
    if ( ! $query ) {
        echo mysql_error();
Then we declare the `$data` variable as an array (`$data = array();`) and feed the returned information from our query into `$data` array;
    for ($x = 0; $x < mysql_num_rows($query); $x++) {
        $data[] = mysql_fetch_assoc($query);
(that's a fancy little piece of code that gets the information row by row and puts it into the array)
We then return (`echo`) the `$data` array in json format (`echo json_encode($data);`) into whatever ran the `data2.php` script (we'll come back to this in a minute).

Then finally we close the connection to the server;

That was a little fast and furious, but I want to revisit the point that we covered in the part about echoing the data back to whatever had requested it. This is because we are going to use it directly in our d3.js script, but we can actually run the script directly but opening the file in our browser.

So if you can navigate using your browser to this file and run it (WAMP should be your friend here again) this is what you should see printed out on your screen;


There it is! The data we want formatted as json!

It looks a bit messy on the printed page, but it's bread and butter for JavaScript.

I have included the data2.php file in the examples zip file that can be downloaded from

Getting the data into d3.js

Let's recap momentarily.

We have created a database, populated it with information, worked out how ro extract a sbset of that information and how to do it in a format that d3.js understands. Now for the final act!

And you will find it slightly deflating how simple it is.

All we have to do is take our simple-graph.html file and make the following change;
d3.json("php/data2.php", function(error, data) {
    data.forEach(function(d) { = parseDate(;
        d.close = +d.close;

Here we have replaced the part of the code that read in the data file as `data.tsv` with the equivalent that reads the `php/data2.php` file in as json (`d3,json`).

That's it.

What it does is we tell d3.js to go and get a json file and when it strikes the `data2.php` file, it executes the script in the file and returns the encoded json information directly to d3.js.

How cool is that?

And here is the result.

Sure, it looks kind of familiar, but it represents a significant ability for you to return data from a database and present it on a web page.

The above description (and heaps of other stuff) is in the D3 Tips and Tricks document that can be accessed from the downloads page of


  1. Hi,

    this is very cool.

    Is there any way to make it interactive?

    I mean, say I have a database with sales of apples and orange, to "chose" what I want to see, send the appropriate query to SQL, get back the data, transform it into JSON, and see it as a chart?



  2. Great question. And the answer is a resounding yes!
    For instance, you could have a query that looked for all DISTINCT fruit types in the database and then presented the results in a drop down selection box. Then when one of these is selected by the used, the selection can be passed back to a follow on page in the url (see which is then used to populate the subsequent query (in PHP) to extract historical data for graphing the price of oranges for example.
    This entire process can be made easier using something like bootstrap as a front end (
    This would work really well, but it would be something that may be daunting depending on your level of experience.
    Thanks for the question. It's a good one :-)

  3. Hi! Thanks for the great tutorial! I'm having an an issue getting this to work with my own data, though. I have my own php script that issues the following query:

    $query=mysql_query("select tag_month as date, count(*) as close from lastfm_annotations where item_id=1020 and tag_id=1 group by tag_month;");

    I named the columns to match your code to minimize changes for testing purposes, and this seems to work. Running the script on its own works fine and returns the something like:

    [{"date":"2005-08-01","close":"10"},{"date":"2005-09-01","close":"2"},{"date":"2005-10-01","close":"5"},{"date":"2005-11-01","close":"3"}, ...

    Now, in principle, I would think there are only two lines in your code I need to change. First, since the date format is different, I changed the parseDate line to read:

    var parseDate = d3.time.format("%Y-%m-%d").parse;

    Second, I changed the getData line to read (my php file is in the same directory as the html file):

    d3.json("getData.php", function(error, data) {
    data.forEach(function(d) { = parseDate(;
    d.close = +d.close;

    I would think everything else could stay identical and I would get a nice line plot, but I'm not getting anything except unlabeld axes! (no labels, no line, nothing).

    Any idea what might be going one here? I'd really appreciate any help you can offer.


  4. Hi Jarred. That looks pretty good to me. I can't pick the problem. Your JSON looks correctly formatted and the parseDate specifiers are correct for your date. Are you able to post your code onto github or google groups? Hmm... Perhaps first I could reccoment having a play with the debugging console on Google Chrome? ( Although I haven't added it to the book yet, this is a great way to debug code and is WELL worth having a play with. The best way to start to use it is to start with a working web page and then introduce a known error and see how the Console picks it up. Then once you know what you're looking for (how the errors are reported) use it on your code. Give it a try.
    However, I can only assume that it's something really simple that has crept into your code, because it looks good from here.
    Good luck

  5. Oops! Found the problem. I think I was using ds.tsv instead of ds.json. It's working now. Thanks for the help (reading through your book now, and it's awesome!)

    1. Well done on working it out!
      Glad you'r enjoying the book and enjoy D3!

  6. Am having problems to reconvert your "chart template" to this one, so I can apply same step-by-step like yours (couse its georgeous simple!!)

    (Source here)

    (Demo here)

    Any ideas?
    Happy regards,
    from Barcelona!

  7. I posted the issue on Stackoverflow!
    Here the link,

    Any ideas?

    Am waiting a answer from ESeufert on GitHub, for some ideas to.

    Best regards,
    From Barcelona

    1. Hi Jan. Sry I've been away from home for a while. Your question looks really interesting. I'll need to sit down and have a good think about it before responding, but I see it's gathering some interest on Stack Overflow already! Well done.Good work laying out your question so well!

  8. Hi, I think d3 is amazing and has some brilliant alternatives to highcharts.
    I am using this chart from the data.tsv all is perfect...

    but I want to pull a json feed in so to keep the data fresh and pass variables to change the data based on user requirements.

    [sample of my json]

    i tried replacing the d3.tsv section but no luck any suggestion or help would be much appreciated.

    1. That's a really elegant chart!
      Your json *looks* properly formatted, so I can only imagine that there might be a syntax problem loading it. Just do a double check of the location that your json is and of the path that your d3.json command is pulling from (I know that sounds simplistic, but I've done sillier things in the past). Failing that if you create a fork of the original chart on gist / github and alter it to suit, we could walk through it there.

    2. Hi thank you for getting back to us have hosted my code up here

    3. Ok, the first thing I note is that you're trying to parse the 'days' variable here 'd.days = parseDate(;' but the variable you're loading is 'day' and you don't have a 'parseDate' function anywhere.
      There may be other issues, but this would be a good start to sort out.
      If I could make a recommendation, revert to as close to the original project you started with as possible ( and then use the output from your 'api.php' call (as json) in a seperate file called api.json and then load this file using the d3.json function. If your json is correct, you should get a result. Even if you pare the data down to just a couple of data points to just get it to display something, that should be a good starting point.
      Another good trick that I like to use is to put an 'echo json_encode($whatevermydatascalled)' in my php file after I've extracted the data. That should print it directly to the screen of the browser and may provide a clue to the function of the php script. See how those ideas work out. Good luck.

    4. Fantastic thank you for you help, I am graphic designer been pushed in to analytics and web work :s so really appreciate your help,

    5. No problem. I hope it works out.

  9. Hi there!
    I used simple-graph.html with my own php file on XAMPP but it does not work. However, it does display the graph if I provide a json file instead of the php file. But I find no problem with the php. It echoes in json format like so: [{"date":"1-May-12","close":"43.32"},{"date":"30-Apr-12","close":"22.54"},{"date":"27-Apr-12","close":"21.54"},{"date":"25-Apr-12","close":"21.42"}]
    My php code is this:
    query("SELECT date, close FROM testable");
    echo json_encode($result_graph->fetchAll(PDO::FETCH_ASSOC));
    Could someone please suggest what might be the problem?

    1. The data looks good. The only thing I might suggest could be the code when you load it. Could you post your code and a short description of the problem onto Stack Overflow? Then post a link here and I'm pretty sure we can sort it.

  10. Thank you! It's here:

    1. Magic, I see people are climbing in to provide assistance already!

  11. Hi,

    Jut want to say thanks. Really simple and quick to follow. Whole process took 10 minutes. Totally ace work.

    One question ... so I want to make a tree chart(as Obviously this is fed by a json of a set format. Is there anywhere where I can get a php script similar to your above that can import from a MySQL in the correct format?

    thanks again for your ace work

    1. I haven't come across one, but it would be possible to craft one in PHP so that the script that queried the database built the format for you as well. Sorry I don't have any examples though.

    2. Hi,

      So I've gone ahead and done that. json_encode output validates to be correct. But I've got no idea of the correct d3.json config for a tree view, or of the correct json parameters for a tree view.

      how do I find this stuff out?

    3. Ahh! Now, I haven't published any work on this, but I did have a look at it a long time ago. From memory, I found working from Mike Bostocks example here ( really good ((view the page source or version here ( And use this in conjunction with the json layout from his example here (view-source: The key is to understand the way that the dat is nested. To really learn how it works, pare his example json down to only a few entries and make sure the example still works, then add in some more data. It might help to do this in conjunction with the post here on JSON (

  12. Dear pal,
    I check ur D3 js code . But for me it show a blank page . So I try it
    with this format too

    <!DOCTYPE html>
    <meta charset="utf-8">
    <script src="d3.v3.min.js"></script>
    function draw(data) {
    data.forEach(function(d) {
    d.post_title = d.post_title;
    d.post_content = +d.post_content; });
    <title>Untitled Document</title>

    d3.json("data2.php", draw);

    still same result. The Json value is passed properly . please advise what my problem.


    1. Hi Anes,

      From the code you've posted, I notice that your 'draw' function doesn't select any elements or append them to the web page as objects. If you're not sure what I'm talking about there, I really recommend that you take some time to work your way through the book ( You will need that background to make more advanced concepts work the way you want. When you get that part sorted out, the best way to get assistance if you strike problems is to post your question along with your code and sample data onto Stack Overflow ( There are a huge number of experts there who will be only too keen to provide guidance. I would certainly not be as clever as them or have as much time as I would like to try and solve your problem as it stands sorry. Good luck.

    2. Dear D3noob,
      Thanks, I also check your code too initially it also fails in my


    3. Ahh... Definitely something odd going on then. I see you've posted to the d3.js Google Group with a question. Good work. Hopefully one of the bright folks that hang out there can assist, but keep Stack Overflow in mind in case it doesn't work out.

  13. Hi, I'm new to d3. I wanted a help to convert MySQL query to hierarchical data in the parent child relationship. I'm trying out the example Zoomable Partition Layout ( Please help me on the same.

    1. Hi there,
      Great to see that you've raised the question on Stack Overflow. That's the perfect place to get the best response. If I could help you get the best result, I would suggest that you edit your Stack Overflow question to include some sample data and perhaps even the php / mysql query. Good going.

  14. Shouldn't the variable be "query" instead of myQuery inside the if-statement of your data.php? The if-statement does nothing because the query variable is always defined.

    1. Holy Moley! You're right! Good spotting. As an additional cool factoid, none of the other 12,318 page views on this page picked it up and reported it! Many thanks. I'll get that corrected in the book as well :-)

    2. Righto. Edits made in blog and book. It's just updating on-line now and a corrected version will be available to the world in about 20 minutes. (I really love the way lean publishing works :-)). Thanks again

  15. Hello, first of all – great tutorial. I got this working in less than 5 minutes (already know SQL). But I am trying to access a dataset with over 750,000 rows, and it seems like this can't handle that many rows. I've tested it at 30, 300, 3,000, etc using LIMIT, but it can never get as high as I need it. Any advice?

    1. Good work getting up and going. I find it very convenient working with MySQL data and d3.js.
      However, you have found a bit of a problem with limiting the amount of data that d3.js can reasonably handle. From memory, there may be three things working against you:
      1. There may be a limit associated with the transfer of data via php (My memory is REALLY shaky here)
      2. Depending on the type of data, your browser may not be able to support it.
      3. D3.js may be struggling because 750,000 is a LOT of data points to deal with..
      From memory there are techniques to deal with this like reducing the data with a map/reduce function (Mike Bostock had something published from memory) and perhaps using the queue.js script to manage the data flow.
      Sorry it's a bit vague, but I haven't really pursued this sort of problem in depth.
      Good luck.

  16. Thank you a lo D3noob for everything I've learnt from you.

    I've posted a question fully related to this tutorial in the point I've been unable to continue.

    Any help would be appreciated. Thanks in advance.

  17. Thanks a lot D3noob, it is a great tutorial. However I have been unable to continue.
    My database.php successfully displayed json formate [{"id":"123","year":"10","gdp":"20"},{"id":"3","year":"10","gdp":"20"},{"id":"4","year":"24","gdp":"20"}]

    Here are javascript code:



    However, my console shows that console.log(data) undefined. Can you help me look at it my problem, thanks.

    1. If your getting the error 'console.log(data) undefined' then I can only presume that the data is not getting loaded correctly. Looking at the address of the php file you are trying to load it from (/var/www/database.php) it seems possible that you might be putting too much information in there. If your database.php file is in the same directory as the file that you're loading in the browser, just have ''d3.json("database.php",function(error,data){" instead.if both files are in the same directory, it should work. Good luck

    2. Thanks for you quick reply, I found what my problem is. Since I open port 8000 for d3.js and my index.html, when I try to open http://localhost:8000/var/www/database.php in my browser, it just start to download this php file. The browser shows data in json formate, when I open /localhost/database.php. I am pretty new to php and apache, do you know how to setup php or apache to be able to open php in all ports?

    3. Well done on sorting it out. Sorry, I have no idea about setting up PHP or Apache.

  18. What is a good reference to generate the json with multiple embedded time series of data e.g. The Wealth and Health of Nations dataset nations.json?

    1. Wow, that's a really good question. Sadly I don't know. I haven't seen that visualization before (thanks) and it is a beauty! Sorry I don't have a good answer for you. Mike may be able to respond (but bear in mind he is frightfully busy). Good luck