php - 插入数组值取决于带条件的数据表(嵌套数组)

Question

我有一个问题，我只是编程新手，逻辑问题有点低。我想从 MySQL 查询结果生成一个数组值，它必须是嵌套的。

我一直在编写的实际代码:

<?php
    $mostSold_arr = array();
    $subData_array = array();
    $day_arr = array();
    $data = array();
    // Connect to MySQL
    $link = new mysqli( 'localhost', 'root', '', 'db' );
    if ( $link->connect_errno ) {
      die( "Failed to connect to MySQL: (" . $link->connect_errno . ") " . $link->connect_error );
    }
    $fetch_data = mysqli_query($link, "SELECT products.id as prodID, title, SUM(total_price) as value, orders.order_date as date FROM products  LEFT JOIN order_details  ON order_details.product_id=products.id  LEFT JOIN orders ON orders.id=order_details.order_id GROUP BY order_details.product_id ORDER BY value DESC LIMIT 2 ") or die(mysqli_error($link));

    while ($row_mainData = mysqli_fetch_assoc($fetch_data)) {
        $mostSold_arr['category'] = $row_mainData['title'];
        $mostSold_arr['value'] = $row_mainData['value'];
        $prod_id['prodID'] = $row_mainData['prodID'];
        $mostSold_arr['url'] = "#";
        $mostSold_arr['description'] = "click to drill-down";
        $mostSold_arr['data'] = array();
        $fetch_subData = mysqli_query($link, "SELECT title, SUM(total_price) as value, MONTHNAME(orders.order_date) as date FROM products LEFT JOIN order_details ON order_details.product_id=products.id  LEFT JOIN orders  ON orders.id=order_details.order_id WHERE order_details.product_id=" . $prod_id['prodID'] ." GROUP BY title, date ORDER BY order_date LIMIT 3  ") or die(mysqli_error($link));
        while ($row_subData = mysqli_fetch_assoc($fetch_subData)) {
            $subData_arr['category']=$row_subData['date'];
            $subData_arr['value']=$row_subData['value'];
            $day_arr['data'] = array();
            array_push($mostSold_arr['data'],$subData_arr); 

            $fetch_subDataDay = mysqli_query($link, "SELECT title, SUM(total_price) as value, DATE_FORMAT(orders.order_date, '%M-%d') as date FROM products LEFT JOIN order_details ON  order_details.product_id=products.id LEFT JOIN orders ON orders.id=order_details.order_id  WHERE order_details.product_id=" . $row_mainData['prodID'] ." AND DATE_FORMAT(orders.order_date, '%M-%d') LIKE '%March%' GROUP BY date ORDER BY order_date  LIMIT 3  ") or die(mysqli_error($link));

            while ($row_subDataDay = mysqli_fetch_assoc($fetch_subDataDay)) {
                $subDataDay_arr['category']=$row_subDataDay['date'];
                $subDataDay_arr['value']=$row_subDataDay['value'];
                //array_push($day_arr['data'],$subDataDay_arr);

                if($subData_arr['category'] == 'March'){
                    array_push($mostSold_arr['data'],$subDataDay_arr);
                }
                //array_push($day_arr['data'],$subDataDay_arr);
                //var_dump($subData_arr); 
            }
        } 
      array_push($data, $mostSold_arr); 
    }

    $jsonData = json_encode($data, JSON_PRETTY_PRINT);
    $fp = fopen('dataColMultiNested.json', 'w');
    fwrite($fp, $jsonData);
    fclose($fp);

    die('<pre>' . print_r($jsonData, 1)); 
    mysqli_close($link); 
    ?>

上述代码的实际输出:

[
    {
        "category": "ACER E5-473-53C0",
        "value": "2455200.00",
        "url": "#",
        "description": "click to drill-down",
        "data": [
            {
                "category": "March",
                "value": "809100.00"
            },
            {
                "category": "March-02",
                "value": "27900.00"
            },
            {
                "category": "March-03",
                "value": "27900.00"
            },
            {
                "category": "March-04",
                "value": "55800.00"
            },
            {
                "category": "April",
                "value": "781200.00"
            },
            {
                "category": "May",
                "value": "418500.00"
            }
        ]
    },
    {
        "category": "ACER ASPIRE E5-551G-F57K LAPTOP",
        "value": "2339220.00",
        "url": "#",
        "description": "click to drill-down",
        "data": [
            {
                "category": "March",
                "value": "449850.00"
            },
            {
                "category": "March-02",
                "value": "29990.00"
            },
            {
                "category": "March-03",
                "value": "59980.00"
            },
            {
                "category": "March-07",
                "value": "59980.00"
            },
            {
                "category": "April",
                "value": "689770.00"
            },
            {
                "category": "May",
                "value": "779740.00"
            }
        ]
    }

预期输出(应该是这样的)

    [
        {
            "category": "ACER E5-473-53C0",
            "value": "2455200.00",
            "url": "#",
            "description": "click to drill-down",
            "data": [
                {
                    "category": "March",
                    "value": "809100.00",
                    "data": [
                         {
                        "category": "March-02",
                        "value": "27900.00"
                    },
                    {
                        "category": "March-03",
                        "value": "27900.00"
                    },
                    {
                        "category": "March-04",
                        "value": "55800.00"
                    }
                    ]
                },
                {
                    "category": "April",
                    "value": "781200.00"
                },
                {
                    "category": "May",
                    "value": "418500.00"
                }
            ]
        },
        {
            "category": "ACER ASPIRE E5-551G-F57K LAPTOP",
            "value": "2339220.00",
            "url": "#",
            "description": "click to drill-down",
            "data": [
                {
                    "category": "March",
                    "value": "449850.00",
                    "data": [
                        {
                        "category": "March-02",
                        "value": "29990.00"
                    },
                    {
                        "category": "March-03",
                        "value": "59980.00"
                    },
                    {
                        "category": "March-07",
                        "value": "59980.00"
                    }
                    ]
                },
                {
                    "category": "April",
                    "value": "689770.00"
                },
                {
                    "category": "May",
                    "value": "779740.00"
                }
            ]
        }
]

我为此工作了大约 2 个晚上或更长时间，但可以找到解决我的问题的正确解决方案。我希望你们能帮助我。谢谢。

Answer 1

这是一个完全退款的答案，基于您的问题及其标签中缺少的信息:您想要的结果是 JSON 格式(但我必须承认您的表示符合它)。

在 PHP 中，您必须首先创建一个等效数组，如下所示:

[
    0 => [
        "year" => "2016",
        "data" => [
            0 => [
                "month" => "Dec",
                "data" => [
                    0 => [
                        "day": "Dec 04",
                    ],
                ],
            ],
        ],
    ],
]

它可以用离你的初始代码不远的东西来构建:

$fetch_data1 = "Select * From tbl1";
$year = [];

while($row_year = mysqli_fetch_assoc($fetch_data1)){
    $year['year'] = $row_year['year'];

    $fetch_data2 = "Select * From tbl2";
    /* my query needs to loop inside because 
    it will contain condition depends on the retrieved data row   */
    $data = [];
    while($row_month = mysqli_fetch_assoc($fetch_data2)){
        $data['month'] = $row_month['month'];

        $fetch_data3 = "Select * From tbl3";
        /* my query needs to loop inside because
        it will contain condition depends on the retrieved data row   */
        $day = [];
        while($row_day = mysqli_fetch_assoc($fetch_data3)){
            $day['day'] = $row_month['month'] . ' ' . $row_day['day'];
        }
        $data['data'] = $day;
    }
    $year[] = $data;
}