Mysql Price Calculation

Topics: Developer Forum, Project Management Forum, User Forum
Feb 3, 2014 at 10:48 AM
// connection with the database
include ('core/config.php');
include ('core/connect.php');

// require the PHPExcel file
require 'core/Classes/PHPExcel.php';

// simple query

$headings = array('Item','Product','Qty','Price','Line Total');

if ($result = mysql_query($query) or die(mysql_error())) {
    // Create a new PHPExcel object
    $objPHPExcel = new PHPExcel();

    $rowNumber = 13;
    $col = 'A';
    foreach($headings as $heading) {

    // Loop through the result set
    $rowNumber = 14;
    while ($row = mysql_fetch_row($result)) {
       $col = 'A';
       foreach($row as $cell) {
    $objPHPExcel->getActiveSheet()->setCellValue('B1', 'Test value');

    // Freeze pane so that the heading line will not scroll

    // Save as an Excel BIFF (xls) file
     // Save Excel 2007 file
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    //$objWriter->save(str_replace('.php', ''. $_SESSION['user'].'-' . $_SESSION['CustNo'] .'-quote.xlsx', __FILE__));
    $objWriter->save(''. $_SESSION['user'].'-' . $_SESSION['CustNo'] .'-quote.xlsx');
I am using the following code to get data from a mysql table. I want to calculate the sellprice and the line total underneath all the values and I am not sure how to do this? Is it something to do with gethighestrow? and then the calculation?
Feb 4, 2014 at 7:41 AM
Two ways to do this:
-Do the calculation in php: each time through the loop, you add the value of each record, and then out of loop, after the last line, you set the value of the cell with the contents of your variable.
-Do the calculation in Excel (*). Out of loop, you defined a form that performs the desired operation, identical to that which you saisirais directly in Excel (in English) after the last row. Concerning, the coordinates, you know the desired column, the line is given by rowNumber

(*)Either by the calculation engine PHPExcel reproducing the operation of Excel, Excel itself at the opening of the file.
Feb 4, 2014 at 8:09 AM

I am fine with all this (doing the calculations etc) but I am not sure how to set the last line. Is it by using gethighestrow?

Feb 4, 2014 at 2:55 PM
I think I'd do the simplest: [...]->setCellValue('E'.$rowNumber, $MyTotal) or [...]->setCellValue('E'. $rowNumber, '=SUM(E14:E'.($rowNumber-1).')')
Although I would certainly have constants for the column and the first row to facilitate subsequent amendments