How do I make a custom query to retrieve max values on columns?

275 Posts

skndn60 Reply #1, 15 years, 11 months ago

I need to do an Ajax request to find the maximum values of YEAR and MONTH columns in a custom table. The Ajax request is working, however the query isn’t.

The query should be something like:

select max(year), max(month) from month

I tried, based on an example I found in one of the forum posts:

$query = $modx->newQuery('Month');
$query->select('max(year), max(month');
$query->stmt->execute();
$result = $query->stmt->fetchAll(PDO::FETCH_COLUMN);

However, that generates a PHP error that "stmt" is not an object.

What would be the proper way of doing that?

Thanks.

MODx Revolution / MAMP / OS X

4,149 Posts

splittingred Reply #2, 15 years, 11 months ago

$stmt = $modx->query('SELECT MAX(year), MAX(month) FROM '.$modx->getTableName('Month'));
if ($stmt) {
    $result = $stmt->fetchAll(PDO::FETCH_COLUMN);
    /* do stuff */
}

shaun mccormick | bigcommerce mgr of software engineering, former modx co-architect | github | splittingred.com

275 Posts

skndn60 Reply #3, 15 years, 11 months ago

Thanks. That did it.

MODx Revolution / MAMP / OS X

MODX Staff
10,725 Posts

opengeek Reply #4, 15 years, 11 months ago

FWIW, you don’t want to use PDO::FETCH_COLUMN unless you want a single array of results from a single column in the result set. IOW, this mode returns an array values from a single column, from multiple rows. You would never have access to the second column in your result set this way.

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

275 Posts

skndn60 Reply #5, 15 years, 11 months ago

Yes, thanks for that. I figured that out myself as well when I found that the second column was missing...

MODx Revolution / MAMP / OS X