-1

I'm trying to get some data out of my database from a specific table which currently has 429,000 records.

I'm trying to total up the 'amount' column but running this query is giving me the error of:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 32 bytes)

Here's my query

$stmt = $db->prepare("SELECT amount FROM salesOrderTransaction WHERE recordStatus=:recordStatus AND paymentStatus=:paymentStatus");
$stmt->bindValue(':recordStatus', '1', PDO::PARAM_STR);
$stmt->bindValue(':paymentStatus', 'Future', PDO::PARAM_STR);
$stmt->execute();

$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

$total = 0;

foreach ($results as $row) {
    $total = $total + $row['amount'];
}

$response['totalTransactionsOwed'] = $total;
Michael Bellamy
  • 543
  • 1
  • 8
  • 16
  • Possible duplicate of [Fatal Error: Allowed Memory Size of 134217728 Bytes Exhausted (CodeIgniter + XML-RPC)](https://stackoverflow.com/a/18660082/6521116) – LF00 Jun 14 '17 at 10:15
  • 1
    Why not use something like `SELECT SUM(amount) FROM ...` ? – CD001 Jun 14 '17 at 10:16
  • Why have I been down voted? – Michael Bellamy Jun 14 '17 at 10:34
  • I didn't downvote but this question suffers from the [XY problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). You should have googled on "how to sum a column in MySQL" instead of doing something wrong and then asking about how to fix the consequences of the wrong approach. – apokryfos Jun 14 '17 at 10:40
  • Thanks for that, to be honest I didn't know the option of 'SUM' existed but a quick Google might have helped that. Thanks. – Michael Bellamy Jun 14 '17 at 10:44

3 Answers3

3

you can get the value in just one row:

$stmt = $db->prepare("SELECT sum(amount) as totalAmount FROM salesOrderTransaction WHERE recordStatus=:recordStatus AND paymentStatus=:paymentStatus");

Also check if you have index on recordStatus and paymentStatus

verhie
  • 1,298
  • 1
  • 7
  • 7
1

If it's just the sum you need you can do: 

$stmt = $db->prepare("SELECT sum(amount) as sum FROM salesOrderTransaction WHERE recordStatus=:recordStatus AND paymentStatus=:paymentStatus");
$stmt->bindValue(':recordStatus', '1', PDO::PARAM_STR);
$stmt->bindValue(':paymentStatus', 'Future', PDO::PARAM_STR);
$stmt->execute();

$row = $stmt->fetch(PDO::FETCH_ASSOC);

$response['totalTransactionsOwed'] = isset($row["sum"])?$row["sum"]:0;
apokryfos
  • 38,771
  • 9
  • 70
  • 114
1

why don't you use SUM mysql function?

select sum(amount) as amount from ...

also you should read about generators, imho

Viktar Pryshchepa
  • 333
  • 1
  • 8