来自SQL的多维PHP数组，用于json_encode

Question

我很难从数据库中创建一个三维数组，并将其编码为JSON。

我的数据库包含3个表，timeline_table、content_table和pic_table。我想要我的JSON的以下结构：

{"timeline:"{"content":{"pictures:"{}}}}

这是我当前的PHP代码：

$get = 1;
$results = mysql_query("
SELECT timeline_table.*, content_table.*, pic_table.*
FROM timeline_table
    JOIN content_table
        ON content_table.tl_ID = timeline_table.tl_ID
    JOIN pic_table
        ON pic_table.content_ID = content_table.content_ID
WHERE timeline_table.tl_ID = $get   
") or die(mysql_error());
while($row = mysql_fetch_assoc($results)){
    $timeline['timeline'][] = array(
        'tl_ID' => $row['tl_ID'],
        'tl_name' => $row['tl_name'],
        'tl_date' => $row['tl_date'],
        'tl_desc' => $row['tl_desc'],
    );
    $timeline['timeline']['content'][] = array(
        'content_ID' => $row['content_ID'],
        'tl_ID' => $row['tl_ID'],
        'content_time' => $row['content_time'],
        'content_date' => $row['content_date'],
        'content_title' => $row['content_title'],
        'content_content' => $row['content_content'],
        'content_category' => $row['content_category'],
        'content_mapLat' => $row['content_mapLat'],
        'content_mapLng' => $row['content_mapLng'],
        'content_zoomLvl' => $row['content_zoomLvl'],       
    );
    $timeline['timeline']['content']['pictures'][] = array(
        'pic_ID' => $row['pic_ID'],
        'content_ID' => $row['content_ID'],
        'pic_path' => $row['pic_path'],
        'pic_desc' => $row['pic_desc'],
        'pic_link' => $row['pic_link']
    );
}
echo stripslashes(json_encode($timeline));
}

我还尝试过为每个表使用1个查询，并使用3个while循环来填充数组。我相信一个问题是更好的方法，但如果我错了，请纠正我。这个php为我提供了以下JSON:

{
   "timeline":{
      "0":{
         "tl_ID":"1",
         "tl_name":"Tidslinje 1",
         "tl_date":"2013-01-16",
         "tl_desc":"Test av tl_table"
      },
      "content":{
         "0":{
            "content_ID":"1",
            "tl_ID":"1",
            "content_time":"16:00:00",
            "content_date":"2013-01-17",
            "content_title":"Test",
            "content_content":"Test content number one.",
            "content_category":"test",
            "content_mapLat":null,
            "content_mapLng":null,
            "content_zoomLvl":null
         },
         "pictures":[
            {
               "pic_ID":"1",
               "content_ID":"1",
               "pic_path":"http://i.imgur.com/F6RmDFt.jpg",
               "pic_desc":"katt",
               "pic_link":"http://i.imgur.com/F6RmDFt.jpg"
            },
            {
               "pic_ID":"3",
               "content_ID":"3",
               "pic_path":"http://i.imgur.com/POum7eK.jpg",
               "pic_desc":"seamonster",
               "pic_link":"http://i.imgur.com/POum7eK.jpg"
            }
         ]
        }
    }
}

所有图片（无论内容ID如何）都在一个数组中，如果我添加更多内容，ID为2,3等的内容都在图片数组下。我希望图片在它们所属的content_ID下的数组中，以及它们所属时间线下的内容。我还希望数组键为"timeline"、"content"one_answers"pictures"，而不是整数。

希望这是可以理解的，任何帮助都将不胜感激！

编辑：已解决

$get = 1;
$result = mysql_query("
SELECT t.tl_ID, t.tl_name, t.tl_date, t.tl_desc, c.content_ID, c.content_time, c.content_date, c.content_title, c.content_content, c.content_category, p.pic_ID, p.pic_path, p.pic_desc, p.pic_link
FROM timeline_table t
    LEFT JOIN content_table c ON t.tl_ID = c.tl_ID
    LEFT JOIN pic_table p ON c.content_ID = p.content_ID
WHERE t.tl_ID = $get
ORDER BY t.tl_ID, c.tl_ID, p.content_ID
") or die(mysql_error()); 
$jsonData = array();
$tl_ID = 0;
$content_ID = 0;
$timelineIndex = -1;
$contentIndex = -1;
while($row = mysql_fetch_assoc($result)){
    if($tl_ID != $row['tl_ID']){
        $timelineIndex++;
        $contentIndex = -1;
        $tl_ID = $row['tl_ID'];
        $jsonData[$timelineIndex]['tl_ID'] = $row['tl_ID'];
        $jsonData[$timelineIndex]['tl_name'] = $row['tl_name'];
        $jsonData[$timelineIndex]['tl_date'] = $row['tl_date'];
        $jsonData[$timelineIndex]['tl_desc'] = $row['tl_desc'];
        $jsonData[$timelineIndex]['content'] = array();
    }
    if($content_ID != $row['content_ID']){
        $contentIndex++;
        $content_ID = $row['content_ID'];
        $jsonData[$timelineIndex]['content'][$contentIndex]['content_ID'] = $row['content_ID'];
        $jsonData[$timelineIndex]['content'][$contentIndex]['content_time'] = $row['content_time'];
        $jsonData[$timelineIndex]['content'][$contentIndex]['content_date'] = $row['content_date'];
        $jsonData[$timelineIndex]['content'][$contentIndex]['content_title'] = $row['content_title'];
        $jsonData[$timelineIndex]['content'][$contentIndex]['content_content'] = $row['content_content'];
        $jsonData[$timelineIndex]['content'][$contentIndex]['content_category'] = $row['content_category'];

        $jsonData[$timelineIndex]['content'][$contentIndex]['pictures'] = array();
    }
    $jsonData[$timelineIndex]['content'][$contentIndex]['pictures'][] = array(
        'pic_ID' => $row['pic_ID'],
        'pic_path' => $row['pic_path'],
        'pic_desc' => $row['pic_desc'],
        'pic_link' => $row['pic_link']
    );
}
echo stripslashes(json_encode($jsonData));
}

Answer 1

编辑完毕，试试这个：

$timeline['timeline'][] = array(
        'tl_ID' => $row['tl_ID'],
        'tl_name' => $row['tl_name'],
        'tl_date' => $row['tl_date'],
        'tl_desc' => $row['tl_desc'],
        'content' => array(
          'content_ID' => $row['content_ID'],
          'tl_ID' => $row['tl_ID'],
          'content_time' => $row['content_time'],
          'content_date' => $row['content_date'],
          'content_title' => $row['content_title'],
          'content_content' => $row['content_content'],
          'content_category' => $row['content_category'],
          'content_mapLat' => $row['content_mapLat'],
          'content_mapLng' => $row['content_mapLng'],
          'content_zoomLvl' => $row['content_zoomLvl'],
          'pictures' => array(
            'pic_ID' => $row['pic_ID'],
            'content_ID' => $row['content_ID'],
            'pic_path' => $row['pic_path'],
            'pic_desc' => $row['pic_desc'],
            'pic_link' => $row['pic_link']
          );      
        );
);