I want to create a multidimensional array for d3.sankey, but I don't understand how it works to build an multidimensional array.
What I need is:
d3.Sankey needs an input like:
{
"nodes":[
{"node":0,"name":"node0"},
{"node":1,"name":"node1"},
{"node":2,"name":"node2"},
{"node":3,"name":"node3"},
{"node":4,"name":"node4"}
],
"links":[
{"source":0,"target":2,"value":2},
{"source":1,"target":2,"value":2},
{"source":1,"target":3,"value":2},
{"source":0,"target":4,"value":2},
{"source":2,"target":3,"value":2},
{"source":2,"target":4,"value":2},
{"source":3,"target":4,"value":4}
]}
Actually I built that thing with strings. That sound really complicated and it is. One mistake and it doesn't work anymore.
So my question is how can I build that form with arrays.
So first of all I call the Database to give me all the entries which comes from and Inputtable. Looks Like: (yes, I'm from Germany so some words in that query are German words)
$stmt = self::$_db->prepare("SELECT BalancesheetInput.RessourceName, "
. "SUM(BalancesheetInput.Amount) AS Amount, "
. "Einheiten.Einheit AS Unit "
. "FROM BalancesheetInput "
. "INNER JOIN Einheiten ON Unit_FK = Unit_ID "
. "GROUP BY BalancesheetInput.RessourceName");
$stmt->execute();
$fetcharray = $stmt->fetchALL(PDO::FETCH_ASSOC);
That "Input" query get the entries for the nodes and links which should refer to one Node which stands in the middle of the sankey-diagram (for better understanding I added a Picture)
Next step I need all the entries from an Outputtable
$stmtOutput = self::$_db->prepare("SELECT BalancesheetOutput.RessourceName, "
."SUM(BalancesheetOutput.Amount) AS AmountOutput, "
."Einheiten.Einheit AS UnitOutput "
."FROM BalancesheetOutput "
."INNER JOIN Einheiten ON Unit_FK = Unit_ID "
."GROUP BY BalancesheetOutput.RessourceName");
$stmtOutput->execute();
$fetcharrayOutput = $stmtOutput->fetchAll(PDO::FETCH_ASSOC);
all these Entries refer from that Node in the Middle so its self (hope you understand what I mean)
What I'm doing at the moment is I get the Input and Output queries and put them together by creating a very long string.
$counter = 0;
$ziel = count($fetcharray);
$array_values = count($fetcharray);
$counterafterInput = 0;
$jsonNodes ='{"nodes":[';
$jsonLinkes = '],"links":[';
foreach ($fetcharray as $getnodes){
if (($array_values-1) == $counter) {
// saves node to string,
//// wenn letzer Datenbankeintrag erreicht ist addet Schöneweide als Node
$jsonNodes .= '{"node":'. $counter. ',"name":"' . $getnodes['RessourceName'] . '","units":"'.$getnodes['Unit'].'"},';
$jsonNodes .= '{"node":'. ($counter+1). ',"name":"Schöneweide","units":"Blackbox"},';
// $jsonNodes .= '{"node":'. ($counter+2). ',"name":"weiter","units":"'.$getnodes['Unit'].'"},'; //irgendwann obsolete
// saves node to string, wenn letzer Datenbankeintrag erreicht ist
$jsonLinkes .= '{"source":'.$counter .',"target":' .$ziel . ',"value":' . $getnodes['Amount'] . ',"units":"'.$getnodes['Unit'].'"},';
// $jsonLinkes .= '{"source":'.($counter+1) .',"target":' .($ziel+1) . ',"value":' . $getnodes['Amount'] . ',"units":"'.$getnodes['Unit'].'"}';
$counter++;
$counterafterInput = $counter;
$counter++;
}
else{
//saves nodes to string while counter != Anzahl der Datenbankeinträge
//If else weil durch Komma getrennte Schreibeweise die nodes und Links eingetragen werden .... geht in die if ,wenn der vorletzte eintrag erreicht wird um
//die letze Node anzuhängen. Die letzte Node ist die Blackbox Schöneweide(oder später auch ein anderes Gebiet
$jsonNodes .= '{"node":'. $counter. ',"name":"' . $getnodes['RessourceName'] . '","units":"'.$getnodes['Unit'].'"},';
$jsonLinkes .= '{"source":'.$counter .',"target":' .$ziel . ',"value":' . $getnodes['Amount'] . ',"units":"'.$getnodes['Unit'].'"},';
$counter++;
}
}
$counterOutput = 0;
$array_values_output = count($fetcharrayOutput);
$jsnonnodesOutput = "";
$jsonLinkesOutput = "";
foreach ($fetcharrayOutput as $getnodesOutput)
{
if (($array_values_output-1) == $counterOutput){
$jsnonnodesOutput .= '{"node":'.$counter.',"name":"'.$getnodesOutput['RessourceName'].'","units":"'.$getnodesOutput['UnitOutput'].'"}';
$jsonLinkesOutput .= '{"source":'.$counterafterInput.',"target":'.$counter.',"value":'.$getnodesOutput['AmountOutput'].',"units":"'.$getnodesOutput['UnitOutput'].'"}]}';
}
else
{
$jsnonnodesOutput .= '{"node":'.$counter.',"name":"'.$getnodesOutput['RessourceName'].'","units":"'.$getnodesOutput['UnitOutput'].'"},';
$jsonLinkesOutput .= '{"source":'.$counterafterInput.',"target":'.$counter.',"value":'.$getnodesOutput['AmountOutput'].',"units":"'.$getnodesOutput['UnitOutput'].'"},';
$counterOutput++;
$counter++;
}
}
$JSONstring = $jsonNodes. $jsnonnodesOutput . $jsonLinkes .$jsonLinkesOutput;
return $JSONstring;
So let me explain what I'm doing:
First step I set a Counter. Next I count all the entries in Query to set the Node in the middle. For example I got 5 entries so I need to set the node in the middle to 6. Nextstep is initialize
`$jsonNodes ='{"nodes":[';`
next step I run the foreach-loop to fill all the entries in string I need to get something like that
{"node":0,"name":"node0"},
my Code for that is
$jsonNodes .= '{"node":'. $counter. ',"name":"' . $getnodes['RessourceName'] . '","units":"'.$getnodes['Unit'].'"},';
the Counter counts an Id for each entry
and also I have to set Links. So the nodes refer to the Node in the middle
$jsonLinkes .= '{"source":'.$counter .',"target":' .$ziel . ',"value":' . $getnodes['Amount'] . ',"units":"'.$getnodes['Unit'].'"},';
So source is the same ID like in nodes, target is the node in the middle amount and units are self explanatory.
In short I do the same to get the Output just that the source is the node in the middle and the target is each node itself
Actually it works but I think there is a much more better solution with arrays.
so Now I need Help to create an Array which I can save as variable for JavaScript.
If someone know how that works and tell me. That would be fantastic!
The better solution with arrays is to build an array with the structure you want and then run it through json_encode():
$nodes = array();
$nodes[] = array('node' => 0, 'name' => 'node0');
$nodes[] = array('node' => 1, 'name' => 'node1');
$nodes[] = array('node' => 2, 'name' => 'node2');
$links = array();
$links[] = array('source' => 0, 'target' => 2, 'value' => 2);
$links[] = array('source' => 0, 'target' => 1, 'value' => 2);
echo json_encode(array('nodes' => $nodes, 'links' => $links));
// {"nodes":[{"node":0,"name":"node0"},{"node":1,"name":"node1"},{"node":2,"name":"node2"}],"links":[{"source":0,"target":2,"value":2},{"source":0,"target":1,"value":2}]}