CODEWITHSUNDEEP
phploopspdosum
Michaël Thönnissen
Michaël Thönnissen

Reputation: 139

Sum column values while looping over a query result set

The following script shows the product and the quantity. Par example: t-shirt(1), boxershort(2).

Now I would like to show the sum of all items, in this case "3".

How can I get an array_sum of this?

$allItems = "";
$items = array();
$select_stmt = $db->prepare("SELECT CONCAT(product, ' (', quantity,')') AS ItemQty, price 
FROM database");
$select_stmt->execute();
while ($row = $select_stmt->fetch(PDO::FETCH_ASSOC))
{
    $items[] = $row["ItemQty"];
}
$allItems = implode(", ", $items);

Upvotes: -1

Views: 71

Answers (2)

El_Vanja
El_Vanja

Reputation: 3983

Avoid selecting data in a way that fits the purpose of displaying it. What you should do instead is grab only the data:

$select_stmt = $db->prepare("SELECT product, quantity, price FROM database");

Then you can easily do calculations with raw numbers:

while ($row = $select_stmt->fetch(PDO::FETCH_ASSOC))
{
    $items[] = $row["quantity"];
}
$sum = array_sum($items);

and have the display value created in PHP:

$displayItem = sprintf('%s (%d)', $row['product'], $row['quantity']);

If you insist, you could get that display string directly from the query, but then do it as an additional field:

$select_stmt = $db->prepare("SELECT product, quantity, price, CONCAT(product, ' (', quantity,')') AS ItemQty FROM database");

This way you still have quantity as a number to do calculations with and ItemQty to use for displaying purposes.

Upvotes: 1

Abolfazl Mohajeri
Abolfazl Mohajeri

Reputation: 1988

you should do this:

<?php
$allItems = "";
$items = array();
$select_stmt = $db->prepare("SELECT CONCAT(product, ' (', quantity,')') AS ItemQty, price 
FROM database");
$select_stmt->execute();
while ($row = $select_stmt->fetch(PDO::FETCH_ASSOC))
{
    preg_match('/[0-9]+/', $row["ItemQty"], $match);
    array_push($items, (int)$match);
}
echo array_sum($items);
$allItems = implode(", ", $items);
?>

Upvotes: -1

Related Questions