dtx3006
dtx3006
2015-10-06 02:03

嵌套在MySQL(PHP)中的JSON

已采纳

Database table articles holds parent articles and articles variants. Variant articles have empty NAME fields, their PARENTID is related to the parents id. Not every article has variants.

parent articles:    ID, NAME   
variant articles:   ID, PARENTID, VARIANT

How can ι get a JSON like this? Only articles with child articles

{
  "ID": "dfe17b",
  "NAME": "this is a very good movie",
  "variants": [
    {
      "ID": "6bcb7",
      "VARIANT": "DVD",
    },
    {
      "ID": "75a9b",
      "VARIANT": "Blu-ray",
    },
    {
      "ID": "fe3b3",
      "VARIANT": "VHS",
    }
  ]
},
{
  "ID": "dfe156",
  "NAME": "this is a pretty nice movie",
  "variants": [
    {
      "ID": "5j4h7",
      "VARIANT": "online",
    },
    {
      "ID": "95c9s",
      "VARIANT": "Blu-ray",
    }
  ]
}

SQL:

SELECT o.ID, o.PARENTID, IFNULL(oa.TITLE, o.TITLE) AS TITLE,     o.VARNAME,       o.VARSELECT
FROM articles o
  LEFT JOIN articles oa
    ON oa.ID = o.PARENTID
WHERE o.VARNAME IS NOT NULL
ORDER BY TITLE ASC

gives

{
  "ID": "dfe17b",
  "NAME": "this is a very good movie",
  "VARIANT": "",
  ...
},
{
 "ID": "6bcb7",
  "NAME": "this is a very good movie",
  "VARIANT": "DVD",
   ...
},
{
"ID": "75a9b",
"NAME": "this is a very good movie",
"VARIANT": "Blu-ray",
...
},
{
"ID": "fe3b3",
"NAME": "this is a very good movie",   
"VARIANT": "VHS",
...
},
{
  "ID": "dfe156",
  "NAME": "this is a pretty nice movie",
  "VARIANT": "",
  ...
  },
  {
  "ID": "5j4h7",
  "NAME": "this is a pretty nice movie",
  "VARIANT": "online",
  ...
  },
  {
  "ID": "95c9s",
  "NAME": "this is a pretty nice movie",
  "VARIANT": "Blu-ray",
   ...
   }
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • dougu5886 dougu5886 6年前
    $parents = array();
    $sql = "SELECT ID, NAME FROM articles
    WHERE NAME!='' AND PARENTID=''
    ORDER BY NAME"; 
    $res = $db->db_query($sql);
    while ($row=$db->db_fetch_assoc($res)){
    $row['variants'] = array();
    $parents[$row['ID']] = $row;
    }
    
    # variants
    $sql = "SELECT ID,PARENTID,VARIANT FROM articles
    WHERE PARENTID!=''
    ORDER BY NAME"; 
    $res = $db->db_query($sql);
    while ($row=$db->db_fetch_assoc($res)){
    $parents[$row['PARENTID']]['variant'][] = $row;
    }
    
    header('Content-Type: application/json');
    echo json_encode(array_values($parents));
    
    点赞 评论 复制链接分享