重新构造PHP数组以正确地将数据放入html表中
重新构造PHP数组以正确地将数据放入html表中
提问于 2018-08-14 07:39:39
我正在尝试显示数据，以显示1月至12月的每个月的地点计数。
我编写了这个查询来从服务器获取必要的数据，但是当我在我的表的视图中显示它时，数据的布局看起来并不像我想要的那样。我相信我必须首先以正确的格式重建我在查询中编写的数组，然后才能在我的表中显示它？
现在，从服务器返回的数据如下所示
<table id="registeredTable" class="table table-striped table-bordered table-hover">
<thead>
<tr>
<th>Locations</th>
<th>January</th>
<th>Feburary</th>
<th>March</th>
<th>April</th>
<th>May</th>
<th>June</th>
<th>July</th>
<th>August</th>
<th>September</th>
<th>October</th>
<th>November</th>
<th>December</th>
</tr>
</thead>
<tbody>
<?php
foreach($selectAllmonthlylocations as $item) {
echo '
<tr>
<td>'.$item["locations"].'</td>
<td>'.$item["Total"].'</td>
</tr>
';
}
?>
</tbody>
</table>
所以我的问题是，我如何正确地格式化我的数组，以便在我的表中以我想要的方式显示它？
Array
(
[0] => Array
(
[usergroup] => Austin_Domestic
[DateAdded] => 2018-01-03
[Total] => 15
)
[1] => Array
(
[usergroup] => Austin_International
[DateAdded] => 2018-01-25
[Total] => 2
)
[2] => Array
(
[usergroup] => BayArea_Domestic
[DateAdded] => 2018-01-16
[Total] => 192
)
[3] => Array
(
[usergroup] => BayArea_International
[DateAdded] => 2018-01-05
[Total] => 28
)
[4] => Array
(
[usergroup] => Bengaluru_Domestic
[DateAdded] => 2018-01-10
[Total] => 2
)
[5] => Array
(
[usergroup] => Bengaluru_International
[DateAdded] => 2018-01-05
[Total] => 1
)
[6] => Array
(
[usergroup] => Cork
[DateAdded] => 2018-01-02
[Total] => 31
)
[7] => Array
(
[usergroup] => CulverCity
[DateAdded] => 2018-01-10
[Total] => 3
)
[8] => Array
(
[usergroup] => Denver
[DateAdded] => 2018-01-10
[Total] => 1
)
[9] => Array
(
[usergroup] => Hyderabad
[DateAdded] => 2018-01-05
[Total] => 3
)
[10] => Array
(
[usergroup] => London
[DateAdded] => 2018-01-02
[Total] => 10
)
[11] => Array
(
[usergroup] => Macau
[DateAdded] => 2018-01-17
[Total] => 1
)
[12] => Array
(
[usergroup] => Munich
[DateAdded] => 2018-01-02
[Total] => 6
)
[13] => Array
(
[usergroup] => Sacramento_Domestic
[DateAdded] => 2018-01-04
[Total] => 1
)
[14] => Array
(
[usergroup] => Shanghai
[DateAdded] => 2018-01-12
[Total] => 2
)
[15] => Array
(
[usergroup] => Singapore
[DateAdded] => 2018-01-03
[Total] => 8
)
[16] => Array
(
[usergroup] => Sydney
[DateAdded] => 2018-01-21
[Total] => 1
)
[17] => Array
(
[usergroup] => Tokyo
[DateAdded] => 2018-01-04
[Total] => 3
)
[18] => Array
(
[usergroup] => Austin_Domestic
[DateAdded] => 2018-02-01
[Total] => 31
)
[19] => Array
(
[usergroup] => Austin_International
[DateAdded] => 2018-02-19
[Total] => 2
)
[20] => Array
(
[usergroup] => Bangkok
[DateAdded] => 2018-02-07
[Total] => 1
)
[21] => Array
(
[usergroup] => BayArea_Domestic
[DateAdded] => 2018-02-08
[Total] => 165
)
[22] => Array
(
[usergroup] => BayArea_International
[DateAdded] => 2018-02-12
[Total] => 29
)这里是我的代码：
$selectallmonthlysql = 'SELECT locations, DateAdded, COUNT(*) as Total
FROM testserverdataSignup
WHERE DateAdded > "2017-12-31"
GROUP BY month(DateAdded), locations';
$selectAllmonthlystmt = $dbo->prepare($selectallmonthlysql);
$selectAllmonthlystmt->execute();
$selectAllmonthlylocations = $selectAllmonthlystmt->fetchAll(PDO::FETCH_ASSOC);表格当前外观的屏幕截图:我希望每个位置的个人计数与月份相关，并跨越而不是向下
回答 2
Stack Overflow用户
回答已采纳
发布于 2018-08-14 07:55:40
首先更改您的查询
$selectallmonthlysql = '
SELECT
locations,
MONTHNAME(DateAdded) AS month,
COUNT(*) AS total
FROM
testserverdataSignup
WHERE
DateAdded > "2017-12-31"
GROUP BY month(DateAdded),locations';我把Total降级为total，这让我很恼火。
使用PDO，您可以
$results = $selectAllmonthlystmt->fetchAll(PDO::FETCH_GROUP);
foreach($results AS $location => $row ){FETCH_GROUP的文档很少，但它将使用第一列作为嵌套数组的键来组织数据，在本例中是位置，这正是我们想要的。我使用PDO而不是其他DB库的原因之一，我不会提到它的名字，但它以MySql开头，以i结束。
MONTHNAME (单击查看文档)将返回月份的全名，如“二月”，虽然不是必需的(只需一个数字即可)，但在输出数组进行调试时更易于阅读。
你会得到类似这样的结果
//canned example data, notice one month is out of order and the big gap
// between February to October, this is to show it properly orders and
//fills those in. Always best to test this situations.
$result = array(
"Austin_Domestic" => array(
0 => Array(
"month" => "February",
"total" => 5
),
1 => Array(
"month" => "January",
"total" => 15
),
2 => Array(
"month" => "October",
"total" => 8
),
),
);
//$results = $selectAllmonthlystmt->fetchAll(PDO::FETCH_GROUP);
//then putting it all together
//we'll need the months in an array anyway so we can saves some space with them in the HTML too.
$months = array(
"January",
"February",
"March",
"April",
"May",
"June",
"July",
"August",
"September",
"October",
"November",
"December"
)
?>
<table id="registeredTable" class="table table-striped table-bordered table-hover">
<thead>
<tr>
<th>Locations</th>
<?php foreach($months as $month){
//Output the names of the months
echo "<th>{$month}</th>\n";
} ?>
</tr>
</thead>
<tbody>
<?php
foreach( $result as $location => $rows ){
echo "<tr>\n";
echo "<td>{$location}</td>\n";
//simplifies lookup by month (indexes are correlated)
//it returns column 'month' from $rows as a flat array.
//we can save ourselves an external loop by doing this "trick"
$m = array_column($rows, 'month');
//for example, in the case: [0=>February,1=>January,2=>October]
//because this is created from $rows, with a natural number index
//we can simply do array_search on $m to get the index we need in $rows (see below)
foreach($months as $month){
$index = array_search($month, $m);
if(false === $index){
echo "<td>0</td>\n";
}else{
echo "<td>{$rows[$index]['total']}</td>\n";
}
}
echo "</tr>\n";
}
?>
</tbody>
</table>输出(我将其格式化以使其看起来更好一些)：
<table id="registeredTable" class="table table-striped table-bordered table-hover">
<thead>
<tr>
<th>Locations</th>
<th>January</th>
<th>February</th>
<th>March</th>
<th>April</th>
<th>May</th>
<th>June</th>
<th>July</th>
<th>August</th>
<th>September</th>
<th>October</th>
<th>November</th>
<th>December</th>
</tr>
</thead>
<tbody>
<tr>
<td>Austin_Domestic</td>
<td>15</td>
<td>5</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>0</td>
<td>8</td>
<td>0</td>
<td>0</td>
</tr>
</tbody>
</table>这几个月来使用数组的一个注意事项和好处是，你把Feburary写错了，它应该是February，这是我犯过很多次的错误。
唯一的缺点是月份名称必须与从DB返回的月份名称相匹配，如果拼写正确，这应该不是问题(这就是我发现错误的原因)。否则，这可以应用于月份作为一个数字，同样容易，相同的想法。
干杯!
Stack Overflow用户
发布于 2018-08-14 07:58:17
首先，您应该更改查询以返回MONTH(DateAdded)而不是整个日期，因为这是输出表的组织方式：
$selectallmonthlysql = 'SELECT locations, MONTH(DateAdded) AS month, COUNT(*) as Total
FROM testserverdataSignup
WHERE DateAdded > "2017-12-31"
GROUP BY month, locations';然后，您应该将数组重新组织为嵌套数组：
array(location1 => array(month1 => total1, month2 => total2, ...),
location2 => array(month1 => total1, month2 => total2, ...),
...)这段代码将会做到这一点：
$groupedData = array();
while ($selectAllmonthlystmt->fetch(PDO::FETCH_ASSOC) {
$groupedData[$row['locations']][$row['month']] = $row['Total'];
}然后，您可以在创建表时使用嵌套循环。外层循环创建行，内层循环用于行中的单元格。
foreach ($groupedData as $location => $locationData) {
echo "<tr><td>$location</td>";
for ($month = 1; $month <= 12; $month++) {
echo "<td>";
echo isset($locationData[$month]) ? $locaitonData[$month] : 0;
echo "</td>";
}
echo "</tr>";
}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51831910
复制相关文章
相似问题