嵌套在MySQL（PHP）中的JSON

Question

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",
   ...
   }

Answer 1

$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));