php - 使用 PHP 从 MySQL 结果输出复杂的 JSON

Question

我刚刚完成了一个 Sencha Touch 教程，它运行良好。我目前正在使用 PHP 从 MySQL 数据库将静态 JSON 数据文件(本教程使用)转换为动态生成的数据 (JSON) 文件。本教程中使用的 JSON 文件(指向示例文件的链接位于下方)具有复杂的结构，我在将 SQL 结果转换为本教程中使用的确切 JSON 格式时遇到了问题。下面列出了源代码的链接。关注 JSON 文件(第三个链接)。如果我可以简单地复制这个文件，那么项目的其余部分将对我有用。

Sencha Touch 教程位于: http://www.sencha.com/learn/intro-to-the-nested-list-component/

该项目的所有代码可以位于(我只允许发布两个链接希望你能理解下面的链接):github dot com/nelstrom/Sencha-Touch-nested-list-demo

可以在以下位置查看静态 JSON 数据文件的示例:https://github.com/senchalearn/Nested-list-demo/blob/master/data/albums.json

我有一个数据库和 sql，它以以下结构输出数据:

Genre  Artist     Album           Track               Duration 
ROCK   MUSE       Absolution      Intro               0:23
ROCK   MUSE       Absolution      Apolcalypse Please  4:13
ROCK   MUSE       The Resistance  Uprising            5:03
ROCK   SEVENDUST  Next            Hero                3:48
FUNK   PRIMUS     Antipop         The Antipop         5:33
FUNK   PRIMUS     Antipop         Ballad of Bodacious 2:29

我有以下输出 JSON 但格式不正确的 php，不幸的是它尽可能接近 - 抱歉我的 PHP 有点一般:)

$result = mysql_query($query,$link) or die('Errant query:  '.$query);
$model = array();

if(mysql_num_rows($result)) {
    while($e = mysql_fetch_assoc($result)) {
        $model['Genre'] = $e['Genre'];
        $model['Artist'] = $e['Artist'];
        $model['Album'] = $e['Album'];
        $model['items'][] = array(
                        'text' => $e['Track'],
                        'duration' => $e['Duration']
        );
    }
}  

header('Content-type: application/json');
echo json_encode(array('items'=>$model));}

这是从上面的 PHP 代码输出的 JSON 示例:

{
    "items": {
        "Genre": "ROCK",
        "Artist": "MUSE",
        "Album": "Absolution",
        "items": [
            {
                "text": "Intro",
                "duration": "0:23"
            },
            {
                "text": "Apolcalypse Please",
                "duration": "4:13"
            }
        ]
    }
}

很遗憾，此 JSON 格式不正确。主要问题是遍历并在正确的位置应用方括号 '[' ']'。我在下面包含了一个缩写示例:

    {
    "items": [
     {
       "model": "Genre",
       "items": [
         {
           "model": "Artist",
           "items": [
             {
               "model": "Album",
               "items": [
                {
                  "model": "Track",
                  "duration": 96,
                  "text": "Introduction",
                  "items": [

                  ],
                  "info": "",
                  "leaf": true
                 },
                 {
                  "model": "Track",
                  "duration": 155,
                  "text": "Please Accept My Love",
                  "items": [

                  ],
                  "info": "",
                  "leaf": true
                }
              ],
              "text": "Live in Cook County Jail",
              "info": "<p>Live in Cook County Jail is a 1971 live album by B.B. King recorded in Cook County Jail, Chicago, Illinois. It was ranked as number 499 in the book version of Rolling Stone's 500 Greatest Albums of All Time.</p>",
              "leaf": true
            }
          ],
          "text": "B.B.King",
          "info": "<p>Riley B. King aka B. B. King (born September 16th, 1925 in Itta Bena, Mississippi) is a well known American blues guitarist and songwriter. He is among the most respected electric guitarists. </p><p>One of King’s trademarks is naming his guitar (Gibson ES335) “Lucilleâ€. In the 1950s in a bar in Twist, Arkansas two men got into a fight, accidentally knocking over a bucket of burning kerosene (used for heating) and setting the establishment on fire. Risking his life, B.B. King ran back into the collapsing building to retrieve his guitar.</p>",
          "leaf": false
        },
      ],
      "text": "Blues",
      "info": "",
      "leaf": false
    }
  ]
} 

预先感谢您查看此内容，抱歉，如果它冗长，但我只是想确保我已经包括了所有内容。如果您需要更多信息，请告诉我。

亲切的问候

Answer 1

我要做的第一件事是尝试重新创建 JSON 格式的数据。快速测试

$array = array(
'items' => array(
    array(
    'model' => 'Genre',
    'items' => array(
        array(
        'model' => 'Artist',
        'items' => array(
            'model' => 'Album',
            'items' => array(array())
        )
        )
    )
    )
)
);
$json = json_encode( $array );
var_dump( $json );

这会提供与您需要的类似的 JSON 输出。

然后，您需要将数据从数据库中获取到相关格式的数组中。为避免多次通过您正在构建的阵列(随着阵列变大，这可能需要一段时间)，我将分两步执行此过程。

首先

$tempData = array();
while($e = mysql_fetch_assoc($result)) {
    $tempData[$e['Genre']][$e['Artist']][$e['Album']][$e['Track']] = $e['Duration']
}

然后您应该能够遍历 $tempArray 并以正确的格式构建数组以创建 JSON。