Abstract
Creating graphs and charts with javascript has led to greater development of libraries capable of creating them. Most often, these graphs and charts will be data driven with data from external sources, most commonly a database.
This tutorial provides a working example of creating an area chart with D3.js, PHP and MySQL.
The Database
The database used for this tutorial is MySQL and here a simple database, with a single table will be created to hold some data.
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT 'Name of the animal',
total INT NOT NULL COMMENT 'The number of animals',
record_date TIMESTAMP
);
INSERT INTO animals(name, total, record_date) VALUES ('wombat', 98000, '2016-01-21');
INSERT INTO animals(name, total, record_date) VALUES ('koala', 293000, '2016-01-21');
INSERT INTO animals(name, total, record_date) VALUES ('dingo', 125000, '2016-02-21');
INSERT INTO animals(name, total, record_date) VALUES ('wombat', 96000, '2016-02-21');
INSERT INTO animals(name, total, record_date) VALUES ('koala', 288000, '2016-02-21');
INSERT INTO animals(name, total, record_date) VALUES ('dingo', 127000, '2016-03-21');
INSERT INTO animals(name, total, record_date) VALUES ('wombat', 93000, '2016-03-21');
INSERT INTO animals(name, total, record_date) VALUES ('koala', 221000, '2016-03-21');
INSERT INTO animals(name, total, record_date) VALUES ('dingo', 115000, '2016-04-21');
INSERT INTO animals(name, total, record_date) VALUES ('wombat', 91000, '2016-04-21');
INSERT INTO animals(name, total, record_date) VALUES ('koala', 229000, '2016-04-21');
INSERT INTO animals(name, total, record_date) VALUES ('dingo', 102000, '2016-05-21');
INSERT INTO animals(name, total, record_date) VALUES ('wombat', 92000, '2016-05-21');
INSERT INTO animals(name, total, record_date) VALUES ('koala', 249000, '2016-05-21');
INSERT INTO animals(name, total, record_date) VALUES ('dingo', 98000, '2016-06-21');
INSERT INTO animals(name, total, record_date) VALUES ('wombat', 83000, '2016-06-21');
INSERT INTO animals(name, total, record_date) VALUES ('koala', 261000, '2016-06-21');
INSERT INTO animals(name, total, record_date) VALUES ('dingo', 09400, '2016-07-21');
INSERT INTO animals(name, total, record_date) VALUES ('wombat', 81000, '2016-07-21');
INSERT INTO animals(name, total, record_date) VALUES ('koala', 278000, '2016-07-21');
OK, the data is now ready.
The object of this tutorial is put use AJAX to get the code from the data, so a PHP script can be used as the API to access the data from the database.
api.php
<?php
$username = 'kevin';
$password = 'secret';
$database = 'animals';
$hostname = '127.0.0.1';
// name of the animal to get data for
$animal_name = 'wombat';
$dbh = new PDO("mysql:host=$hostname;dbname=$db_name", $username, $password);
$stmt = "SELECT * FROM animals WHERE name=:name";
$stmt->bindParam( ':name', $animal_name, PDO::PARAM_STR );
$stmt->execute();
$result = $stmt->fetchAll( PDO::FETCH_ASSOC );
echo json_encode( $result );
?>
The code above is quite simple an comments to the database and fetches all the data related to womabat. The results are fetched into an associative array and then json_encode'd so that the d3.js ajax call can consume the results.
Here is where the magic happens. D3.js uses blocks of javascript directly embedded where needed within a web page. This process simpifies the code separation by keeping code where needed.
Putting It All Together
Here all the code is added, with a little CSS to display the finished chart.
tute.php
<html>
<head>
<title>PHPRO AJAX D3.js MySQL Example</title>
<style>
body {
background-color:white;
font: 10px sans-serif;
}
.axis path,
.axis line {
fill: none;
stroke: #000;
shape-rendering: crispEdges;
}
.area {
fill: steelblue;
}
</style>
<script src="https://d3js.org/d3.v3.min.js"></script>
</head>
<body>
<script>
var margin = {top: 20, right: 20, bottom: 30, left: 50},
width = 1080 - margin.left - margin.right,
height = 960 - margin.top - margin.bottom;
var x = d3.scale.ordinal()
.rangeRoundBands([0, width], .0);
var y = d3.scale.linear()
.range([height, 0]);
var xAxis = d3.svg.axis()
.scale(x)
.orient("bottom");
var yAxis = d3.svg.axis()
.scale(y)
.orient("left");
var area = d3.svg.area()
.x(function(d) { return x(d.record_date); })
.y0(height)
.y1(function(d) { return y(d.total); });
var svg = d3.select("body").append("svg")
.attr("width", width + margin.left + margin.right)
.attr("height", height + margin.top + margin.bottom)
.append("g")
.attr("transform", "translate(" + margin.left + "," + margin.top + ")");
d3.json("api.php", function(error, data) {
var k = [];
data.forEach(function(d) {
d.record_date = d.record_date;
d.total = +d.total;
k.push(d.record_date)
});
x.domain(k);
y.domain([0, d3.max(data, function(d) { return d.total; })]);
svg.append("path")
.datum(data)
.attr("class", "area")
.attr("d", area);
svg.append("g")
.attr("class", "x axis")
.attr("transform", "translate(0," + height + ")")
.call(xAxis);
svg.append("g")
.attr("class", "y axis")
.call(yAxis)
.append("text")
.attr("transform", "rotate(-90)")
.attr("y", 6)
.attr("dy", ".71em")
.style("text-anchor", "end")
.text("Count");
});
</script>
</body>
</html>