Member Avatar for dioz20

Hello.

I need some help with summing up the total price each company owe to us.

Basically it's looking like this currently:
http://i.imgur.com/7kkdt.png
For English speakers,

Företag = Company
Datum = Date
Artikel = Product
Antal = Amount
Pris = Price

And then the last row
Företag = Company
Total Summa = Total sum

The code for printing out the rows is currently looking like this:

$result = mysql_query("Select Customer.Customer_Company_Name,
  Cleaning.Cleaning_Date,
  Product.Product_Name,
  CleaningRow.CleaningRow_Product_Amount,
  CleaningRow.CleaningRow_Customer_Total_Price,
  Cleaning.Cleaning_Invoice
From Cleaning Inner Join
  CleaningRow On Cleaning.CleaningID = CleaningRow.CleaningID And
    Cleaning.CustomerID = CleaningRow.CustomerID Inner Join
  Product On CleaningRow.ProductID = Product.ProductID Inner Join
  Customer On CleaningRow.CustomerID = Customer.CustomerID
Order By Customer.Customer_Company_Name");

printf ("<table>");
printf ("<tr>");
printf ("<td><b>F&ouml;retag</b></td> <td><b>Datum</b></td> <td><b>Artikel</b></td> <td><b>Antal</b></td> <td><b>Pris</b></td>");
printf ("</tr>");
while($row = mysql_fetch_array($result))
{
	printf("<tr>");
	printf("<td>{$row['Customer_Company_Name']}</td> <td>{$row['Cleaning_Date']}</td> <td>{$row['Product_Name']}</td> 
<td>{$row['CleaningRow_Product_Amount']}</td> <td>{$row['CleaningRow_Customer_Total_Price']}</td>");
	printf("<br />");
	printf("</tr>");
}
printf ("</table>");

Currently as you see it's only printing out the sum for that individual order.

What I want to print out in the last row is the total sum each company owes so something like:

Company --- Total Sum
Ericsson --- 402699 (395409 + 5985 + 1305 + 0 + 0)
IKEA --- 689100 (130500 + 558600)
SAAB --- 0 (0)

But I'm just not sure how to do this. If I store the numbers I get from CleaningRow_Customer_Total_Price in an array and sum them together I will have no clue what sum belongs to what company, and that's essentially the problem. Any help is appreciated and ask if something is unclear and I'll do my best to explain it better.

  • 2 Contributors
  • 2 Replies
  • 116 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by dioz20

Dani AI

Generated

A couple of practical options were already touched on in this thread: accumulate totals in PHP (as suggested) or let the database do the work. Both work, but for correctness and scalability it is usually better to aggregate in SQL and fetch one row per company. solved the immediate problem with a PHP-side accumulator, which is fine for small result sets; below is a compact, safer, and more efficient alternative that pushes the summation into the database and prints the results.

// example using PDO (replace DSN/user/pass)
$pdo = new PDO('mysql:host=localhost;dbname=yourdb;charset=utf8mb4', 'user', 'pass');

$sql = "
  SELECT c.CustomerID AS id,
         c.Customer_Company_Name AS company,
         COALESCE(SUM(cr.CleaningRow_Customer_Total_Price), 0) AS total_sum
  FROM Customer c
  LEFT JOIN CleaningRow cr ON cr.CustomerID = c.CustomerID
  GROUP BY c.CustomerID, c.Customer_Company_Name
  ORDER BY c.Customer_Company_Name
";

$stmt = $pdo->query($sql);

echo \"<table>\";
echo \"<tr><td><b>Company</b></td><td><b>Total</b></td></tr>\";
foreach ($stmt as $row) {
    echo \"<tr><td>\" . htmlspecialchars($row['company'], ENT_QUOTES) . \"</td>\";
    echo \"<td>\" . number_format((float)$row['total_sum'], 2, '.', ',') . \"</td></tr>\";
}
echo \"</table>\";

Notes and troubleshooting tips:

  • Group by the company primary key (CustomerID) rather than the company name to avoid mismatches caused by typos, whitespace, or case differences.
  • Use LEFT JOIN + COALESCE (or IFNULL) when you want companies with no rows to show a zero total.
  • Store money as DECIMAL in the database to avoid floating-point rounding errors; format for display with number_format (or SQL formatting if preferred).
  • For larger datasets the database aggregate is far faster than accumulating in PHP; ensure an index exists on the join column (e.g., CleaningRow.CustomerID).
  • If using user-supplied filters, use prepared statements to avoid SQL injection (see the PDO docs: https://www.php.net/manual/en/book.pdo.php). For SUM/GROUP BY behavior see MySQL aggregate docs: https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html.

Recommended Answers

All 2 Replies

Member Avatar for pritaeas

Actually, you do know what company it belongs to. Your results are ordered by the company name. So you could do something like this (within the while loop):

//before while:
$totals = array ();

//inside while:
if (!isset($totals[$row['Customer_Company_Name']])) {
  $totals[$row['Customer_Company_Name']] = 0;
}
$totals[$row['Customer_Company_Name']] += $row['CleaningRow_Customer_Total_Price'];

// after the while:
print_r($totals); // for quick check
Edited by pritaeas because: n/a
Member Avatar for dioz20

Actually, you do know what company it belongs to. Your results are ordered by the company name. So you could do something like this (within the while loop):

//before while:
$totals = array ();

//inside while:
if (!isset($totals[$row['Customer_Company_Name']])) {
  $totals[$row['Customer_Company_Name']] = 0;
}
$totals[$row['Customer_Company_Name']] += $row['CleaningRow_Customer_Total_Price'];

// after the while:
print_r($totals); // for quick check

Thank you! After a little while I got it working just like I wanted. You were a huge help! Tagging this as solved. :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.