need help using MySQL SUM with modx newQuery

57 Posts

ntech Reply #1, 15 years, 9 months ago

i have a number of fields in a database which i’ve (thanks to Bob’s Guides) created a "component" for. So far getting all my results has been fairly simple, i’m a bit stumped in this case as what i want to do is use the MySQL SUM function to add up rows in a column that meet a criteria, this would only give me one result, i’ve tried using getOne on this but i’m obviously doing something wrong somewhere.

here’s my code so far, as always any help is appreciated!

<?php
$path = MODX_CORE_PATH . 'components/donate/'; //this is my component
$result = $modx->addPackage('donate',$path . 'model/','touch_');
$donate = $modx->newObject('Donate'); //make object

$id = $modx->resource->get('id'); //i use the resource id in my sql statement

$c = $modx->newQuery('Donate');
$c->select('SUM(amount) as amount'); //amount is my column in the db with the amounts i want to SUM up
$c->where(array(
    'page_id' => $id,
    'complete' => 0
));
$sum = $modx->getObject('Donate',$c); //since "Donate" is an object, i can use it like this right?
$output = $sum->getOne('amount');

echo $output;

this returns nothing. i tried some alternatives but i just get errors

MODX Staff
10,725 Posts

opengeek Reply #2, 15 years, 9 months ago

You don’t want to use the object-retrieval methods like xPDO->getObject() or xPDOObject->getOne() when working with result sets like this. You just want a regular result set using the PDOStatement which xPDOQuery objects wrap, e.g. something like this...

<?php
$path = MODX_CORE_PATH . 'components/donate/'; //this is my component
$result = $modx->addPackage('donate',$path . 'model/','touch_');
$id = $modx->resource->get('id'); //i use the resource id in my sql statement

$c = $modx->newQuery('Donate');
$c->select('SUM(amount) as amount'); //amount is my column in the db with the amounts i want to SUM up
$c->where(array(
    'page_id' => $id,
    'complete' => 0
));
$c->prepare();
$c->stmt->execute();
$sum = $c->stmt->fetchColumn();
$c->stmt->closeCursor();

echo $sum;

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

57 Posts

ntech Reply #3, 15 years, 9 months ago

oh, ok cool, i’m still getting my head around all the new Revo features, thanks for the help!