0

A user is able to download an invoice by clicking the 'download CSV file'. The invoice pulls the following data from my database : 

$sql = mysql_query("SELECT Jobs.date, Jobs.order_ref, Jobs.quantity, Jobs.packing_price, Jobs.dispatch_type, Jobs.courier_price from Jobs WHERE order_type = 'Retail' AND confirmed = 'Yes'");

I am trying to add 2 new columns to the file "total" and "subtotal" and these will contain the line totals and then the overall total. My code doesn't add a new column and neither does it calculate the totals. I just get a 0. 

<?php
include("../controllers/cn.php");

// Fetch record from Database
$output= "";

$sql = mysql_query("SELECT Jobs.date, Jobs.order_ref, Jobs.quantity, Jobs.packing_price, Jobs.dispatch_type, Jobs.courier_price from Jobs WHERE order_type = 'Retail' AND confirmed = 'Yes'");

$columns_total = mysql_num_fields($sql);

// Get The Field Name

/********************************
 *      RETAIL
 ********************************/
for ($i = 0; $i < $columns_total; $i++) {
    $heading = mysql_field_name($sql, $i);

    $output .= '"'.$heading.'",';
}
$output .="\n";

while ($row = mysql_fetch_array($sql)) {

    $data[] = 'New Column';
    $newCsvData[] = $data;
    for ($i = 0; $i < $columns_total; $i++) {
        $total[] .= $i['packing_price'] + $i['courier_price'];
        $sum .= $['packing_price'] + $i['courier_price']; 
        $output .='"'.$row["$i"].'",';
    }
    $output .="\n";
}

// Download the file
$filename = "myFile.csv";
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename='.$filename);

echo "Retail";
echo "\n";
echo $output;

?>
Barmar
  • 741,623
  • 53
  • 500
  • 612
user3519721
  • 21
  • 9
  • Your code makes no sense. `$i` is a number, not an array, what is `$i['packing_price']` supposed to be? You're calculating `$total[]` and `$sum`, but not adding them to `$output`. – Barmar May 27 '14 at 08:17
  • @Barmar $i['packing_price'] is supposed to be the column that contains all the prices that need to totalled – user3519721 May 27 '14 at 08:20
  • Why don't you add `packing_price + courier_price` to your `SELECT` clause? – Barmar May 27 '14 at 08:21
  • Shouldn't that be `$row['packing_price']` and `$row['courier_price']`? You should only calculate these once per row, not while you iterate over the columns. – Barmar May 27 '14 at 08:22
  • @Barmar it is in my select clause, unless you mean to actually do the maths in the select clause? – user3519721 May 27 '14 at 08:23
  • Where do you want the grand total to be put? You can't add it to every row, because it's not calculated until you've iterated through all the rows. – Barmar May 27 '14 at 08:24
  • Yes, I mean to do the math in the select clause. – Barmar May 27 '14 at 08:24
  • Make sure you have error reporting turned on. You should be getting warnings for `$i['packing_price']` and `$i['courier_price']`. – Barmar May 27 '14 at 08:25
  • @Barmar There needs to be a subtotal for each line which is the $row['packing_price'] + $row['courier_price']. And then a grand total at the bottom which adds up all the subtotals together – user3519721 May 27 '14 at 08:26
  • Please be aware that the mysql extension (supplying the `mysql_` functions) has been deprecated since 2012, in favor of the mysqli and PDO extensions. It's use is highly discouraged. See http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php – Oldskool May 27 '14 at 13:05

1 Answers1

0
$sum = 0;
while ($row = mysql_fetch_array($sql)) {
    for ($i = 0; $i < $columns_total; $i++) {
        $output .='"'.$row["$i"].'",';
    }
    $total = $row['packing_price'] + $row['courier_price'];
    $output .= $total;
    $sum += $total; // Add to overall total
    $output .="\n";
}
// Download the file
$filename = "myFile.csv";
header('Content-type: application/csv');
header('Content-Disposition: attachment; filename='.$filename);

echo "Retail";
echo "\n";
echo $output;
echo "$sum\n";
Barmar
  • 741,623
  • 53
  • 500
  • 612