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 :-).

phpMyAdmin

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 (http://www.phpmyadmin.net/home_page/index.php).

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 (http://www.wampserver.com/en/) 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;

1-May-12,58.13,34.12
30-Apr-12,53.98,45.56
27-Apr-12,67.00,67.89
26-Apr-12,89.70,78.54
25-Apr-12,99.00,89.23
24-Apr-12,130.28,99.23
23-Apr-12,166.70,101.34
20-Apr-12,234.98,122.34
19-Apr-12,345.44,134.56
18-Apr-12,443.34,160.45
17-Apr-12,543.70,180.34
16-Apr-12,580.13,210.23
13-Apr-12,605.23,223.45
12-Apr-12,622.77,201.56
11-Apr-12,626.20,212.67
10-Apr-12,628.44,310.45
9-Apr-12,636.23,350.45
5-Apr-12,633.68,410.23
4-Apr-12,624.31,430.56
3-Apr-12,629.32,460.34
2-Apr-12,618.63,510.34
30-Mar-12,599.55,534.23
29-Mar-12,609.86,578.23
28-Mar-12,617.62,590.12
27-Mar-12,614.48,560.34
26-Mar-12,606.98,580.12

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!

Sweet!

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.
Yay!

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;
SELECT *
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!

[Edit: From the future] - Just be aware that while this was written with the best intentions in 2013, it would be better practice to implement it with mysqli or PDO. Have a Google!

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;
<?php
    $username = "homedbuser"; 
    $password = "homedbuser";   
    $host = "localhost";
    $database="homedb";
    
    $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();
        die;
    }
    
    $data = array();
    
    for ($x = 0; $x < mysql_num_rows($query); $x++) {
        $data[] = mysql_fetch_assoc($query);
    }
    
    echo json_encode($data);     
     
    mysql_close($server);
?>
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";
    $database="homedb";
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();
        die;
    }
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;
    mysql_close($server);
Whew!

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;

[{"date":"1-May-12","close":"58.13"},{"date":"30-Apr-12","close":"53.98"},
{"date":"27-Apr-12","close":"67.00"},{"date":"26-Apr-12","close":"89.70"},
{"date":"25-Apr-12","close":"99.00"},{"date":"24-Apr-12","close":"130.28"},
{"date":"23-Apr-12","close":"166.70"},{"date":"20-Apr-12","close":"234.98"},
{"date":"19-Apr-12","close":"345.44"},{"date":"18-Apr-12","close":"443.34"},
{"date":"17-Apr-12","close":"543.70"},{"date":"16-Apr-12","close":"580.13"},
{"date":"13-Apr-12","close":"605.23"},{"date":"12-Apr-12","close":"622.77"},
{"date":"11-Apr-12","close":"626.20"},{"date":"10-Apr-12","close":"628.44"},
{"date":"9-Apr-12","close":"636.23"},{"date":"5-Apr-12","close":"633.68"},
{"date":"4-Apr-12","close":"624.31"},{"date":"3-Apr-12","close":"629.32"},
{"date":"2-Apr-12","close":"618.63"},{"date":"30-Mar-12","close":"599.55"},
{"date":"29-Mar-12","close":"609.86"},{"date":"28-Mar-12","close":"617.62"},
{"date":"27-Mar-12","close":"614.48"},{"date":"26-Mar-12","close":"606.98"}]

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 d3noob.org.

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) {
        d.date = parseDate(d.date);
        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 d3noob.org.

131 comments:

  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?

    thanks

    fabio

    ReplyDelete
  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 http://www.html.net/tutorials/php/lesson10.php) 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 (http://twitter.github.com/bootstrap/).
    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 :-)

    ReplyDelete
  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) {
    d.date = parseDate(d.date);
    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.

    Thanks,
    /Jared

    ReplyDelete
  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? (https://developers.google.com/chrome-developer-tools/docs/console) 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

    ReplyDelete
  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!)

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

      Delete
  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) https://github.com/novus/nvd3/blob/master/examples/crossfilterWithTables.html

    (Demo here)
    http://www.discoelmas.com/master/examples/crossfilterWithTables.html

    Any ideas?
    Happy regards,
    from Barcelona!

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

    Any ideas?
    http://stackoverflow.com/questions/16266109/last-step-connecting-mysql-to-php-to-json-to-d3-javascript-cool-stuff-multicha

    Am waiting a answer from ESeufert on GitHub, for some ideas to.
    https://github.com/ESeufert

    Best regards,
    From Barcelona

    ReplyDelete
    Replies
    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!

      Delete
  8. Hi, I think d3 is amazing and has some brilliant alternatives to highcharts.
    I am using this http://bl.ocks.org/tjdecke/5558084 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]
    [{"day":"1","hour":"0","value":"174"},{"day":"1","hour":"1","value":"136"},{"day":"1","hour":"2","value":"66"},{"day":"1","hour":"3","value":"58"},{"day":"1","hour":"4","value":"57"},{"day":"1","hour":"5","value":"46"},

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

    ReplyDelete
    Replies
    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.
      Cheers.

      Delete
    2. Hi thank you for getting back to us have hosted my code up here
      https://gist.github.com/padster09/5659237

      Delete
    3. Ok, the first thing I note is that you're trying to parse the 'days' variable here 'd.days = parseDate(d.day);' 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 (http://bl.ocks.org/tjdecke/5558084) 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.

      Delete
    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,

      Delete
    5. No problem. I hope it works out.

      Delete
  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?

    ReplyDelete
    Replies
    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.

      Delete
  10. Thank you! It's here:
    http://stackoverflow.com/questions/17470571/json-mysql-data-to-d3-plot

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

      Delete
  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 http://mbostock.github.io/d3/talk/20111018/tree.html). 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

    ReplyDelete
    Replies
    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.

      Delete
    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?
      thanks

      Delete
    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 (http://mbostock.github.io/d3/talk/20111018/tree.html) really good ((view the page source or version here (http://bl.ocks.org/mbostock/4339083)). And use this in conjunction with the json layout from his example here (view-source:http://mbostock.github.io/d3/talk/20111018/flare.json). 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 (http://www.d3noob.org/2013/06/understanding-javascript-object.html)

      Delete
  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>
    <html>
    <head>
    <meta charset="utf-8">
    <script src="d3.v3.min.js"></script>
    <script>
    function draw(data) {
    data.forEach(function(d) {
    d.post_title = d.post_title;
    d.post_content = +d.post_content; });
    }
    </script>
    <title>Untitled Document</title>
    </head>

    <body>
    <script>
    d3.json("data2.php", draw);
    </script>
    </body>
    </html>

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

    Thanks,
    Anes

    ReplyDelete
    Replies
    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 (https://leanpub.com/D3-Tips-and-Tricks). 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 (http://stackoverflow.com/questions/tagged/d3.js). 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.

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

      Thanks,
      Anes

      Delete
    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.

      Delete
  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 (http://mbostock.github.io/d3/talk/20111018/partition.html). Please help me on the same.

    ReplyDelete
    Replies
    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.

      Delete
  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.

    ReplyDelete
    Replies
    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 :-)

      Delete
    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

      Delete
  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?

    ReplyDelete
    Replies
    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.

      Delete
  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.

    http://stackoverflow.com/questions/22305474/db-and-d3-connection-via-php-unable-to-complete-d3noobs-tutorial

    Any help would be appreciated. Thanks in advance.

    ReplyDelete
  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:

    d3.json("/var/www/database.php",function(error,data){
    //data=parseDate(data);

    console.log(data)
    });


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

    ReplyDelete
    Replies
    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

      Delete
    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?

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

      Delete
  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? http://bost.ocks.org/mike/nations/

    ReplyDelete
    Replies
    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

      Delete
  19. mysql_connect() is deprecated so use PDO_MYSQL instead.

    it is better also as you can use prepared statements that will avoid mysql injection and it can be used for multiple database languages.

    ReplyDelete
    Replies
    1. Thanks Dave. Yeah, I became aware of the different options not too long ago. I haven't gotten my head around them yet, but you're dead right. I need to and I need to update the book. Thanks for the heads up.

      Delete
    2. Hi D3noob, how safe is it to include the file path to a php script in D3? If the file path is viewable in the D3 source code to the user, then wouldn't this open security issues with PHP? I'm just trying to now wrap my head around security now that my D3 code is creating nice looking visualizations :)

      Delete
    3. Hi Robert, If the path name is all that is required for evil hackers to pwn our systems, we are all done for! But seriously, it shouldn't be a problem so long as the data being presented doesn't need protecting in some other way (and I can't think of a reason for this). Yes, you're right this could mean that the data can be accessed directly by a user, but as the php executes on the server we should be ok. We could remove the php to an area that isn't accessible to the external browser which would be better practice, so I think your point is well made and worth considering, but in this particular situation I think it's ok.

      Delete
  20. Thank you for your information

    ReplyDelete
  21. I've managed to create a html page with some checkboxes. When a checkbox is checked or unchecked, an ajax call to php is made which sends a query to a mySQL database based on the values of the checked boxes. The result of the query is json encoded and returned to a div in the html. Now I'm having trouble conceptualizing how I am going to access and chart this data. Getting the innerHTML of the div and assigning it to a var seems right, but I'm still learning.

    For the sake of clarity, I'll simplify this. Let's say the json encoded data within the div on an update is [{"value1": "10"}, {"value1":"15"}, {"value1":"20"}, {"value2":"100"},{"value2":"200"},{"value2":"300"}]. I'll need a way to update that var when the div is updated, right? i.e. adding value3 and value4, while dropping value2 creates entirely different contents within the div, and thus different points to graph.

    ReplyDelete
    Replies
    1. Righto... If I'm understanding you correctly, you are looking for a way to update the new data into your graph. Have you had a look over the following two posts? http://www.d3noob.org/2013/02/update-d3js-data-dynamically-button.html and http://www.d3noob.org/2013/02/update-d3js-data-dynamically.html Feel free to download the content in the book format from Leanpub (https://leanpub.com/D3-Tips-and-Tricks (it's free)) and then you can get all the code samples as well.

      Delete
    2. Thanks for the reply. I've run into a problem - the mySQL database returns those numbers with double quotes. I've tried:
      $json = preg_replace('/"(-?\d+\.?\d*)"/', '$1', $toecho); in the php which removes the quotes
      and
      .attr("width", function(d) {return parseInt(d.value1)})
      but I'm getting "NaN"
      perhaps there is a better approach. Ideas?

      Delete
    3. Hmm... Tricky one. I suspect there is something odd going on here. Especiallt if you've already tried stripping the quotes with the regex in php. Make sure you're running your data through a forEach and formatting the data in a way similar to the post here http://www.d3noob.org/2012/12/getting-data.html That should make sure that if it can be a number it will be in JavaScript.

      Delete
  22. Here's an example of the JSON after preg_replace:
    [{"Value1":60},{"Value1":14},{"Value1":3},{"Value1":7},{"Value1":39},{"Value1":18},{"Value1":1}]

    It is tricky, as the php is delivering the JSON encoded data to a div, and not the standard external JSON.

    var bars = canvas.selectAll("rect")
    .data.(res) /* <--- res is obtained by setting document.getElementById("result").innerHTML; to the var res (json encoded data returned by php to the div id result-- where to put forEach???*/
    .enter()
    .append("rect")
    .attr("width", function(d) {return parseInt(d.value1)}) /*/returning NaN - number is unquoted in php */
    .attr("height", 10)
    .attr("y", function(d, i) {return i *100});


    I'm no javascript expert, so the forEach you're suggesting -- I need some help understanding where to put that.

    ReplyDelete
    Replies
    1. Sorry that this reply is so hopelessly late Mark. I'm sure that you will have solved this by now, but for what it's worth, the json looks fine. From your code snippet (and bearing in mind that I'm not really seeing it in context) I think you could load your json before that code and give it a new name ('data1') then carry out a for-each loop on the data and name the array 'data'. then you should be able to load the information without problem. However, as good or bad as that advice may be, your example of the json would indicate that it is correctly formatted and doesn't need a foreach.
      My next suggextion is to ask a question on stack overflow (and make up the code into an example if possible (just use an external json file)). Good luck and apologies again for the delay.

      Delete
  23. Postures is fine to use and although I can't remember the syntax for the code exactly, from what I recall what you have there is pretty close (sorry I am away from a desktop). So you're definitely heading in the right direction

    ReplyDelete
  24. Thanks for your script

    It's not working well for me, could you please post the total script by details,

    Thanks,

    ReplyDelete
    Replies
    1. Hi. Sorry to hear that you're having problems. If you download a copy of the book from Leanpub (https://leanpub.com/D3-Tips-and-Tricks) (It's free by the way) you can download all the code that I use. If you're having problems, the best advice I can give is to go back a step or two and make sure that you've understood all the steps leading up to where you want to be. It can be a bit complex and if you jump into the middle it can be a bit tricky. Good luck,

      Delete
  25. That's pretty much it! It looks like you have the solution already. The only sticking point with the first piece of code you have is that the 'data' array will only be able to be acted on while it is in the function. In the second example where you declare 'data' internally, you can just lay the rest of your code out after the declaration and it should all work. A great way to check is to use 'console.log(data);' just after you load it and to check the results in the console of your browser (Firefox or Chrome) (http://blog.teamtreehouse.com/mastering-developer-tools-console).This might be a bit of a distraction for you, but well worth the the journey.

    ReplyDelete
  26. Thanks for your answer but...

    if i replace:

    d3.tsv("data.tsv", function(error, data) {
    data.forEach(function(d) {
    d.date = parseDate(d.date);
    d.close = +d.close;
    });

    with:

    var data = [
    {date: "1-May-12", close: "582.13"},
    {date: "30-Apr-12", close: "583.98"},
    {date: "27-Apr-12", close: "603.00"},
    {date: "26-Apr-12", close: "607.70"}
    ];

    it doesnt seem to work..
    im newbie at this and may be missing something obvious..

    ReplyDelete
  27. There are a couple of reasons that it might not be working. Firstly the numbers that are enclosed by speech-marks in the 'data =' version could be being recognized as text and not numbers. The piece of code 'd.close = +d.close;' makes sure that those numbers are treated as numbers. The second possibility is that as well as removing the 'd3.tsv' part, there should be another set of brackets later in the code (after the part that draws the graph) that will need to be removed.
    As an example I have set up two identical graphs. One here (http://bl.ocks.org/d3noob/b3ff6ae1c120eea654b5) that uses an external csv file and one here (http://bl.ocks.org/d3noob/13a36f70a4f060b97e41) that uses the 'data' array internally. This is a perfect example for what you are trying to achieve and if you look at the second example I have commented out the pieces of code that need to be removed (one (the d3.csv) part and one (//});) almost at the end).
    That should explain the difference hopefully.

    ReplyDelete
  28. But of course, removing the other set of brackets did it.
    Im deelply thankful with your explanations and the time you took to help an stranger.

    ReplyDelete
  29. You're welcome. Good on you for persevering.

    ReplyDelete
  30. hello sir can u help me with this???

    Sir i making a Server log analyser...The user will upload the server logs in our offline tool....and the output would be displayed in the form of a table....So can we use D3.js to convert those tables into Visual designs???

    ReplyDelete
    Replies
    1. That would be possible, but if I were you I would take a look at splunk or a similar alternative first and see if that would do the job for you. All things are possible, but the amount of work required to achieve what you might want would be the defining question. Try some simple things first to get a feel for what products might suit your needs and if you feel that it warrants the kind of flexibility that d3 brings to the problem.
      The answer to your question would always be 'yes' but the amount of effort is important to define as well.

      Delete
  31. Sir, I need one help. I have written a javascript to show values in webpage from a csv file using d3. The code is working perfectly fine in firefox. But can you please tell what can I do to make the code workable in IE as well.

    ReplyDelete
    Replies
    1. Hi there. You may find this difficult depending on the type of objects that you are trying to display. IE before version 9 does not support open standards as well as other browsers and may cause problems. Do some googling and see if you can find out if the version you are using might be affected. Good luck

      Delete
  32. GREAT post!!
    Quick Question . . . .
    So if we were to pull "live" data from a MySQL database (that would be updating all the time) - and say we wanted to pull a number of unique users (a data format in all Wordpress/MySQL sites) - how could you designate a schedule that the .php script would "run" - in the above case I assume it is would just run everytime the .php file was called? If so - would their be an easy way to schedule the file to run every day - or some other interval and then you would have a graph that would update on a predefined schedule?
    Does that make sense? THANKS!!

    ReplyDelete
    Replies
    1. Hi Jamey. I think that the process you are looking for is how to run a scheduled task. I'm not sure about Windows (but I'm sure there is a way)< but in Linux you would be l;ooking at setting up a cron job. The following site might help. http://www.thesitewizard.com/general/set-cron-job.shtml. Good luck.

      Delete
  33. Hi, I see you are doing a great job helping people with D3 here!

    I have a problem myself when trying to use API data for my D3 pie chart. The data loads in well when trying 'console.log(json)' but I can't seem to get it to work in my D3..
    One error I get is 'Uncaught TypeError: undefined is not a function' in the console.

    Hereby the code:
    https://gist.github.com/CrackingEgg/02e54e822f6f82fa0dc0

    Any help will be appreciated!

    ReplyDelete
    Replies
    1. You have some good looking code there, so I hesitate to try to provide any semblance of guidance, but it seems possible that some of the data isn't in the correct format and in this case I would use a foreach loop to make sure that it was correct (https://leanpub.com/D3-Tips-and-Tricks/read#leanpub-auto-getting-the-data). If that doesn't work, try hard coding someraw data into the script (just a few points) and keep adjusting the format until it does work. Then you'll be able to compare the api data with the known good data. I hope this helps.

      Delete
    2. Thanks, I tried:
      data.forEach(function(d) {
      d.uploads.count_download = parseFloat(d.uploads.count_download.value);
      });
      But I get: 'Uncaught TypeError: undefined is not a function'. Raw data works, tried that before.

      Delete
    3. OK, if raw data works either the data is not being returned from your api or it's in an incompatible format. Your console.log call should show you which of the two is the problem.

      Delete
    4. Hi again,

      Thanks for the help. I changed the code a bit and the console.log(array) line 42 works and all the arrays come in.
      I am trying to get all the arrays (to return in line 48). But it only returns one array (the last array) when using the .value(function(), I see that by trying the console.log(array) on line 47.

      Here is the code:
      https://gist.github.com/CrackingEgg/558cc90f9031b8aa18a1

      Delete
    5. I can only imagine that would be because your `jsondata.uploads.length` variable must be equal to 1, Keep an eye on that value as it may be cutting the loop short.

      Delete
    6. `jsondata.uploads.length` is 50 arrays long, so that can't be the problem.

      I am using 'return parseInt(array);' instead of line 48 now, maybe that is not totally corect..

      You mean the '.value' statement?

      Delete
    7. Sorry no,I was referring to the value of the 'return parseInt(array)' variable. I'd want to check it to make sure that it is greater than 1.

      Delete
    8. All working now, thanks for attention! Change pie(data) into pie(jsondata.uploads) was a very import one.

      I would really appreciate if you can into look to my next challenge on Stack Overflow:
      http://stackoverflow.com/questions/28034612/redraw-d3-pie-chart-with-new-data-when-clicking-button-or-enter-search-field

      Thanks :)

      Delete
  34. hey i love you'r tutorialn is there anny wey you can send my you'r map structure? i don't have the other files like the html, json, ....

    ReplyDelete
    Replies
    1. The structure of the table (if that's what you are referring to) is described in the post above. The files are all available when you get a copy of the book from here https://leanpub.com/D3-Tips-and-Tricks They are available as an extra add on (you can download everything for free by the way).

      Delete
  35. hmm my previous post didnt come through, ill try again :)
    First of thanks for this wonderfull example its really easy to follow.
    I do however have a small problem with the script resulting in an empty graph.

    my data is from my pokerleague. I want to plot out the scores over the evening of players, however there are more rounds per day then one.
    So obviously I need to include a timestamp with my date.
    First I editted the SQL so it would put out the desired code with DATA.PHP:
    [{"date":"2015-04-11 23:28:33","points":"38"},{"date":"2015-04-11 22:20:33","points":"16"},{"date":"2015-04-11 21:19:33","points":"10"},{"date":"2015-04-11 20:07:47","points":"6"},{"date":"2015-04-11 19:02:22","points":"38"},{"date":"2015-04-11 18:00:35","points":"38"}]

    this seems acceptable format i think?
    then I editted the SIMPLE-GRAPH.HTML to reflect the above data:

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

    however nothing is showing up, except the graph X and Y axis (without numbers).

    the location of data.php is correctly set in simple-graph.html.

    what could be the problem?

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. Hi Jorn. Thanks for the question. I'm sure you can appreciate that it's really tricky to debug code in the comment section of a blog :-), so I'll preface this with the observation that there could be a wide range of problems that I just can't see. I really recommend that you try to generate a sample code as a JSFiddle or similar for demonstrating code. However, in this case you have provided a good range of information. your parseDate line is not quite formatted correctly. It needs to have a space in the same place as the 'date' element. So instead of being "%Y-%m-%d%H%i%s" it should be "%Y-%m-%d %H%i%s". I know it looks like a small change, but if the format of the date is not the same as the the code is looking for that would certainly explain the problem you are seeing. Another really good troubleshooting tool for this kind of problem is to use the debugging console in the browser. I find the one in Google chrome the best. Just press F12 and it will open and you can often pick up errors from the dialogs that it produces. Good luck.

      Delete
  36. Hello!
    I found this to be an excellent tutorial that, as a newbie, I was able to follow along with right up to nearly the end. :-(
    I wasn't able to get the same plot to be produced from the JSON call.
    I've got a WAMP server setup. I was able to set up the mySQL database. Calling just the php file I got a warning about:
    Deprecated: mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead
    This came along with a call stack table display. I am not sure if this is causing the graph to not display or not?

    The other possible concern was I had to move my localhost port from 80 to 8000 due to a conflict. When I call http://localhost:8000/indexSL.htm (where the SL extension was for simple line graph) the console output is:
    GET XHR http://localhost:8000/data2.php [HTTP/1.1 200 OK 0ms]
    TypeError: data is undefined

    I've used the same code as the example. It works fine for the CSV version, the PHP code seems to be returning some data (after the call stack table) but putting the two pieces together is not communicating.

    Is this an example of the 'cross origin request' if so, is there a work around to handle it? Should I try and rewrite the example using mysqli is the call stack table causing issues or is that only a console log display?

    Thanks for any guidance

    ReplyDelete
    Replies
    1. Hi Larry. Fist of all, sorry for the late reply. I expect that you've moved on to much greater things in the mean time, but you have asked some good questions which others could benefit from, so thank you for the question.
      When I originally wrote this section of D3 Tips and Tricks, this method of extracting data from a MySQL database was probably borderline acceptable for the purpose and to be perfectly honest I didn't know any better. However, it no longer conforms to any best practices due to a need to include better security into requesting and retrieving data. As a result a better model to follow is to use a PDO call as ouitlined here (https://leanpub.com/RPiMRE/read#leanpub-auto-php-1).
      The change to using ports above 8000 has also been introduced to improve security and when setting up a web server nowadays to overcome the limitation there is much trickiness required. To get a feel fro the steps involved you can check out some information in the free book on setting up a Ghost blogging platform here (https://leanpub.com/jeghost).
      The error you mention is unlikely to be a cross origin problem unless you're looking off localhost. I would be looking for syntax problems.
      Apologies again for the late reply.

      Delete
  37. Hi,

    I want to draw line chart using d3.js.
    My array as like this : {"x_axis":["there","here","no"],"y_axis":["1","2","2"]}

    I want to set x_axis array as whole x-axis on graph and y_axis array as y-axis on graph.

    Please advice for the same.

    Thank you in advance.

    ReplyDelete
    Replies
    1. Because I'm not entirely sure what you're asking, it's difficult for me to advise. It sounds a little more like a bar graph. I suggest that you have a think about the type of display you're aiming for and review your needs. Good luck.

      Delete

  38. Hi D3noob,
    i am actually trying to plot a graph using php i actually got my json format using the data.php its in the following format

    [{"date_joined":"24-Apr-12","city":"delhi"},{"date_joined":"23-Apr-12","city":"gurgaon"},{"date_joined":"27-Apr-12","city":"noida"},{"date_joined":"26-Apr-12","city":"gurgaon"},{"date_joined":"25-Apr-12","city":"noida"},{"date_joined":"24-Apr-12","city":"delhi"},{"date_joined":"23-Apr-12","city":"mumbai"},{"date_joined":"20-Apr-12","city":"kanpur"},{"date_joined":"19-Apr-12","city":"hyderabad"},{"date_joined":"18-Apr-12","city":"delhi"},{"date_joined":"17-Apr-12","city":"delhi"},{"date_joined":"16-Apr-12","city":"gurgaon"},{"date_joined":"13-Apr-12","city":"faridabad"},{"date_joined":"12-Apr-12","city":"delhi"},{"date_joined":"11-Apr-12","city":"delhi"},{"date_joined":"10-Apr-12","city":"chandigarh"},{"date_joined":"9-Apr-12","city":"jaipur"},{"date_joined":"5-Apr-12","city":"delhi"},{"date_joined":"4-Apr-12","city":"mumbai"},{"date_joined":"3-Apr-12","city":"mumbai"},{"date_joined":"2-Apr-12","city":"hyderabad"},{"date_joined":"30-Mar-12","city":"hyderabad"},{"date_joined":"29-Mar-12","city":"hyderabad"},{"date_joined":"28-Mar-12","city":"hyderabad"},{"date_joined":"27-Mar-12","city":"hyderabad"},{"date_joined":"26-Mar-12","city":"delhi"}]

    how do i modify my simple_graph.html in order to plot graph for the above data since i am getting a blank page on execution with the modifications shown in the blog

    ReplyDelete
    Replies
    1. Hi there. Your json looks correctly formatted, but it doesn't have the correct type of information required to make a line graph. You have the time values for the x axis, but the other variable you have is an ordinal value (city names). You would need to have a numerical value to create the line graph. You most likely have a thought in mind about what you want to see on the page. Work out what the numerical value is that you need on the y axis and you should be there.

      Delete
  39. Hi, thanks for the great tutorial! I have a question about graphing multiple groups of data to see the correlations.
    I already have a database set up with multiple tables of user inputted information. I would like the user to be able to select which information they would like to see. Is this possible with the D3 simple graph?
    Thank you!

    ReplyDelete
    Replies
    1. Hi Ana, The example that I think you might want to check out is on page 411 of the book (https://leanpub.com/D3-Tips-and-Tricks (free to download)). It will let you display multiple lines and the user can select which they want to see by clicking on a legend that is generated automatically. You can download the code samples with the book as well.

      Delete
  40. This comment has been removed by a blog administrator.

    ReplyDelete
  41. This comment has been removed by the author.

    ReplyDelete
  42. Thanks for this great tutorial. It is so detailed and clear. Unfortunately, I still could not get it work with Node.js and Express. I suspect that the problem lies in about the setting of Node.js and Express, maybe. Did you cover this in the book? or is there any relevant tutorials about how to use php files in Node.js that you find useful?

    ReplyDelete
    Replies
    1. Sorry, there's nothing in this book with Node or Express. I do a little with Node in Just Enough Ghost (https://leanpub.com/jeghost) but it's more centred around getting it working properly on a Pi and not too much on actual usage. Worth a look (since it's free) but probable not the best direction. Other than that I Don't have any experience with it sorry.

      Delete
  43. Hello and thanks for this tutorial. I'm a noob as well. I do have an interesting case that i'm not sure how to resolve, but I think you'll say "it's easy". :-) The data you have here has the dates in each row, but what if I have lets say 4 rows (each is a warehouse) and 13 columns (warehouse name and 12 columns of monthly sales data). The data.forEach() reads and processes row by row (if i'm understanding correctly). What I'd like to do is also process the 12 date columns. I'm not sure where to begin. My data is in json format. Any tutorials that you can point me to or hints? My goal is vertical bar chart. Thank you in advance!

    ReplyDelete
    Replies
    1. You are correct that the forEach will process row by row. So for your data you will have (instead of something like a column named 'date') columns named 'january', 'february' etc possibly. In this case you could (there would be other ways) simply include a seperate line in your forEach loop that processes each month (d.jan = parseDate(d.january); etc). This then means that you will need your parseDate to correctly read the original data for the date/month whatever. Read the post here (http://www.d3noob.org/2012/12/formatting-date-time-on-d3js-graph.html) or check out this section in the free book (about page 26/27) which may be slightly more up to date (https://leanpub.com/D3-Tips-and-Tricks).

      Delete
  44. Thanks for this super easy tutorial.. I'd been wanting to learn this for ages and finally managed to do it today.

    ReplyDelete
  45. i not find simple-graph.html :((

    ReplyDelete
    Replies
    1. This is not the first time that someone has asked where to download the code samples. I get the feeling that I should include a chapter in the book just to help out :-).
      Once you've downloaded the book (https://leanpub.com/D3-Tips-and-Tricks) you can go to your Leanpub 'library' here; https://leanpub.com/user_dashboard/library
      This will show you all the books that you have downloaded from Leanpub and you can download updated versions of those books from here as well (all for free!). Once you click on the book that you want to update/download from this page you will see a link to 'download extras' on the right hand side of the screen under the book downloads. These Extras have the code in them.
      Alternatively there is a simple graph example here http://bl.ocks.org/d3noob/4414436 or a range of the code examples (from the book and others as well) here http://bl.ocks.org/d3noob.

      Delete
    2. From PHP file I got a conclusion
      [{"data":"00:42:29 02\/12\/2016","info2":"Anketirovaniye","status":"error","schet":"3"},{"data":"00:02:24 02\/12\/2016","info2":"Anketirovaniye","status":"good","schet":"204"}]

      Inserted in the page code in the example with a linear graph

      d3.json("php/data2.php", function(error, data) {
      data.forEach(function(d) {
      d.date = parseDate(d.date);
      d.close = +d.close;
      });

      date and close i renamed to data and schet

      But charts not work :((

      Delete
    3. Unfortunately it will be very difficult for me to diagnose the problem remotely. The best advice I can give is to start with the simplest example possible that works and slowly advance the code from there. In your example, I would recommend starting with the original code and working from that point. From what you have posted, the date is not in a format where thew original code could parse it correctly ("date":"27-Mar-12") and it has been 'escaped' with backslashes. Try and revert to the point where the original code works and slowly move forward from there.

      Delete
  46. Hii, Thanks for this tutoriel was very helpful
    Please can you tell me how i can display in xAxis just the days of the date .
    Ther date in database has this format : 2016-01-28

    ReplyDelete
    Replies
    1. Hi There, The good news is that I describe how to do this on page 90 of the book (https://leanpub.com/D3-Tips-and-Tricks) or you can read it online here (https://leanpub.com/D3-Tips-and-Tricks/read#leanpub-auto-format-a-date--time-axis-with-specified-values). In your case since you only want to have '%d' in your d3.time.format statement (that's just the day value).

      Delete
  47. Hi, this what i have like json file
    but can't progress anymore. can you help me to recognize folder organisation please.

    [{"regions":"louga","valeur":"124"},{"regions":"Saint louis","valeur":"145"},{"regions":"thies","valeur":"124"},{"regions":"matam","valeur":"124"},{"regions":"dakar","valeur":"124"},{"regions":"diourbel","valeur":"124"},{"regions":"kaolack","valeur":"124"},{"regions":"fatick","valeur":"124"},{"regions":"tambacouda","valeur":"124"},{"regions":"kedougou","valeur":"124"},{"regions":"kaffrine","valeur":"124"},{"regions":"kolda","valeur":"124"},{"regions":"sedhiou","valeur":"124"},{"regions":"ziguinchore","valeur":"124"}]

    ReplyDelete
    Replies
    1. Hi, I'm afraid that you're a long way from a standard format for data for the visualisation demonstrated in this section. I can't really help you adapt what you have here. That would be a considerable effort. Take a bit more time and work through the examples in the book (It's free from Leanpub (https://leanpub.com/D3-Tips-and-Tricks)).

      Delete
  48. Can I import TSV file as you did with CSV file???

    ReplyDelete
    Replies
    1. It's trivial to import tsv data instead of csv data. See this post http://www.d3noob.org/2012/12/getting-data.html. However, since you're asking the question in a post about the exporting of data from a MySQL database, I'm slightly unsure tht I am interpreting your question correctly. If you're wanting the php file in the example above that exports from a MySQL database, that would be a slightl odd thing that I haven'ttried before. I'm sure that it's do-able, but I have never had the need to try it. If you are trying to export from a mysql database, my reccomendation would be to use JSON formatted instead of TV data

      Delete
  49. Hi,
    this is my output using your same php file:
    [{"ID":"1","Country":"India","Value1":"100","Value2":"200"},{"ID":"2","Country":"India","Value1":"230","Value2":"800"},{"ID":"3","Country":"USA","Value1":"30","Value2":"300"},{"ID":"4","Country":"Sri Lanka","Value1":"320","Value2":"330"},{"ID":"5","Country":"Sri Lanka","Value1":"120","Value2":"90"},{"ID":"6","Country":"Sri Lanka","Value1":"420","Value2":"890"},{"ID":"7","Country":"China","Value1":"20","Value2":"890"},{"ID":"8","Country":"China","Value1":"430","Value2":"999"},{"ID":"9","Country":"Canada","Value1":"200","Value2":"319"},{"ID":"10","Country":"Canada","Value1":"1000","Value2":"29"}]

    However when i run my html file, I am getting the error in the console log:
    SyntaxError: Unexpected token < in JSON at position 0(…)

    Could you help me out please

    ReplyDelete
    Replies
    1. Interesting. Your JSON looks good, so I can only presume that there is something not quite perfect in the way that your html file is calling up the php file. In fact it seems possible that when your html file does the d3.json call to the php file it is not returning the json. it is looking at the raw php file (hence the mntion of the unexpected '<' in the JSON. Is it possible that your html file is calling the wrong php file?

      Delete
  50. can i automatically show data from my database daily on a bootstrap webpage in chart or graph format when i m remotely login to other system by SSH

    ReplyDelete
    Replies
    1. Hmm... I'm not sure that I understand your question properly. ssh and web access (which the post is describing) are pretty different. If you ssh'd into another system you would then need to instantiate an environment that supported a browser to do what the post is describing, and that would be waaaaay out of scope for what is being described. I can't help but feel as if I've misunderstood the question sorry.

      Delete
  51. Incredible Usefull post!!
    I've been trying to create a live.time path, using the linechart that you posted as a structure but didnt work at all...
    1. Its possible to create a database that update at realtime in MySQL?¿
    2. i tried a simplistic way, using a interval and a bucle it takes data every Xmiliseconds(setInterval...) and uses a bucle for(var i=0; i<m;i++) to past throw the diferents slots of the array, function(d,i){return xscale(d.close[i]);} for the diferents positions of the line, but is not working!

    3. tried to use this http://bl.ocks.org/simenbrekken/6634070, but not sure if i have to generate a now=new Date(), as a x-axis data or is possible to update the data from the json file.

    thanks alot, just wanted some orientation :D!!

    ReplyDelete
    Replies
    1. Hi there. Live graphs are an interesting topic. There are a couple of ways that I think work ok. The first is to use the setInterval function and within that function call another function that updates the data for a graph. Check out this (http://blockbuilder.org/search#text=setInterval) for a range of examples or this (http://bl.ocks.org/laurieskelly/6fbb8fd69c364654bf72) for the first good looking one I found. So in answer to your first point. if you have data being collected all the time by your MySQL database, a function inside the setInterval function could be reading that new data and writing it to the graphic. I have used that in the past and it works well.
      However that is a variation on the mechanism that you have in your example which I personally like. What you will want to do with that example is to determine how to load your data inside the 'Add new values' section to get data in via a look up call to your database. The direction I would take is to have some php that grabbed a json value using a database lookup (perhaps a php function) in the `group.data.push(20 + Math.random() * 100)` line in place of the `20 + Math.random() * 100` part.
      That all sounds pretty advanced now that I read it for someone that is starting out, but my advice is to persevere and keep trying things. Along the way you will learn heaps of other stuff that will come in handy at some point.
      The other way that might be interesting to think about (and I've never done it) is to use a socket connection. Check out this for info http://stackoverflow.com/questions/31705453/update-real-time-d3-chart-by-socket-io
      Best of luck.

      Delete
    2. Thanks D3noob, your comment helped me alot! Now i'm able to create Live graphs using random data function. And use data from mysql, but i can't put these two together. For call data i'm using a setinterval function, and my problem is that i cant use that data outside the function (update), and im not able to create a live graph inside the "bucle"(update). I know javascript have wierd variable declaration, just works inside the function you are using, but i'm trying some tricks to take the data from the update outside(globally), but is not working at all :D, any tips? Im out of resources. Thanks you for taking your time to answer d3.noobs like me :D

      Delete
    3. I'm glad that things are going in the right direction. I'm not much of a JavaScript person, but I had a read of the answer here (http://stackoverflow.com/questions/4862193/javascript-global-variables) on stack overflow and it looks promising. Try the alternatives they suggest. Good luck.

      Delete
    4. Actually the technique in this link looks like the best option https://snook.ca/archives/javascript/global_variable

      Delete
  52. what an awesome tutorial!
    However, I'm stuck in loading the json file. The csv file was imported to the database successfully, but it failed when I tried to generate the json. I don't know what's wrong but I tried adding echo "helloworld"; to the data2.php and it worked. So the only issue was really generating the json.
    Your help will be appreciated! thanks

    ReplyDelete
    Replies
    1. That's weird. The echo `json_encode($data);` line should return the variable $data encoded as json. Is it possible that there isn't anything in $data? is the query not working properly for some reason?

      Delete
  53. If i want to show two charts on single page . i copy the script code. change data file for second script.but both graph line shows in single chart.

    ReplyDelete
    Replies
    1. Ahh... Try having a read through this section of the book https://leanpub.com/D3-Tips-and-Tricks/read#leanpub-auto-arranging-more-than-one-graph-on-a-web-page. Its intent was to introduce the reader to bootstrap, but it shows how to do what you're wanting I think.

      Delete
  54. Please update your code to mysqli or PDO

    ReplyDelete
    Replies
    1. Cheers. Good point. I have added a note into the post advising what you have suggested and for the next eddition the plan is to use PDO. Cheers

      Delete
  55. Where do I find date2.php? I looked in the zip example I downloaded, but inside the zip there is no php with this name.

    ReplyDelete
    Replies
    1. Hi there. Please accept my apologies. I think that you mean data2.php? And you are right. It's not there! I have added it to the downloads from Leanpub, so if you grab a fresh version it will be there. Cheers

      Delete
  56. Very good, It's work properly, Tnx

    ReplyDelete
  57. I am trying to insert rows from a CSV file and failing (differently) each time. Please help. Screenshots are at the bottom of this tale of woe (blog post): https://jotascript.wordpress.com/2017/11/18/from-excel-to-mysql/

    ReplyDelete
    Replies
    1. Thanks Jay, Could you make a copy of the csv file available on your blog (not a screenshot per the blog, the actual file you are wanting to use to import), or (and this will probably work faster) ask your question on stackoverflow.

      Delete
  58. Hello,

    This is very interesting.

    Can we use a mysql database as a source of geographic data, query it and then use php to transform the result into a geojson file in order to draw a map?

    Thanks.

    ReplyDelete
    Replies
    1. Absolutely. This would be a very common use for this type of code. I won't recommend using the code from this post as it is pretty old and there are newer (and more secure) methods for using a database (Look for PDO).

      Delete
  59. Super helpful - thanks a ton to get me started on my d3 journey

    ReplyDelete
  60. Super helpful - thanks a ton to get me started on my d3 journey

    ReplyDelete
  61. This is such a great resource that you are providing and you give it away for free. I love seeing blog that understand the value of providing a quality resource for free. data sciences

    ReplyDelete