In this article I would like to share with you a sample program that I wrote using PHP, MySQL and D3 Javascript library to display graph. The code is very short and simple I hope you will find my work useful thank you.
I am currently accepting programming work, it projects, school
programming projects, thesis and capstone projects, IT consulting
work, computer tutorials, and web development work kindly contact me in the following email address for further details. If you want to advertise in my website kindly contact me also in my email address also. Thank you.
My email address is the following jakerpomperada@gmail.com, jakerpomperada@aol.com, and jakerpomperada@yahoo.com.
My mobile number here in the Philippines is 09173084360.
My telephone number at home here in Bacolod City, Negros Occidental Philippines is +63 (034) 4335675.
My personal website is http://www.jakerpomperada.com
My personal website is http://www.jakerpomperada.com
JSON File Sample Output
D3 Graph Output
Program Listing
api.php
<?php
// set up the connection variables
$db_name = 'animals';
$hostname = 'localhost';
$username = 'root';
$password = '';
// connect to the database
$dbh = new PDO("mysql:host=$hostname;dbname=$db_name", $username, $password);
// a query get all the records from the users table
$animal='wombat';
$sql = 'SELECT * FROM animals where name="wombat" ';
// $sql .= ' WHERE name ='.$animal;
// use prepared statements, even if not strictly required is good practice
$stmt = $dbh->prepare( $sql );
// execute the query
$stmt->execute();
// fetch the results into an array
$result = $stmt->fetchAll( PDO::FETCH_ASSOC );
// convert to json
$json = json_encode( $result );
// echo the json string
echo $json;
?>
index.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>
<h1> PHP,MySQL and D3 Demonstration </h1>
<br>
<h2> Created By Mr. Jake R. Pomperada </h2>
<br>
<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>
animals.sql
-- phpMyAdmin SQL Dump
-- version 4.5.1
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Sep 26, 2018 at 04:16 AM
-- Server version: 5.7.21
-- PHP Version: 5.6.24
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `animals`
--
-- --------------------------------------------------------
--
-- Table structure for table `animals`
--
CREATE TABLE `animals` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL COMMENT 'Name of the animal',
`total` int(11) NOT NULL COMMENT 'The number of animals',
`record_date` timestamp NULL DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `animals`
--
INSERT INTO `animals` (`id`, `name`, `total`, `record_date`) VALUES
(1, 'dingo', 127000, '2016-01-20 16:00:00'),
(2, 'wombat', 98000, '2016-01-20 16:00:00'),
(3, 'koala', 293000, '2016-01-20 16:00:00'),
(4, 'dingo', 125000, '2016-02-20 16:00:00'),
(5, 'wombat', 96000, '2016-02-20 16:00:00'),
(6, 'koala', 288000, '2016-02-20 16:00:00'),
(7, 'dingo', 127000, '2016-03-20 16:00:00'),
(8, 'wombat', 93000, '2016-03-20 16:00:00'),
(9, 'koala', 221000, '2016-03-20 16:00:00'),
(10, 'dingo', 115000, '2016-04-20 16:00:00'),
(11, 'wombat', 91000, '2016-04-20 16:00:00'),
(12, 'koala', 229000, '2016-04-20 16:00:00'),
(13, 'dingo', 102000, '2016-05-20 16:00:00'),
(14, 'wombat', 92000, '2016-05-20 16:00:00'),
(15, 'koala', 249000, '2016-05-20 16:00:00'),
(16, 'dingo', 98000, '2016-06-20 16:00:00'),
(17, 'wombat', 83000, '2016-06-20 16:00:00'),
(18, 'koala', 261000, '2016-06-20 16:00:00'),
(19, 'dingo', 9400, '2016-07-20 16:00:00'),
(20, 'wombat', 81000, '2016-07-20 16:00:00'),
(21, 'koala', 278000, '2016-07-20 16:00:00');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `animals`
--
ALTER TABLE `animals`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `animals`
--
ALTER TABLE `animals`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=22;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
