I created a D3 graph which works with an local JSON file. Now I want to utilize a MySQL DB, where I wrote a PHP script to fetch the data. The script is working but I receive a parsing error, as soon as I feed my D3 graph with it.
I am pretty new in terms of DB and PHP handling. I guess my Javascript file can´t resolve the PHP result proper, as the local JSON file starts with "nodes"... instead of "0".
How can I resolve that?
The JSON file looks like:
{
"nodes": [
{ "id": 0, "type": "food" },
{ "id": 1, "type": "drinks" },
{ "id": 2, "type": "snacks" }
]
}
The PHP script and output:
<?php
$username = "root";
$password = "root";
$host = "localhost";
$database="idep3";
$connection = mysqli_connect($host, $username, $password, $database);
$connection2 = mysqli_select_db($connection, $database);
$myquery = "
SELECT * FROM idep3.nodes";
$query = mysqli_query($connection, $myquery);
if ( ! $query ) {
echo mysql_error();
die;
}
$data = array();
for ($x = 0; $x < mysqli_num_rows($query); $x++) {
$data[] = mysqli_fetch_assoc($query);
}
echo json_encode($data, JSON_FORCE_OBJECT);
mysqli_close($connection);
?>
Output:
{"0":{"id":"0","type":"food"},"1":{"id":"1","type":"drinks"},"2":{"id":"2","type":"snacks"}}
And finally the graph:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>D3v6 DB demo</title>
<script src="https://d3js.org/d3.v6.min.js"></script>
</head>
<style>
body {
background-color: #e6e7ee;
}
circle {
stroke: black;
stroke-width: 1px;
cursor: pointer;
}
</style>
<body>
<script>
const data = "connection.php";
const onLoad = graph => {
var svg = d3.select("body").append("svg")
.attr("width", window.innerWidth)
.attr("height", window.innerHeight)
.attr("class", "svg")
.call(d3.zoom().on("zoom", function (event) {
svg.attr("transform", event.transform)
}))
.append("g")
d3.select("svg").on("dblclick.zoom", null)
//var linkContainer = svg.append("g").attr("class", "linkContainer")
var nodeContainer = svg.append("g").attr("class", "nodeContainer")
var forceLayout = d3.forceSimulation()
.force("charge", d3.forceManyBody().strength(-25))
.force("center", d3.forceCenter(window.innerWidth / 2, window.innerHeight / 2))
.force("collision", d3.forceCollide().radius(50))
//###############################################
//################## initialize #################
//###############################################
init()
function init() {
nodes = nodeContainer.selectAll(".node")
.data(graph.nodes, function (d) { return d.id; })
.join("g")
.attr("class", "node")
.attr("id", function (d) { return "node" + d.id; })
.call(d3.drag()
.on("start", dragStarted)
.on("drag", dragged)
.on("end", dragEnded)
)
nodes.selectAll("circle")
.data(d => [d])
.join("circle")
.attr("r", 40)
.style("fill", "whitesmoke")
.on("click", addNode)
nodes.selectAll("text")
.data(d => [d])
.join("text")
.attr("dominant-baseline", "central")
.attr("text-anchor", "middle")
.attr("id", function (d) { return "text" + d.id })
.attr("pointer-events", "none")
.text(function (d) {
return d.id + " " + d.type
})
forceLayout
.nodes(graph.nodes)
.on("tick", tick)
}
//add node function
function addNode(event, d) {
thisNode = d
var newNode = {
"id": graph.nodes.length + 1,
"type": "drinks",
x: thisNode.x,
y: thisNode.y
}
graph.nodes.push(newNode)
init()
}
//tick function
function tick() {
nodes.attr("transform", function (d) {
return "translate(" + d.x + "," + d.y + ")";
});
}
//drag function
function dragStarted(event, d) {
if (!event.active) forceLayout.alphaTarget(0.3).restart();
d.fx = d.x;
d.fy = d.y;
}
function dragged(event, d) {
d.fx = event.x;
d.fy = event.y;
}
function dragEnded(event, d) {
if (!event.active) forceLayout.alphaTarget(0);
d.fx = undefined;
d.fy = undefined;
}
}
d3.json(data).then(onLoad).catch(err => console.log(err))
</script>
</body>
</html>