桑基图，多维数组创建与查询在PHP

Question

我想为d3.sankey创建一个多维数组。

但是我不明白它是如何建立一个多维数组。

我需要的是:

d3。Sankey需要这样的输入:

{
"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}
]}

实际上我用字符串构建了那个东西。这听起来很复杂，事实也是如此。一个错误就不能用了。

所以我的问题是我如何建立与数组的形式。

所以首先我调用数据库给我所有的条目来自和inputttable。看起来像:(是的，我来自德国，所以查询中的一些单词是德语单词;))

$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);

"Input"查询获取节点和链接的条目，这些节点和链接应该指向位于sankey-diagram (为了更好地理解，我添加了一个图片)

下一步，我需要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);

所有这些条目都是指中间的节点，所以它自己(希望你明白我的意思)

我现在做的是我得到输入和输出查询，并通过创建一个很长的字符串把它们放在一起。

$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;

让我解释一下我在做什么:

第一步设置计数器。接下来，我将对Query中的所有条目进行计数，以将Node设置在中间。例如，我有5个条目，所以我需要将中间的节点设置为6。下一步是initialize

`$jsonNodes ='{"nodes":[';`

下一步，我运行foreach-loop来填充string中的所有条目我需要得到这样的东西

{"node":0,"name":"node0"},

我的代码是

 $jsonNodes .= '{"node":'. $counter. ',"name":"' . $getnodes['RessourceName'] . '","units":"'.$getnodes['Unit'].'"},';

计数器为每个条目计数一个Id

，我也必须设置链接。所以节点指的是中间的节点

 $jsonLinkes .= '{"source":'.$counter .',"target":' .$ziel .  ',"value":' . $getnodes['Amount'] . ',"units":"'.$getnodes['Unit'].'"},';

source和node是相同的ID, target是中间数量的node，单位是自定义的

简而言之，我做了同样的事情来获得输出，只是源是中间的节点，目标是它自己的每个节点

实际上它工作，但我认为有一个更好的解决方案与数组。

所以现在我需要帮助来创建一个数组，我可以保存为Javascript的变量。

如果有人知道这是怎么回事，告诉我。那太棒了!

Answer 1

使用数组的更好的解决方案是用您想要的结构构建一个数组，然后通过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}]}