I am looking for a PHP method which would allow me to calculate the sum of 2 values. In my example I have 3 tables and I would like to add table 1 with table 3 because they have the same article_id and the same declination_id.
$sql = 'SELECT SUM(quantity) AS qty, SUM(quantity) * price_cost - ((SUM(quantity) * price_cost) * discount) / 100 AS cost, articles_id AS id_article, combination_id AS declinaison FROM invoice_detail';
if ($inventoryType == 'year') {
$sql .= ' WHERE DATE_FORMAT(date_add, \'%Y\') = DATE_FORMAT(NOW(), \'%Y\')';
} else {
$sql .= ' WHERE DATE_FORMAT(date_add, \'%Y-%m\') = DATE_FORMAT(NOW(), \'%Y-%m\')';
}
$sql .= ' GROUP BY invoice_id, articles_id, combination_id';
$statement = $conn->prepare($sql);
$statement->execute();
$cm = $statement->fetchAll();
foreach ($cm as $key => $item) {
// Calcul du CUM
dump($item);
}
Basically if there is in the loop the same id_article and the same id_declinaison we must sum all the values of the column "cost" in my example I would therefore like to return an array that looks like this:
3_1 => [
'cost' => 112.875,
'qty' => 60
];
3_2 => [
'cost' => 31.5,
'qty' => 10
];
I would like to have that at the end as a result.
This will be used to calculate the CUMP of my products.
Thank you for your help.
My Answer:
>prepare($sql);
$statement->execute();
$result = $statement->fetchAll();
$sum = 0;
foreach ($result as $row) {
if ($row['declinaison'] == 1 || $row['declinaison'] == 3) {
$sum += $row['qty'] * $row['cost'];
}
}
echo "The sum of table 1 and table 3 is: " . $sum;
Rate this post
5 of 5 based on 9322 votesComments