php sum column in multiple tables and return as one total

Question

I have a php code below that sum up the column going through multiple tables and return 3 separate total amounts after the query is done since I have 3 tables. If I want to sum up those 3 total amounts into one, in what ways do I need to change the code to accomplish that?

foreach($result as $row) {
  $stmt1 = $DB_CON_C->prepare("SELECT SUM(total_fee) AS total_amount FROM `".$row['files']."`");
  $stmt1->execute();
  $sum1 = $stmt1->fetch(PDO::FETCH_ASSOC);

  echo $sum1['total_amount'];
}

Your code is vulnerable to attacks. The advantage of using prepare statement is so that you can bindParams — FrenchMajesty
– FrenchMajesty, Commented May 11, 2017 at 5:10
@FrenchMajesty - You can't bind column names (which the OP is inserting) using prepared statements. The column names should never be injected using user inputs, though. There are safer ways to do it, like having a "white list" of column names that are acceptable. — M. Eriksson
– M. Eriksson, Commented May 11, 2017 at 5:14

FrenchMajesty · Accepted Answer · 2017-05-11 05:12:54Z

1

Try something like this:

$totalAmount = 0;

foreach($result as $row) {
    $stmt1 = $DB_CON_C->prepare("SELECT SUM(total_fee) AS total_amount FROM `".$row['files']."`");
    $stmt1->execute();

    $sum1 = $stmt1->fetch(PDO::FETCH_ASSOC);

  totalAmount += $sum1['total_amount'];
}

echo $totalAmount;

Just create a variable outside of the loop's scoop and add up to it.

answered May 11, 2017 at 5:12

FrenchMajesty

1,1492 gold badges16 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MasterJoe Over a year ago

Thanks. This works perfectly when added the missing $ before totalAmount.

Carl Binalla · Accepted Answer · 2017-05-11 05:13:29Z

1

Try something like this. Create a variable outside the loop, then increment it per loop.

$grandTotal = 0;
foreach($result as $row) {
  $stmt1 = $DB_CON_C->prepare("SELECT SUM(total_fee) AS total_amount FROM `".$row['files']."`");
  $stmt1->execute();
  $sum1 = $stmt1->fetch(PDO::FETCH_ASSOC);

  $grandTotal = $grandTotal +  $sum1['total_amount'];
}
echo $grandTotal;

Not sure if this works, but you get the idea.

answered May 11, 2017 at 5:13

Carl Binalla

5,4315 gold badges31 silver badges49 bronze badges

Collectives™ on Stack Overflow

php sum column in multiple tables and return as one total

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest