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!
