How to make looping condition inside particular values in while loop

[itsdarshan]

Dear All,

Here I am using three while loop for fetch these Data

this is my code:

$sql='SELECT  * FROM `booking_items`
INNER JOIN products ON booking_items.product_id=products.product_id
INNER JOIN product_category ON products.pcat_id=product_category.pcat_id
INNER JOIN size_master ON booking_items.size_master_id=size_master.size_master_id
INNER JOIN booking_details ON booking_details.booking_id=booking_items.booking_id
INNER JOIN student_reg ON booking_details.student_id=student_reg.student_id
INNER JOIN school_master ON student_reg.school_id=school_master.school_id
WHERE booking_items.created_date BETWEEN :bdate AND :bdate1 order by 
booking_items.booking_id asc';
  $query = $conn->prepare($sql);
  $query->bindParam(':bdate', $bdate);
  $query->bindParam(':bdate1', $bdate1);
  $query->execute();
  if($query->rowCount())
  {
     while($row = $query->fetch(PDO::FETCH_ASSOC))
     {
         ?>
          <tr>
          <td > <?php echo $i++; ?> </td> 
          <td >   <?php echo  $dddd= $row['booking_id'];?></td>
          <td >   <?php echo   $row['school_name'];?></td>
          <td  >   <?php echo   $row['prod_cat_name'];?></td>
          <td  >  <?php   $totamt = $row['grand_amount']; echo  $row['prod_name'];?></td>
          <td  >   <?php echo $row['size'];echo " &nbsp; ";   echo $row['size_note'];?></td>
          <td >   <?php echo   $row['quantity'];?></td>
          <td >   <?php 

            $sql='SELECT pt.booking_id, SUM(pt.grand_amount) AS tot  FROM `booking_items` AS pt
                            WHERE pt.booking_id=:dddd';
                             
                             $querys = $conn->prepare($sql);
                             
                             
                             $querys->bindParam(':dddd', $dddd);
                             $querys->execute();
                             
                             if($querys->rowCount())
                             {
                                 
                                 while($rows = $querys->fetch(PDO::FETCH_ASSOC))
                                 {
                                     
                                  echo  $totalamt=$rows['tot'];  

                          $sql='SELECT pt.booking_id, SUM(pt.paid_amt) AS tot  FROM `pymt_trans` AS pt 
                                  WHERE pt.booking_id=:dddd';
                             
                             $querys = $conn->prepare($sql);
                             
                             
                             $querys->bindParam(':dddd', $dddd);
                             $querys->execute();
                             
                             if($querys->rowCount())
                             {
                                 
                                 while($rows = $querys->fetch(PDO::FETCH_ASSOC))
                                 {
                                     
                                     echo "</td>";
                                     echo "<td>";
                                     echo $paid1=$rows['tot'];  
                                         
                                         echo "</td>";
                                         echo "<td>";
                                         
                                         echo $rem=$paid-$paid1;
                                 }
                             }
                      }
              }
                            
                              	     
        }
                       
                         
                      
}

Here i want to show Bill amount & paid amount & balance will show only once not repeating depending on the booking id

any suggestions…?
thank you,

[astonecipher]

You need to add a Group by clause

[phdr]

As posted in one of your previous threads for this, because there can be more than one row in the booking_items table per booking_id and zero or more rows in the payment (pymt_trans) table per booking_id, one straight forward way to do this is to use a UNION ALL query to get these amounts in one query.

The following is the portion of the query needed to get the data for the last thee columns of your output -

SELECT booking_id, SUM(amt_due) 'Bill Amount', SUM(amt_paid) 'Paid Amount', SUM(amt_due-amt_paid) 'Balance Amount' FROM (
  SELECT booking_id, SUM(grand_amount) amt_due, 0 amt_paid
  FROM booking_items
  GROUP BY booking_id
 UNION ALL
  SELECT booking_id, 0, SUM(paid_amt)
  FROM pymt_trans
  GROUP BY booking_id
) x GROUP BY booking_id

Some of the columns you have listed - stock category, stock name, and size, don’t mean anything in terms of the amounts per booking_id and shouldn’t be selected (you should in fact list out the columns you are selecting.) You could SUM() the quantity to get the total number of items per booking_id.