dongren4099 2016-05-30 19:36
浏览 73
已采纳

从mysql渲染复杂的JSON并使用列值作为标签

I have a table with 1000 records and a corresponding data history of 5 years, including events. The table structure looks like this at the moment:

id|date|reference_id|account_id|dataSet|price|title|type|description

1|2006-01-03|ID00001|1|dataSet01|44.23|Analyst opinion change|A|Upgrade by Bank from Sell to Hold
2|2006-01-03|ID00002|1|dataSet02|62.75|||
3|2006-01-03|ID00003|1|dataSet03|25.95|Dividend|D|Amount: 0.22
4|2006-01-03|ID00004|2|dataSet04|31.81|||
5|2006-01-03|ID00005|3|dataSet05|78.20|||
6|2006-02-01|ID00001|1|dataSet01|45.85|Dividend|D|Amount: 0.30
7|2006-02-01|ID00002|1|dataSet02|59.37||
8|2006-02-01|ID00003|1|dataSet03|27.59|Dividend|D|Amount: 0.26
9|2006-02-01|ID00004|2|dataSet04|34.24|||
10|2006-02-01|ID00005|3|dataSet05|83.42|||
11|2006-03-01|ID00001|1|dataSet01|45.54|Analyst opinion change|A|Upgrade by Bank from Sell to Hold
12|2006-03-01|ID00002|1|dataSet02|60.86|||
13|2006-03-01|ID00003|1|dataSet03|27.04|Downgrade by Bank from Buy to Hold
14|2006-03-01|ID00004|2|dataSet04|36.04|||
15|2006-03-01|ID00005|3|dataSet05|84.32|||

I want to render the data depending on account_id (in this case account_id = 1) to get the following JSON:

{
"data": [{
    "date": "2006-01-03",
    "dataSet01": "44.23",
    "dataSet02": "62.75",
    "dataSet03": "25.95"
}, {
    "date": "2006-02-01",
    "dataSet01": "45.85",
    "dataSet02": "59.37",
    "dataSet03": "27.59"
}, {
    "date": "2006-03-01",
    "dataSet01": "45.54",
    "dataSet02": "60.86",
    "dataSet03": "27.04"
}],
"events": [{
    "dataSet01": [{
        "date": "2006-01-03",
        "title": "Analyst opinion change",
        "text": "A",
        "description": "Upgrade by Bank from Sell to Hold"
    }, {
        "date": "2006-02-01",
        "title": "Dividend",
        "text": "D",
        "description": "Amount: 0.30"
    }, {
        "date": "2006-03-01",
        "title": "Analyst opinion change",
        "text": "A",
        "description": "Upgrade by Bank from Sell to Hold"
    }]
},{
    "dataSet03": [{
        "date": "2006-01-03",
        "title": "Analyst opinion change",
        "text": "A",
        "description": "Upgrade by Bank from Sell to Hold"
    }, {
        "date": "2006-02-01",
        "title": "Dividend",
        "text": "D",
        "description": "Amount: 0.30"
    }, {
        "date": "2006-03-01",
        "title": "Analyst opinion change",
        "text": "A",
        "description": "Downgrade by Bank from Buy to Hold"
    }]
}]
}

I'm struggling to build the json though. As of right now I'm rendering the data like this:

$query = "SELECT date, price 
FROM datatable
WHERE account_id = 1
ORDER BY date ASC";
$result = mysql_query( $query );

$data = array();
while ( $row = mysql_fetch_assoc( $result ) ) {
$data[] = $row;
}

return json_encode( $data );

Obviously this returns the json with price as label for each record value (price). How should the query look like instead to render the above json example?

  • 写回答

1条回答 默认 最新

  • doujianchao7446 2016-05-30 21:39
    关注
    $query = "SELECT * 
              FROM datatable
              WHERE account_id = 1
              ORDER BY date ASC";
    
    $result = mysql_query( $query );
    
    // Define temporary arrays
    $data = array();
    $events = array();
    
    while ( $row = mysql_fetch_assoc( $result ) ) {
    
       // Assemble the data grouped by date and dataset
       if ( !isset($data[$row['date']]) )
       {
          $data[$row['date']] = array(
              'date' => $row['date'],
          );
       }
    
       // Inject dataSet in $data grouped by date
       if ( !isset($data[$row['date']][$row['dataSet']]) )
       {
          $data[$row['date']][$row['dataSet']] = $row['price'];
       }
    
       // Assemble events grouped by dataSet
       if ( !isset($events[$row['dataSet']]) )
       {
          $events[$row['dataSet']] = array();
       }
    
       $events[$row['dataSet']][] = array(
          'date' => $row['date'],
          'title' => $row['title'],
          'text' => $row['type'],
          'description' => $row['description']
       );
    }
    
    // Remove date keys
    $data = array_values($data);
    
    return json_encode(array(
        'data' => $data,
        'events' => $events
    ));
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

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