php - 将 MySQL 连接转换为 JSON 数组

Question

我有一个名为 calls 的表和一个名为 uploads 的表，其结构如下:

通话

╔═════════╦═══════════╦═════════════════════╗
║ call_id ║ caller_id ║ call_time           ║
╠═════════╬═══════════╬═════════════════════╣
║ 235     ║ 23        ║ 2017-11-01 12:47:27 ║
╠═════════╬═══════════╬═════════════════════╣
║ 259     ║ 65        ║ 2017-11-02 16:58:27 ║
╚═════════╩═══════════╩═════════════════════╝

上传

╔═══════════╦═════════╦═════════════════════╗
║ upload_id ║ call_id ║ file_name           ║
╠═══════════╬═════════╬═════════════════════╣
║ 145       ║ 235     ║ bu2t7384uhjnfns.mp3 ║
╠═══════════╬═════════╬═════════════════════╣
║ 146       ║ 235     ║ jbwer8y23gr92o.mp3  ║
╚═══════════╩═════════╩═════════════════════╝

然后我有一个连接两个表的查询:

SELECT calls.*, uploads.*
FROM cads
LEFT OUTER JOIN uploads ON uploads.call_id = 235

当我在 codeigniter 中使用 return $query->result_array() ，然后使用 json_encode 时，它会返回一个包含两个 call 元素的数组:

calls:(2) [{…}, {…}] 

我希望 JSON 对象中只有一个 call 元素，但有一个名为 uploads 的键，它是上传表中的一组上传内容，例如像这样的东西:

calls: Array(1)
  0:
    call_id: 235
    caller_id: 23
    call_time: 2017-11-01 12:47:27
    uploads: Array(2)
       0: 
         upload_id: 145
         call_id: 235
         file_name: bu2t7384uhjnfns.mp3
       1:
         upload_id: 146
         call_id: 235
         file_name: jbwer8y23gr92o.mp3

如果有人能够向我展示如何在 Handlebars-JS 中显示上述 JSON，我们将获得奖励积分:)

Answer 1

假设$results是从数据库返回的结果，如下所示

array (size=2)
  0 => array (size=5)
      'call_id' => string '235' (length=3)
      'caller_id' => string '23' (length=2)
      'call_time' => string '2017-11-01 12:47:27' (length=19)
      'upload_id' => string '145' (length=3)
      'file_name' => string 'bu2t7384uhjnfns.mp3' (length=19)
  1 => array (size=5)
      'call_id' => string '235' (length=3)
      'caller_id' => string '23' (length=2)
      'call_time' => string '2017-11-01 12:47:27' (length=19)
      'upload_id' => string '146' (length=3)
      'file_name' => string 'jbwer8y23gr92o.mp3 ' (length=19)

这是根据您的要求重新格式化两个数据库行的一种方法。

$calls = $results[0];
//remove unwanted indexes
unset($calls['upload_id'], $calls['upload_id'], $calls['file_name']);
//gather the upload sub-arrays
foreach($results as $result)
{
     //remove unwanted indexes
     unset($result['caller_id'], $result['call_time']);
     //add sub-array to "upload" 
     $calls['upload'][] = $result;
}
$data = array($calls); //put it all into an array
$encode = json_encode($data);
var_dump($encode);

var_dump($encode) 生成

[[{"call_id":"235","caller_id":"23","call_time":"2017-11-01 12:47:27","upload":[{"call_id":"235","upload_id":"145","file_name":"bu2t7384uhjnfns.mp3"},{"call_id":"235","upload_id":"146","file_name":"jbwer8y23gr92o.mp3 "}]}]]' (length=223)