It’s been a while since I’ve posted any updates about Switchy McPortface. It works just fine in my dayjob and the data can be viewed by anyone wanting to see what is plugged into what, which has been helpful in a few situations.
However, aside from a few customised tables and functions, there’s not really a lot more that I’ve done to improve functionality. What I have done, however, is embark on making a nicer frontend with some interaction. Essentially what I am aiming to do is to display all of the computers on a single webpage for a given room, roughly corresponding to the positions that they actually exist in in each room. My first thought a few years ago was to use Unity3d or C++ and OpenGL but, since I’ve already used these things before, why not try something new and use Javascript to make a web app or something?
This guide is going to go through a nicer way to display data than just using a table generated from some basic HTML in php. There’s a few libraries out there, but I thought it would be nice to get stuck into something that has some widespread use and the name Data Driven Documents sums up nicely what I’m trying to do – generate something visual based on some underlying data. D3 gives a nice way to manipulate the DOM, bind data and provide some visualisation and I felt it fit somewhere between jQuery and (other) visualisation libraries for this project. So without further ado, here is a quick rundown of one way to use d3.js to generate something prettier than a few table rows and columns!
Starting out
Because this builds on my previous work (with the desktop client now uploaded to Github), I won’t go into exactly how you get this specific data generated, or a web server set up and running. The focus here is how to interpret data with a basic web page – so hopefully you’ll already have a way to serve and process some php files.
For this project overall, I used three files:
- index.html – the main file you’ll call when you open your browser. It’ll also contain the page styles, instead of a separate .css file, and invoke the Javascript file
- main.js – the file that will contain the scripts used that, in turn, call d3.js
- query.php (this comes in part 2) – the file that handles the communication between your page and a database
The index and js files can sit at the web directory root of the server, or they can reside on your own machine. I’ve been using Brackets and testing this on my local machine, with php files running off a virtual machine and the concept is really simple:
- Open index.html
- Load d3.js and the main.js files
- Load in some data that represents computers
- Create an SVG on the main page
- Create a sub shape for that SVG for each computer
Index.html
The html landing page isn’t going to have an awful lot in it. Its primary purpose is to load the JS files, which in this case are d3.js v4 and jquery (used for simple AJAX calls later in part 2).
<html>
<head>
<title>Room test</title>
<script src="https://d3js.org/d3.v4.js"></script>
<script src="jquery-3.1.1.js"></script>
</head>
<body>
</body>
<script src="main.js"></script>
</html>
Note that main.js is called after everything else; this is so that it loads after other JS libraries. You also don’t need to type out the entire <script type=”text/javascript” src=”main.js”></script> as of html5, since Javascript is the default script type now and the “type” attribute can be omitted.
main.js
This is where the main gubbins of this example resides. The gist of it is:
- Create a blank SVG
- Load in list of computers and a list of positions
- Draw a new circle for each computer at its respective position
- For fun, display the hostname for each of those computers when you hover over it with the mouse
Here’s each part broken down:
Creating a blank SVG
Once you’ve loaded the d3.js library in your index.html file, you can access d3 functions as in the example below. All it does is to create a blank SVG, appended to the body of the page, with an arbitrary width and height.
var w = 500;
var h = 450;
var svg = d3.select("body")
.append("svg")
.attr("width", w)
.attr("height", h);
Load in computers and positions
So far so good? Next I’m just going to create two objects for a Computer and a Position, each taking some parameters to represent what they are. A computer is, for now, defined as simply a name – and this has a “place”. A position is a combination of a place number and its respective x and y coordinates. The reason these are separate is because at some point we might have different rooms or position layouts and I want to keep the computer and position data separate.
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('10', 0, 0),
new Position('20', 80, 0),
new Position('30', 160, 0),
new Position('40', 240, 0),
new Position('50', 0, 100),
new Position('60', 80, 100),
new Position('70', 160, 100),
new Position('80', 240, 100)
];
var computers = [
new Computer('10', "WS10562"),
new Computer('20', "WS10239"),
new Computer('50', "WS10555"),
new Computer('60', "WS9111"),
new Computer('70', "WS11032"),
new Computer('40', "WS11031")
];
So here are two arrays of data for the computers and positions. The logic is that, whilst there may be any number of positions, they may not all be filled by a computer. Anyhow, this is all sort of irrelevant to D3 for now (and it could have been loaded in externally), so I’ll get on and demonstrate how you can now put something on screen.
Draw a new circle for each computer at its respective position
Very simply, I’m going through that array of computers and, for each one, I’ll add a circle at that position.
for (var x = 0; x < computers.length; x += 1) {
var posIndex = positions.findIndex(y => y.place == computers[x].place);
svg.append("svg")
.append("circle")
.attr("cx", positions[posIndex].posx + 30)
.attr("cy", positions[posIndex].posy + 30)
.attr("r", 20)
.style("fill", "purple");
}
To match the data in the positions array up with the computers array, I need to first find the index of the correct item in positions array that corresponds with the “place” of the computer at the current index. In SQL, a left or an inner join would do what we need to do but in this example, I’m using two separate arrays of data that I need to match up.
Apparently, as of ES6, you can use findIndex. What this will do is return the index of positions where it finds a match by the function provided. Because I’m trying to match a property of an item in the array, the function needs to compare that “place” property for each item in the position array to the current computer’s “place” property. The => operator shortens the need to make that function by using y as the variable to represent the operative array item and it will return true when it is equal to the condition given.
For each of these computers, you can then append the SVG created earlier with a circle and give it the attributes for the radius, x and y coordinates (with an offset) and modify their style (which could be done in the index.html file but you can do it now too), which is just the fill colour here. This just adds some circles nested within the SVG tag that has been added to the page – nothing hugely complex, which is the great thing about D3. It is just a nice way to access the DOM and to add elements to a webpage dynamically.
It is important to note that this is not the best way to use D3. The way in which it should be done is to say you’ll add all of the elements of a given shape in one call then pass the data in – here, we’re going through the data first and then just adding one element on each iteration of the loop. There is no need to use a for loop in d3 and so what I’ve done is counter-intuitive to the way you’d normally learn it; but I’m simplifying the process of combining two arrays’ worth of data which I haven’t been able to find a nicer way to do when using D3. Besides, later it won’t be necessary; however, it was useful to figure out a nice way to make things work for now.
Display the hostname on mouse events
This is really easy to do. You first need to modify the previous code a little to look like this:
svg.append("svg")
.append("circle")
.data(computers) //Add this!
.attr("cx", positions[posIndex].posx + 30)
.attr("cy", positions[posIndex].posy + 30)
.attr("r", 20)
.style("fill", "purple")
.on("mouseover", fadein) //Mouse over event
.on("mouseout", fadeout); //Mouse moved away event
Although it isn’t used as extensively as it will be later, the data function has been added in which is needed to provide the hostnames to each of the shapes when you mouse over them. For that to happen, two events need to be added with the on function – which are “mouseover” and “mouseout” (events that are triggered when the mouse is detected to have entered and exited the boundaries of the element in question). As much as I like the whole anonymous function thing in JS, I’m just going to call some named ones because I hate polluting what should be simple code with a bunch of ugly long functions that are more than 2 or 3 lines long.
And these are the functions you need:
var div = d3.select("body")
.append("div")
.attr("class", "tooltip")
.style("opacity", 0)
.text("");
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);
//console.log("fadeoin");
}
function fadeout() {
div.transition().duration(400).style("opacity", 0);
console.log("fadeout");
}
Here, the two functions can take two parameters passed into them by d3, which are the data and an index.The data is the element of the array used when each shape is created (although, in this case, that will always just be the first element, since only one circle is added at a time). The index will be which iteration that d3 element was created during.
I’ve also added a new div to the page up there too – that’s because, in order to have text pop up, I want to do it in a tiny floating “box”, which is really just an HTML element. As a result, changing the text is as simple as changing the html property of the object to whatever the hostname of that data object is. Note that, if you were to use a totally different dataset for this, “hostname” would have to be replaced by whatever else you would want to have displayed instead. I also have it appear at wherever the mouse is with a bit of a transition time, because that makes it look a bit swishier.
One last thing is that, to make it actually look nice, you might want to add a style just for the tooltip (hence div.tooltip) to the main html file within the <head> section somewhere:
<style>
div.tooltip {
position: absolute;
text-align: center;
width: 60px;
height: 29px;
padding: 2px;
font: 12px sans-serif;
background: green;
border: 0px;
border-radius: 9px;
pointer-events: none;
color: white;
}
</style>
This centers the text and gives it a solid colour background with a bit of a rounded border and some padding.
Conclusion
If you’ve done it right, the result should look something like this (I’ve added both circles and rectangles here as a test and reduced the number of “computers” a bit for this example)
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!
Ok so this is a bolt-on post to my previous post, where I am now essentially trying to figure out a nice way to map the data between the computers and their positions in a room. The way I’ve designed the system is that I have three tables with the relevant data:
- Hosts: The main piece of data here is the hostname and other data – but it also contains the room that the host belongs to (and the “place”, which is just a notional number that I placed on a diagram)
- Rooms: This is the list of rooms and their “layout type”. We could have ten different rooms each with the same layout, so this is a way to say which room has which layout type. Its sole purpose is to join the other two tables.
- Places: The combination of a layout type and a position will give you an x and a y coordinate. This is a relative coordinate. It doesnt have to know which rooms are associated with it, its purely data on where – for a given layout type – a “position” would exist.
The idea for this is that you would fetch the respective coordinates for a host from the places table. The hosts table only has the place, so its necessary to fetch the correct layout type that matches the room, and then fetch the coordinates based on that data.
Complicated? A little. Especially for something that doesn’t seem too big. But I want it to be scalable and to separate out the data for positions from the hosts; a room may entirely change its layout but, so long as the room doesn’t change, the only thing I would need to change is the layout type and then the coordinates. However, I could have just placed all of this data into the hosts table – or removed the third table for places and just have mapped each room directly to sets of coordinates (and I still may!).
But I did find a solution, although it took a few iterations. I needed to refresh myself on inner and left joins a bit but my original plan was to do a left join between the hosts table and places table where the hosts’ room is a specific room. But then the places table works on layout type and noot a room number.
Ok so the first step would be something like this:
SELECT hostname, place, room, posx, posy FROM hosts INNER JOIN places ON places.position = hosts.place WHERE room = "W004"
Select the hostname from hosts, the coordinates from places, the room and the place from the inner join’d josts and places. The inner join will be done where the position field from places matches the place field from hosts. To limit it, I then just put “where room = w004”.
This would return a lot of results, since there’s many hosts potentially with the same place and many places with the same position. The “where” limitation narrows the hosts down to only the selection relevant to the one room, but it still leaves lots of entries potentially from the position, since there will likely be n times as many results as there are positions and layouts. That would then give duplicate host results, a duplicate for each recurring position that appears in the places table.
So the next step is to narrow this further, which was causing me a lot of headaches. I had an idea to expand the “WHERE” statement to include the room, but that isn’t a part of the dataset constructed by the joins. The solution was to do two joins – the second join being where the layout type matches the room specified in the room table and where the room matches in both the room and hosts table. This narrows both the hosts and rooms down to just one room, which means in turn there will only be one layout – and this further restricts the results from places down to what I need.
SELECT hostname, place, rooms.room, posx, posy FROM hosts INNER JOIN places ON places.position = hosts.place INNER JOIN rooms ON rooms.room = hosts.room AND rooms.layoutType = places.layout WHERE rooms.room = "W004"
Note that I could have used left joins, which would preserve all the host data, but I am trying to narrow it down and there’s just no need for anything else. I should probably also tidy up some of the names a bit, especially since there is now ambiguity between the different “room”s, but not between place and position.
With that done, I can now replace my original SQL statement:
$getHosts = "SELECT * from hosts WHERE Room = '". $room ."'";
With the following:
$getHosts = "SELECT hostname, place, rooms.room, posx, posy FROM hosts INNER JOIN places ON hosts.place = places.position INNER JOIN rooms ON rooms.room = hosts.room AND rooms.layoutType = places.layout WHERE rooms.room = '". $room ."'";
A lot longer, but it returns a single dataset with all the position data we need for a given room!
