php - 数组中按日期排序的PHP问题
问题描述
我有一个小脚本,我可以在其中生成一个漂亮的表格中的收入和支出报告,但是,我目前在排序顺序方面遇到了一些问题。这是我当前的代码:
<div class="panel-body">
<div class="col-md-12">
<?php
$fdate=$_POST['fromdate'];
$tdate=$_POST['todate'];
$rtype=$_POST['requesttype'];
?>
<?php
$source1 = $_POST['fromdate'];
$date1 = new DateTime($source1);
$source2 = $_POST['todate'];
$date2 = new DateTime($source2);
?>
<h5 align="center" style="color:#30a5ff">Expense Report from <?php echo $date1->format('d/m/Y')?> to <?php echo $date2->format('d/m/Y')?></h5>
<hr />
<!-- table start--->
<table class="table table-bordered mg-b-0">
<thead>
<tr>
<th>Date</th>
<th>Description</th>
<th>Category</th>
<th style="text-align:right;">Cash In</th>
<th style="text-align:right;">Cash Out</th>
<th style="text-align:right;background: yellow;">Balance THB</th>
</tr>
</thead>
<?php
$total_e = 0;
$userid=$_SESSION['detsuid'];
$ret=mysqli_query($con,"select * from tblexpense where (ExpenseDate BETWEEN '$fdate' and '$tdate') && (ExpenseType='Expense') && UserId='$userid' ORDER BY ExpenseDate ASC");
$cnt=1;
while ($row=mysqli_fetch_array($ret)) {
$total_e = $total_e+$row['ExpenseCost']
?>
<tbody>
<tr>
<td><?php echo date('d/m/Y', strtotime($row["ExpenseDate"]));?></td>
<td><?php echo $row['ExpenseItem'];?></td>
<td><?php echo $row['ExpenseCat'];?></td>
<td></td>
<td style="text-align:right;"><?php echo number_format($row['ExpenseCost'], 2);?> THB</td>
<td style="text-align:right;"><?php echo number_format($row['ExpenseCost'], 2);?> THB</td>
</tr>
<?php
$cnt=$cnt+1;
}?>
<?php
$total_i =0;
$userid=$_SESSION['detsuid'];
$ret=mysqli_query($con,"select * from tblexpense where (ExpenseDate BETWEEN '$fdate' and '$tdate') && (ExpenseType='Income') && UserId='$userid' ORDER BY ExpenseDate ASC");
$cnt=1;
while ($row=mysqli_fetch_array($ret)) {
$total_i = $total_i+$row['ExpenseCost'];
?>
<tbody>
<tr>
<td><?php echo date('d/m/Y', strtotime($row["ExpenseDate"]));?></td>
<td><?php echo $row['ExpenseItem'];?></td>
<td></td>
<td style="text-align:right;"><?php echo number_format($row['ExpenseCost'], 2);?> THB</td>
<td></td>
<td style="text-align:right;"><?php echo number_format($row['ExpenseCost'], 2);?> THB</td>
</tr>
<?php
$cnt=$cnt+1;
}?>
<tr>
<th colspan="2" style="text-align:left">Grand Total</th>
<th></th>
<th style="text-align:right;"><?php echo number_format($total_i, 2);?> THB</th>
<th style="text-align:right;"><?php echo number_format($total_e, 2);?> THB</th>
<td style="text-align:right; font-weight:bold; background: yellow;"> <?php $ret=mysqli_query($con,"SELECT ExpenseType, SUM(ExpenseCost) FROM tblexpense where (ExpenseDate BETWEEN '$fdate' and '$tdate') && (ExpenseType='Expense') && UserId='$userid' ORDER BY ExpenseDate GROUP BY ExpenseType");
$e_sum = 0;
while ($row=mysqli_fetch_array($ret)) {
//$e_sum = number_format($row['SUM(ExpenseCost)'], 2);
$e_sum = $row['SUM(ExpenseCost)'];
}
?>
<?php $ret=mysqli_query($con,"SELECT ExpenseType, SUM(ExpenseCost) FROM tblexpense where (ExpenseDate BETWEEN '$fdate' and '$tdate') && (ExpenseType='Income') && UserId='$userid'ORDER BY ExpenseDate GROUP BY ExpenseType");
$i_sum = 0;
while ($row=mysqli_fetch_array($ret)) {
//$i_sum = number_format($row['SUM(ExpenseCost)'], 2);
$i_sum = $row['SUM(ExpenseCost)'];
}
echo number_format($i_sum-$e_sum,2);
?> THB</td>
</th>
</tr>
</tbody>
</table>
<!-- table end--->
</div>
</div>
这是输出的屏幕截图:
基本上输出和值是正确的,但是,我用红色标记的 2 个项目(即 Cash out)没有按日期排序,因为我的其他费用值而是添加到顶部。我的理想目标是让它们按 ExpenseDate 无缝排序。实现这一目标的最佳方法是什么?一些专家的帮助将不胜感激。
解决方案
您应该将两个查询合并为一个,然后根据ExpenseType
例如的值生成表输出
$ret=mysqli_query($con,"select *
from tblexpense
where (ExpenseDate BETWEEN '$fdate' and '$tdate')
and (ExpenseType='Expense' or ExpenseType='Income')
and UserId='$userid'
ORDER BY ExpenseDate ASC");
然后在你的循环中:
<tr>
<td><?php echo date('d/m/Y', strtotime($row["ExpenseDate"]));?></td>
<td><?php echo $row['ExpenseItem'];?></td>
<td><?php echo $row['ExpenseType'] = 'Expense' ? $row['ExpenseCat'] : '';?></td>
<td style="text-align:right;"><?php echo $row['ExpenseType'] = 'Expense' ? '' : number_format($row['ExpenseCost'], 2); ?> THB</td>
<td style="text-align:right;"><?php echo $row['ExpenseType'] = 'Expense' ? number_format($row['ExpenseCost'], 2) : '';?> THB</td>
<td style="text-align:right;"><?php echo number_format($row['ExpenseCost'], 2);?> THB</td>
</tr>
推荐阅读
- powershell - Powershell将文件从环境变量复制到另一个环境变量的zip文件夹
- python - 如何在创建父级时清除内部类属性
- r - 将列拆分为行,并更改 R 中新行的特定列值
- c# - OpenPop.Pop3.Exceptions.InvalidLoginException:“服务器不接受用户凭据”
- php - 更新列值 laravel
- reactjs - 错误:在 com.android.build.gradle.internal.api.ApplicationVariantImpl 类型的对象上找不到参数 [] 的方法 getCompileConfiguration()
- python - pip install nsenter 在 alpine docker 容器中超时
- security - 滚动你自己的加密货币
- apache-spark - 如何根据在pyspark中作为字符串传递的日期值创建数据框?
- java - 模拟注释方法时的NPE