How to make sum of values in the middle of while loop for all separated Id

[itsdarshan]

Hello,

This is what i am getting output after while loop

School Name    Product Category    Product Name    Size    Quantity

---

BGS                  Shirt                          Shirt32              20x30    5
BGS                  Shirt                          Shirt33              20x30    4
ASD                  Shirt                          Shirt34              20x30    2
BGS                  Shirt                          Shirt34              20x30    1
JAIN                  Pant                          Pant20              20x30    2
JAIN                  Pant                          Pant23              20x30    5
 
 

My Expected Output is

School Name    Product Category    Product Name    Size    Quantity

---

BGS                  Shirt                          Shirt32              20x30    5
BGS                  Shirt                          Shirt33              20x30    4
ASD                  Shirt                          Shirt34              20x30    2
BGS                  Shirt                          Shirt34              20x30    1
                                                                                     SUM: 12
JAIN                  Pant                          Pant20              20*30    2
JAIN                  Pant                          Pant23              20x30    5
                                                                                     SUM: 7

Is there any way in single while loop…?

thanks,

[phdr]

The simplest and most general purpose way of doing this is to pivot/index the data using the Product Category value when you fetch it, then simply loop over the data to produce the output. When you fetch the data, store it in an array of arrays using the Product Category value as the main array index, the stored sub-arrays are just the rows of fetched data. You will get an array that looks like this -

Array
(
    [Shirt] => Array
        (
            [0] => Array
                (
                    [School] => BGS
                    [Category] => Shirt
                    [Product] => Shirt32
                    [Size] => 20x30
                    [Quantity] => 5
                )

            [1] => Array
                (
                    [School] => BGS
                    [Category] => Shirt
                    [Product] => Shirt32
                    [Size] => 20x30
                    [Quantity] => 4
                )

            [2] => Array
                (
                    [School] => ASD
                    [Category] => Shirt
                    [Product] => Shirt34
                    [Size] => 20x30
                    [Quantity] => 2
                )

            [3] => Array
                (
                    [School] => BGS
                    [Category] => Shirt
                    [Product] => Shirt34
                    [Size] => 20x30
                    [Quantity] => 1
                )

        )

    [Pant] => Array
        (
            [0] => Array
                (
                    [School] => JAIN
                    [Category] => Pant
                    [Product] => Pant20
                    [Size] => 20x30
                    [Quantity] => 2
                )

            [1] => Array
                (
                    [School] => JAIN
                    [Category] => Pant
                    [Product] => Pant23
                    [Size] => 20x30
                    [Quantity] => 5
                )

        )

)

To produce the output -

foreach($result as $category=>$arr)
{
	// the $category variable is not used here, but typically, you would produce some type of heading display using it
	// calculate the total quantity
	$total = array_sum(array_column($arr,'Quantity'));
	foreach($arr as $row)
	{
		// display the content of $row here...
		
	}
	// output the category total
	echo "SUM: $total<br>";
}

An alternative method is to use a variable to detect when the product category value changes in a loop and to add up each quantity value, but this takes more logic to accomplish since you must have conditional logic to detect the change in the category value, store the new category value, initialize the quantity variable, and output the total when the category changes before starting the output for the new category.

[itsdarshan]

This is what i am using loop:

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

  $query = $conn->prepare($sql);

  $query->bindParam(':bdate', $bdate);
  $query->bindParam(':bdate1', $bdate1);
  $query->execute();
   if($query->rowCount())
   {
           ?>
         
   <div class="table-responsive">
   <table id="zero_config" class="table table-striped table-bordered">
   <thead>
   <tr>
              <th  style=" font-weight: 400; font-size: 13px;">School Name</th>
	  <th  style=" font-weight: 400; font-size: 13px;">Product Category</th>
	  <th  style=" font-weight: 400; font-size: 13px;">Product Name</th>
	  <th  style=" font-weight: 400; font-size: 13px;">Size</th>
	 <th  style=" font-weight: 400; font-size: 13px;">Quantity</th>
    </tr>
    </thead>
    <tbody>
     <?php 
       while($row = $query->fetch(PDO::FETCH_ASSOC))
                    {?> <tr>
                          <td style="  font-size: 12px;">   <?php  echo    $row['school_name'];?></td>
                            <td style="  font-size: 12px;">   <?ph  echo  $row['prod_cat_name'];?></td>
                           <td style="  font-size: 12px;">  <?php  echo $row['prod_name'];?></td>
                            <td style="  font-size: 12px;">   <?php echo $row['size'];?></td>
                            <td style="  font-size: 12px;">   <?php echo   $row['quantity'];?></td>
                              <?php 
                              	     
                       }
       }

this is what i am using right now…!