how to calculate sum of column values using mysql and php?

Question

How to count the sum of balance amount(bal_amt db field name) where status column is null and display it in end of the excel sheet.How to calculate sum value of the mysql column Please help me to do this

      <?php
      include_once('dbconnect.php');
      date_default_timezone_set('Asia/Dubai');
      $xls_filename = 'Report_'.date('d-m-Y').'.xls'; // Define Excel (.xls) file name


      $sql = "SELECT a.cusName as CustomerName, a.cusMob as CustomerMob, a.invoiceNo as InvoiceNo,
                    a.invoiceDate as InvoiceDate, a.SPH_right, a.CYL_right,
                    a.Axis_right, a.Prism_right,a.Add_right,
                    a.SPH_left, a.CYL_left, a.status,
                    a.Axis_left, a.Prism_left,a.Add_left, 
                    CONCAT_WS(',', IF(a.PD_right IS NULL OR a.PD_right = '', '0', a.PD_right), IF(a.PD_left IS NULL OR a.PD_left = '', '0', a.PD_left)) as PD_RL,
                    a.total_VAT as TotalVAT, a.bill_tot as BillTot,
                    a.adv_amt as AdvanceAmt, 
                    a.bal_amt as BalanceAmt,
                    b.itemsName as Items,b.rate as Rate, b.amt_vat as ItemVat,
                    ROUND(b.amt_vat + b.amount, 2) as Amount
                    FROM invoices a, invoice_items b where a.invoiceID=b.invoiceid and  a.status IS NULL
                    and a.invoiceDate between '$getfromdate' and '$gettodate' 
                    order by a.invoiceID desc";

      $result = mysqli_query($link, $sql);



      // Header info settings
      header("Content-Type: application/xls");
      header("Content-Disposition: attachment; filename=$xls_filename");
      header("Pragma: no-cache");
      header("Expires: 0");

      /***** Start of Formatting for Excel *****/
      // Define separator (defines columns in excel &amp; tabs in word)
      $sep = "\t"; // tabbed character
        $col_cnt = 0;
      // Start of printing column names as names of MySQL fields
      for ($i = 0; $i<mysqli_num_fields($result); $i++) {
        $field_info = mysqli_fetch_field($result);
        $col = "{$field_info->name}";
        echo $col . "\t";
         $col_cnt++;
      }
      print("\n");
      // End of printing column names

      // Start while loop to get data
      $row_cnt = 0;
      $tot_BalanceAmt = 0;
      while($row = mysqli_fetch_row($result))
      {
        $schema_insert = "";
        $tot_BalanceAmt +=$row['BalanceAmt'];
        for($j=0; $j<mysqli_num_fields($result); $j++)
        {
          if(!isset($row[$j])) {
            $schema_insert .= " ".$sep;
          }
          elseif ($row[$j] != "") {
            $schema_insert .= "$row[$j]".$sep;
          }
          else {
            $schema_insert .= "".$sep;
          }
        }
        $schema_insert = str_replace($sep."$", "", $schema_insert);
        $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
        $schema_insert .= "\t";
        print(trim($schema_insert));
        print "\n";
         $row_cnt ++;
      }
      print "\n";

      $schema_insert = "";
      for($i=0;$i<$col_cnt-25;$i++)
      {
        $schema_insert .= " ".$sep;
      }
      $schema_insert .= "TOTAL BALANCE = ".$sep;
      $schema_insert .= $tot_BalanceAmt.$sep;
      $schema_insert = str_replace($sep."$", "", $schema_insert);
      $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
      $schema_insert .= "\t";
      print($schema_insert);
      print "\n";


    ?>

I want to display total balance amount ``` db column name BalanceAmt ``` at end of the   excel sheet and result should be like this

    bal_amt       status

    1        

    1             cancelled

    3        

    4             cancelled

    0

    3

    0
    3

Total Balance amount : 10 (only need to calculate sum of bal_amt where status field null)

Answer 1

You have to add $Col_cnt and $row_cnt to check total row & total column

Code:

// Start of printing column names as names of MySQL fields
  $col_cnt = 0;
  for ($i = 0; $i<mysqli_num_fields($result); $i++) {
    $field_info = mysqli_fetch_field($result);
    $col = "{$field_info->name}";
    echo $col . "\t";
    $col_cnt++;
  }
  print("\n");
  // End of printing column names

  $row_cnt = 0;
  $tot_BalanceAmt = 0;
  while($row = mysqli_fetch_assoc($result))
  {
    $schema_insert = "";
    $schema_insert .= $row['col_1'].$sep.$row['col_2'].$sep.$row['col_price'];
    $tot_BalanceAmt +=$row['col_price'];
    $schema_insert = str_replace($sep."$", "", $schema_insert);
    $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
    $schema_insert .= "\t";
    print(trim($schema_insert));
    print "\n";
    $row_cnt ++;
  }

  $schema_insert = "";
  for($i=0;$i<$col_cnt-2;$i++)
  {
    $schema_insert .= " ".$sep;
  }
  $schema_insert .= "Total Amount".$sep;
  $schema_insert .= $tot_BalanceAmt.$sep;
  $schema_insert = str_replace($sep."$", "", $schema_insert);
  $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
  $schema_insert .= "\t";
  print($schema_insert);
  print "\n";