Feeding the frontend – displaying data with D3.js Part 2
Carrying straight on from my previous post on the subject, I’m going to go through the alluded to third page to add, which is query.php and change main.js a little, too. The purpose is to swap out the hard coded arrays of data for an external data source, namely a web-based resource rather than any locally stored files using AJAX and JSON.
Query.php
What we’ve got so far are some blobs being drawn at various positions on the screen, based on the data stored in a couple of arrays. It isn’t all that exciting, but the main thing is that we have data in an array that can be visualised. The next step is then to load in that list of a computers from an external source. D3 can do things like load CSVs and JSON data in from a file, but since I’ve been using php to fetch data already from my database, I felt that it would be worth just adding a bit more code to what is already there to put that data into d3.
In another previous post I’d displayed the results of a query in a table, but what I want to do instead is to store them in JSON format. Its a data format that can be read and used by many different parsers on different platforms as well as being able to be directly read in by Javascript and interpreted as a list of objects.
Below is what you should be able to use to return and display a JSON string.
<?php
$username = "user";
$password = "password";
$room = $_GET['room'];
$serverDB = "mysql:host=localhost;dbname=inventory";
$conn = new PDO($serverDB, $username, $password);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$getHosts = "SELECT * from hosts WHERE Room = '". $room ."'";
$hosts = array();
if ($result = $conn->query($getHostsPlaces))
{
while ($row = $result->fetch(PDO::FETCH_ASSOC))
{
$hosts[] = $row;
}
echo json_encode($hosts);
}
$result->closeCursor();
$conn = null;
The first thing to note here is that, in this example, the request for the page will take the form of http://url/query.php?room=A001 – where “A001” would be the room you are asking to have the computers returned from. This lets me create the select statement with the room specified and return only the computers in a certain room.
I then create an array that has a new element added for each row (leftover terminology from the previous example). The syntax isn’t obvious but assinging a value to an array without specifying a key seems to be the same as array.push() in Javascript. After that, I then call json_encode and pass it the array of hosts, which serialises the data to be read in by something else later. In this instance I simply echo the data, which should be an array of data.
One last thing is that, unlike any previous examples, I try to now use PDOs. Included with most php/mysql installations of Linux, its a technology-neutral way to access most databases (although the connection string does specify that it is a MYSQL database), so in lieu of any real reason not to, I have decided to go down that route. However, one important thing I found out was that, if you don’t properly close the connection and queries when you’re done, you get internal server errors. So, whilst you didn’t have to do this with mysql(i) connections, you absolutely do have to with PDOs, which is probably a good thing.
When I call this page from my browser, what is displayed is the JSON’d data, as expected:
With that done, I can now go back to my main.js file and make some changes so that this data is read in.
main.js
jQuery and d3.json
It is at this point that the reference to the jQuery library becomes relevant. To access the jQuery object, you just have to use a $. It turns out that this is actually a legitimate variable name – but thankfully nobody else would be crazy enough to use it on its own so jQuery can use it all on its own. What it can then be used to do is to manipulate the DOM, like D3, although in a different way. But it can also be used to perform an AJAX request – used to grab data from another page via an XML HTTP Request, but with a fraction of the code. To call our query.php page and interpret the returned data as JSON, you can use the following code below.
$.ajax({
url: "query.php?room=w004",
dataType: "JSON",
success: makeComputers
});
The callback function is what is invoked (with the data passed in as a parameter) when the AJAX call is successful (you can create a similar callback for a failure, too). I’ve made a function called makeComputers, where I will put all of the previous code.
Update: I have since found that there is an even simpler way, when using d3.xhr or d3.json. For this example, you can reduce the above code down to a single line and cut jQuery out completely:
d3.json("query.php?room=w004", makeComputers);
In either case, the result will be the same; makeComputers will be called when the call has completed. This is an asynchronous call, though, so any code inside makeComputers will likely happen a few milliseconds after whatever follows either call.
I’ve modified the original code from last time to be as follows:
function Computer(place, hostname) {
this.place = place;
this.hostname = hostname;
}
function Position(place, posx, posy) {
this.place = place;
this.posx = posx;
this.posy = posy;
}
var positions = [
new Position('1', 0, 0),
new Position('2', 80, 0),
new Position('3', 160, 0),
new Position('4', 240, 0),
new Position('5', 320, 0),
new Position('6', 400, 0),
new Position('7', 0, 100),
new Position('8', 80, 100),
new Position('9', 160, 100),
new Position('10', 240, 100),
new Position('11', 320, 100),
new Position('12', 400, 100),
new Position('13', 0, 200),
new Position('14', 80, 200),
new Position('15', 160, 200),
new Position('16', 240, 200),
new Position('17', 320, 200),
new Position('18', 400, 200),
new Position('19', 0, 300),
new Position('20', 80, 300),
new Position('21', 160, 300),
new Position('22', 240, 300),
new Position('23', 320, 300),
new Position('24', 400, 300),
];
var computers = [];
var w = 450;
var h = 450;
var svge = d3.select("body")
.append("svg")
.attr("width", w)
.attr("height", h);
var div = d3.select("body")
.append("div")
.attr("class", "tooltip")
.style("opacity", 0)
.text("Tooltip");
function makeComputers(jsony){
for(var k=0; k<jsony.length; k++){
computers.push(new Computer(k+1,jsony[k]['hostname']));
}
var recties = svge.selectAll("rect")
.data(computers)
.enter()
.append("svg")
.attr("data-hello", function (d,i) {return d[i]; })
.append("rect")
.attr("width", 30)
.attr("height", 30)
.attr("x", function(d,i) {var xloc = positions.findIndex(y => y.place == computers[i].place); return positions[xloc].posx +15})
.attr("rx", 6)
.attr("ry", 6)
.attr("y", function(d,i) {var xloc = positions.findIndex(y => y.place == computers[i].place); return positions[xloc].posy +15})
.style("fill", "Lavender")
.on("mouseover", fadein)
.on("mouseout", fadeout)
.on("mousemove", moviemouse);
}
function fadein(d, i) {
div.transition().duration(200).style("opacity", 0.9);
div.style("left", d3.mouse(this)[0])
.style("top", d3.mouse(this)[1])
.html(d.hostname);
}
function fadeout(e) {
div.transition().duration(400).style("opacity", 0);
}
function moviemouse(e) {
div.style("left", d3.mouse(this)[0])
.style("top", d3.mouse(this)[1]);
}
d3.json("query.php?room=w004", makeComputers);
Just to break this down in summary:
- The makeComputers function starts off by adding all of the computers imported to the computers array based on their hostname
- It then adds in rectangles, with the mouse listeners, using the better D3 method of adding SVGs to the page.
- I’ve also used the findIndex function without needing a for loop, since D3 provides the index to be able to do this.
- There are no longer any computers specified in the array of computers
- There are now three functions associated with fading in/out and movement of the mouse (so the position of the tooltip box will change as the mouse moves)
- Finally, this all happens when I call makeComputers at the end as the function that is executed once the request to the given URL has completed
The only last modification you may need to make, if your query.php file is stored on a different server to the one you are running your index.html and main.js from, is to add this to the top of your query.php file:
<?php
header("Access-Control-Allow-Origin: *");
This allows calls from other domains to be made, which is disabled by default for security reasons. However, if you’re confident that (for testing purposes at least) this won’t be an issue, then you can go ahead and enable to it, which lets you do things like run your index.html page and JS files from your local disk and make queries to the remote server.
This should have hopefully got you to a stage where you can make requests for JSON data to a web server and have it return some SQL results as readable data by JavaScript, but if anyone has any issues or encounters any oddities, do get in touch!
‹ Feeding the frontend – displaying data with D3.js Part 1 Feeding the frontend – generating data for D3.js Part 2a ›