<?php $c = $this->modx->newQuery('Model'); $c->select('id, model, code, SUM(qty) AS qty'); $c->groupby('model'); $stmt = $c->prepare(); $stmt->execute(); $rows = $stmt->fetchAll(); $count = count($rows);
<?php $c = $modx->newQuery($table); $c->select('id, model, SUM(failures) as failures'); $c->groupBy('model'); //Count return the right result here $t = $c->prepare(); $t->execute(); $rows = $t->fetchAll(); $count = count($rows); //Seems like those properties are not taken into consideration $c->sortby($sort,$dir); $c->limit($limit, $start); $fields = $modx->getCollection($table, $c);
<?php //Retreive the count $c = $modx->newQuery($table); $c->groupBy('model'); $stmt = $c->prepare(); $stmt->execute(); $rows = $stmt->fetchAll(); $count = count($rows); //Do the actual query $c = $modx->newQuery($table); $c->select('id, model, SUM(failures) as failures'); $c->groupBy('model'); $c->sortby($sort,$dir); $c->limit($limit, $start); $fields = $modx->getCollection($table, $c);
<?php //Retreive the count $c = $modx->newQuery($table); $c->select("COUNT(DISTINCT model)"); if ($c->prepare() && $c->stmt->execute()) { $rows = $c->stmt->fetchAll(PDO::FETCH_COLUMN); $count = (integer) reset($rows); //Do the actual query only if there is a count if ($count > 0) { $c = $modx->newQuery($table); $c->select('id, model, SUM(failures) as failures'); $c->groupBy('model'); $c->sortby($sort,$dir); $c->limit($limit, $start); $fields = $modx->getCollection($table, $c); } }
PDO::FETCH_COLUMN is an option to return the results only from a single column; I didn’t specify the column here because I just want the first column, which is the default. Since I only want the result from the count, which is the first and only column in my result set, I’m just using this so I don’t have to fetchAll(PDO::FETCH_NUM), reset($rows) and then get the count from $row[0]; fewer steps if I just return a single column and grab the only result with a simple reset($rows).
I need to read more on PDO because even though you give me the solution, i don’t really understand why you use $c->stmt->fetchAll(PDO::FETCH_COLUMN) instead of fetchAll() alone.