在Codeigniter中生成JSON查询结果

So i have database table named usermeta and have table structure like this :

-----------------------------------------------------------
| ummeta_id | user_id | meta_key    | meta_value          |
-----------------------------------------------------------
| 1         | 1       | fullname    | John Doe            |
| 2         | 1       | birthplace  | New York            |
| 3         | 1       | birthdate   | 1990/01/01          |
| 4         | 1       | mobile      | 0812-3456-7890      |
| 5         | 1       | email       | john.doe@mail.com   |
| 6         | 2       | fullname    | Jon Wick            |
| 7         | 2       | birthplace  | Washington DC       |
| 8         | 2       | birthdate   | 1985/10/21          |
| 9         | 2       | mobile      | 0890-1234-5678      |
| 10        | 2       | email       | wickjohn@mail.com   |

And i try to generate json data for all data from this database using Codeigniter (v 3.1.9) using Controller and Model.

This is my Model (model name: db_usermeta)

function userslist()
{
   $query = $this->db->select('*')
                     ->from('usermeta')
                     ->get();
   return $query->result();
}

This is my Controller

public function userlist()
{
   header('Content-Type: application/json; charset=utf-8');
   $query = $this->db_usermeta->userslist();
   $json_data = array();
   foreach ($query as $key)
   {
      $json_data[$key->meta_key] = $key->meta_value;
   }
   echo json_encode($json_data);
}

The result when i open using my browser to check the json data using web developer tool is only show last record, in this case only show data from user_id 2, like this:

{
  "fullname":"John Wick",
  "birthplace":"Washinton DC",
  "birthdate":"1985/10/21",
  "mobile":"0890-1234-5678",
  "email":"wickjohn@mail.com"
}

What I want to achieve is to show all json data nested like this:

"data": [
  {
    "fullname":"John Doe",
    "birthplace":"New York",
    "birthdate":"1990/01/01",
    "mobile":"0812-3456-7890",
    "email":"john.doe@mail.com"
  },
  {
    "fullname":"John Wick",
    "birthplace":"Washinton DC",
    "birthdate":"1985/10/21",
    "mobile":"0890-1234-5678",
    "email":"wickjohn@mail.com"
  }
]

How can i achieve this? Did I make a mistake on my controller and model. I really appreciate your help.

your $key->meta_key is overwriting for every record. that's why only last record appeared. You don't actually need to loop through to get json data.

public function userlist()
{
   header('Content-Type: application/json; charset=utf-8');
   $query = $this->db_usermeta->userslist();
   $json_data = array(array());
   $user_id_map = array();
   $index = 0;
   foreach ($query as $key)
   {
        if(!isset($user_id_map[$key->user_id])){
            $user_id_map[$key->user_id] = $index++;
        }
        $currentIndex = $user_id_map[$key->user_id];
        $json_data[$currentIndex][$key->meta_key] = $key->meta_value;
   }
   echo json_encode($json_data);
}

just change your controller code to this and this will return json data.

Since the meta key fullname is same for both records, you need to change the key name to something unique

foreach ($query as $key)
   {
      $json_data[$key->meta_key] = $key->meta_value;
   }

Change $json_data[$key->meta_key] to $json_data[$key->meta_key.$key->user_id] or simply change it to $json_data[$key->ummeta_id]