如何使用MySQL存储过程返回值数组

Question

我没有关于存储过程的经验（mysql）。我想如下返回JSON数据，

[
  {
    'id': 1,
    'name': 'ABC',
    'children': [
      {
        'id': 1,
        'name': 'Ana',
        'sex': 1
      },
      {
        'id': 2,
        'name': 'John',
        'sex': 0
      },
      {
        'id': 3,
        'name': 'Max',
        'sex': 0
      }
    ]
  },
  {
    'id': 2,
    'name': 'XYZ',
    'children': [
      {
        'id': 1,
        'name': 'Bob',
        'sex': 1
      },
      {
        'id': 2,
        'name': 'Mike',
        'sex': 0
      },
      {
        'id': 3,
        'name': 'Sara',
        'sex': 1
      }
    ]
  }
]

我的表格

父表

id int 10,
name varchar 30

儿童桌

id int 10,
name varchar 30,
sex tinyint 1,
parent_id int 10

在这里，我现在可以返回整个对象的数组。但是我不知道如何在每个对象上返回孩子们里面的孩子。

Answer 1

使用codeigniter：

首先，在parent和child表上具有两个查询的模型：

class Json_model extends CI_Model {
    public function get_hierarchy_json(){
        //get all records from `parent`
        $sql = "select * from parent order by id";
        $parent = $this->db->query($sql)->result();
        //get all records from `child`
        $sql = "select * from child order by parent_id";
        $childs = $this->db->query($sql)->result();
        //build the hierarchy tree comparing parent.id and child.parent_id
        $current_parent = 0;
        foreach ($childs as $index => $child) {
            if ($parent[$current_parent]->id == $child->parent_id) {
                $parent[$current_parent]->childs[] = $child;
            }else{
                while (isset($parent[$current_parent]) &&
                       $parent[$current_parent]->id != $child->parent_id) {
                    $current_parent++;
                }
                $parent[$current_parent]->childs[] = $child;
            }
        }
        return  $parent;
    }
}

第二，打印模型结果的控制器作为JSON格式化文本：

class Welcome extends AppController {
    public function __construct(){
        $this->load->model('json_model');
    }
    public function json_test(){
        echo json_encode($this->json_model->get_hierarchy_json());
    }
}

第三，打开URL/WELLE/JSON_TEST和VOILA：

[
   {
      "id":"1",
      "name":"ABC",
      "childs":[
     {
        "id":"1",
        "name":"Ana",
        "sex":"1",
        "parent_id":"1"
     },
     {
        "id":"2",
        "name":"Jhon",
        "sex":"0",
        "parent_id":"1"
     },
     {
        "id":"3",
        "name":"Max",
        "sex":"0",
        "parent_id":"1"
     }
      ]
   },
   {
      "id":"2",
      "name":"XYZ",
      "childs":[
     {
        "id":"4",
        "name":"Bob",
        "sex":"1",
        "parent_id":"2"
     },
     {
        "id":"5",
        "name":"Mike",
        "sex":"0",
        "parent_id":"2"
     },
     {
        "id":"6",
        "name":"Sara",
        "sex":"1",
        "parent_id":"2"
     }
      ]
   }
]