John Davidson

Message:

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;