CODEWITHSUNDEEP
mysqlfindcakephp-1.3distinct
sloga
sloga

Reputation: 642

select distinct year from created date in mysql db table using cakephp 1.3

I have working sql query that returns one row for each year that there is a report. I need the syntax for cakephp 1.3 controller to achieve the same results.

SELECT DISTINCT Year( `dated` )
FROM `reports`
ORDER BY Year( `dated` ) ASC

My cakephp 1.3 queries I've tried:

$theYear = $this->Model->Database->find('all',array('fields'=>'DISTINCT Report.dated as Year')); // this still returns all entries, not distinct

or

$theYear = $this->Model->query('SELECT DISTINCT Report.dated from Report as Year ORDER by Report.dated ASC'); // returns bool(false)

also tried a function in my cake model:

function getYears()
{
    $ret = $this->query
      (
      "SELECT DISTINCT dated FROM reports as Year ORDER BY dated ASC"
     );
    return $ret;
} // returns NULL

expected results are: 2011 2012 2013

Upvotes: 1

Views: 1114

Answers (1)

sloga
sloga

Reputation: 642

$theYear = $this->Model->Database->find('all',array('fields'=>'DISTINCT Year(Report.dated)' )); // this appears to be the correct syntax

Upvotes: 1

Related Questions