phpsortingresultset

Merge two query result sets and sort by date column


I have a little script on which I am working where I can generate income and expense reports in a nice table, however, I am currently facing some issues with the sort order. Here is my current code:

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

Here a screenshot of the output:

enter image description here

Essentially the output and the values are correct, however, the 2 items I've marked in red (which are Cash out) are not sorted by date as my other values for expenses but add to the top. My ideal goal is to have them all sorted seamlessly by ExpenseDate.


Solution

  • You should merge your two queries into one, and then generate the table output based on the value of ExpenseType e.g.

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

    then in your loop:

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