php - 以逗号分隔的具有一个值的步骤到具有相同值的多个步骤

Question

我正在构建一个流程图(非常类似于桑基图)，但我在处理数据时遇到了一些问题。

我从 API 调用中获取了一个 JSON 文件。我正在使用 PHP 脚本将 JSON 输出存储在 MySQL 数据库中。

问题是现在的布局:

('A, B, C, D, E, F', 123456),
('A, B, C, D', 6543),
('B, C', 94934),

我需要什么(我想单独拆分每个步骤，但需要每个步骤的值来创建流程图)。

('A, B', 123456),
('B, C', 123456),
('C, D', 123456),
('D, E', 123456),
('E, F', 123456),
('A, B', 6543),
('B, C', 6543),
('C, D', 6543),
('B, C', 94934)

JSON 到数据库

$json_data = file_get_contents($CALL);
$json_data = str_replace("'","",$json_data);
$array = json_decode($json_data,true);
$sql_inserts = array();
foreach($array['reportitems']['reportitem'][0]['rows']['r'] AS $row)
    {
        $sql_inserts[] = "('".$row[c][0]."','".$row[c][1]."')";
    };
$sql = 'INSERT INTO flow (flow,number) VALUES '.implode(",",$sql_inserts);
    if (mysqli_query($conn, $sql)) {
            echo "Ok";
    } else {echo "<Error" . mysqli_error($conn) ;}
mysqli_close($conn);

输出 API 调用 - JSON

{
  "reportitems": {
    "@count": "1",
    "reportitem": [
      {
        "columns": {
          "@count": "2",
          "column": [
            {
              "ctitle": "flow",
              "type": "track",
              "alignment": "left"
            },
            {
              "ctitle": "number",
              "type": "integer",
              "alignment": "right"
            }
          ]
        },
        "title": "flow",
        "statistics": {
          "total": {
            "c": [
              "",
              "10813438"
            ]
          },
          "maximum": {
            "c": [
              "",
              "2482782"
            ]
          },
          "average": {
            "c": [
              "",
              "29"
            ]
          },
          "minimum": {
            "c": [
              "",
              "1"
            ]
          }
        },
        "rows": {
          "@count": "3",
          "r": [
            {
              "c": [
                "A, B, C, D, E, F",
                "123456"
              ]
            },
            {
              "c": [
                "A, B, C, D",
                "6543"
              ]
            },
            {
              "c": [
                "B, C",
                "94934"
              ]
            }
          ]
        }
      }
    ]
  }
}

我需要在哪里解决这个问题？将 JSON 输出写入 MySQL 数据库的脚本？或者从数据库查询到 sankey 图(需要分离格式)？

Answer 1

你可以试试:

foreach($array['reportitems']['reportitem'][0]['rows']['r'] AS $row)
{
    // EXPLODE TO GET INDIVIDUAL CHARACTERS
    $explodedCharac = explode(',', $row[c][0]);
    // LOOP THROUGH EACH CHARACTER
    foreach($explodedCharac as $key => $charac) 
    {
        // CHECK IF THIS IS NOT THE LAST CHARACTERS IN THE EXPLODED LIST
        if(isset($explodedCharac[$key+1])) {
             // PREPARE ARRAY WITH CURRENT AND NEXT CHARACTER, IF THIS IS NOT THE LAST CHARACTER IN THE EXPLODED LIST
             $sql_inserts[] = "('".$explodedCharac[$key].", ".$explodedCharac[$key+1]."','".$row[c][1]."')";
        }
    }

};