This is an archived copy of the forum for reference purposes

Date column format

posted by xoan 8 years ago

Hi all,

I have this database tables:

CREATE TABLE editions (
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    date DATE
);

CREATE TABLE shortfilms (
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    edition_id INTEGER NOT NULL,
    FOREIGN KEY (edition_id) REFERENCES editions(id) ON DELETE CASCADE
);

And in my application I pass the year of the edition as a GET parameter:

http://domain.tld/2007

So I need to build a fRecordSet with all the shortfilms of this year edition, but I don't know how to achieve this, because date is store in date format, and I can't use something like that:

$shortfilms = fRecordSet::build('Shortfilm', array(
    'editions.YEAR(date)=' => fRequest::get('year')
));

Thanks in advance.

Just use simple comparisons with the first and last day of the year:

$year = fRequest::get('year', 'integer');
$shortfilms = fRecordSet::build(
    'Shortfilm',
    array(
        'editions.date>=' => $year . '-01-01',
        'editions.date<=' => $year . '-12-31'
    )
);

posted by wbond 8 years ago