dtx3006 2015-10-06 02:03
浏览 30
已采纳

嵌套在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 2015-10-07 20:24
    关注
    $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));
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题