来自MYSQL PDO的自定义格式JSON，可在NVD3.js中使用 [英] Custom formatted JSON from MYSQL PDO for use in NVD3.js

问题描述

我想创建自定义格式的JSON以与NVD3.js一起使用，但是不确定如何使用PDO创建嵌套数组?

I would like to create custom formatted JSON to be used with NVD3.js, but am not sure how to create the nested arrays with PDO?

示例数据表:

+--------------+-------------------+---------------------+
| volume_name  | volume_files_used | recorded            |
+--------------+-------------------+---------------------+
| content001   |         130435938 | 2014-08-22 13:20:44 |
| content002   |          95977391 | 2014-08-22 13:20:44 |
+--------------+-------------------+---------------------+

使用PDO和json_encode():

//JSON OUTPUT
$stmtJSON = $pdo->prepare("SELECT volume_name, 
                           volume_files_used, 
                           recorded FROM collection;");
$stmtJSON->execute();
$json = json_encode($stmtJSON->fetchAll(PDO::FETCH_ASSOC));
print_r($json);

当前JSON输出:

[
    {
        "volume_name": "content001",
        "volume_files_used": "130435938",
        "recorded": "2014-08-22 13:20:44"
    },
    {
        "volume_name": "content002",
        "volume_files_used": "95977391",
        "recorded": "2014-08-22 13:20:44"
    }
]

我想要以下JSON输出:

[
    {
        "key": "content001",
        "values": [
            {
                "x": "2014-08-22 13:20:44",
                "y": "130435938"
            }
        ]
    },
    {
        "key": "content002",
        "values": [
            {
                "x": "2014-08-22 13:20:44",
                "y": "95977391"
            }
        ]
    }
]

在上面的示例中只有两行，但实际上我想将每个volume_name的所有values都拉为key:

In the example above there are only two rows, but practically I would like to pull all values for each volume_name as key:

[
    {
        "key": "content001",
        "values": [
            {
                "x": "2014-08-22 13:20:44",
                "y": "130435938"
            },
            {
                "x": "2014-08-22 14:20:44",
                "y": "130435940"
            },
            {
                "x": "2014-08-22 15:20:44",
                "y": "130435945"
            },
            {
                "x": "2014-08-22 16:20:44",
                "y": "130435965"
            }
        ]
    },
    {
        "key": "content002",
        "values": [
            {
                "x": "2014-08-22 13:20:44",
                "y": "95977391"
            },
            {
                "x": "2014-08-22 14:20:44",
                "y": "95977402"
            },
            {
                "x": "2014-08-22 15:20:44",
                "y": "95977445"
            },
            {
                "x": "2014-08-22 16:20:44",
                "y": "95977457"
            }
        ]
    }
]

---

答案更新

夏洛特·杜诺瓦(Charlotte Dunois)更新了输出:

---

ANSWER UPDATES

Charlotte Dunois updated output:

{
    "content002": {
        "values": [
            {
                "y": "95583732",
                "x": "2014-08-27 11:05:01"
            },
            {
                "y": "95539534",
                "x": "2014-08-27 12:05:01"
            }
        ],
        "key": "content002"
    },
    "content001": {
        "values": [
            {
                "y": "130121075",
                "x": "2014-08-27 11:05:01"
            },
            {
                "y": "130131806",
                "x": "2014-08-27 12:05:01"
            }

        ],
        "key": "content001"
    }
}

---

我设法从另一位开发人员那里获得了帮助.下面的代码可以正常工作，但是如果可以做得更好，任何人都可以发表评论.

I managed to get assistance from another Dev. The below code is working, but anyone feel free to comment if you can do it better.

//JSON OUTPUT
$stmtJSON = $pdo->prepare("SELECT volume_name, 
                           volume_files_used, 
                           recorded FROM collection;");
$stmtJSON->execute();
$result = $stmtJSON->fetchAll(PDO::FETCH_ASSOC));

$temp_array = array();
foreach($result as $bf) {
    if (!isset($temp_array[$bf['volume_name']])) {
        $temp_array[$bf['volume_name']] = array();
    }
    $temp_array[$bf['volume_name']][] = array(
        "x" => $bf['recorded'], 
        "y" => $bf['volume_files_used']);
}

$final_array = array();

foreach ($temp_array as $volume_name => $values) {
    $final_array[] = array(
        'key' => $volume_name,
        'values' => $values);
}

$json = json_encode($final_array);

推荐答案

在将其编码为json对象之前，必须使用此结构创建一个新数组.这样就可以完成工作(您的新格式化数组位于$ new_array中，因此您可以对其进行json编码):

You have to create a new array with this structure before encoding it as json object. This would do the job (your new formatted array is in $new_array, so you can just json encode that):

$new_array = array();
foreach($pdo_response as $bf) {
            if(empty($new_array[$bf['volume_name']])) {
                    $new_array[$bf['volume_name']] = array("key" => $bf['volume_name'], "values" => array());
            }
    $new_array[$bf['volume_name']]['values'][] = array("x" => $bf['recoreded'], "y" => $bf['volume_files_used']);
}

如果要为第一维使用数字键(0-....)，请使用array_values().

Make use of array_values() if you want numeric keys (0 - ....) for the first dimension.

这篇关于来自MYSQL PDO的自定义格式JSON，可在NVD3.js中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！