gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-11-29 11:30:15 24 4
gpt4 key购买 nike

我有一个包含 1000 条记录的表和 5 年的相应数据历史记录,包括事件。目前的表结构如下所示:

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|||

我想根据 account_id(在本例中 account_id = 1)渲染数据以获得以下 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"
}]
}]
}

不过,我正在努力构建 json。截至目前,我正在渲染数据,如下所示:

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

显然,这会返回 json,其中价格作为每个记录值(价格)的标签。查询应该如何呈现上面的 json 示例?

最佳答案

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

关于javascript - 从 mysql 渲染复杂的 JSON 并使用列值作为标签,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/37532617/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com