Thread

Hi all. I have a question about whether SQL can accomplish this or I have to do something else. I have a magazine that has 12 editions per year. Each Edition has an {edition-date}, a custom date field (Jan 01, 2010, Feb 01, 2010, etc…).

In the archives section, I want to have a dropdown menu with only the available years of existing Editions. Not the entry date (so {exp:yearly_archives} will not work).

I’ve tried this:

{exp:query sql='SELECT DISTINCT field_id_12 AS year FROM exp_weblog_data WHERE field_id_12 <> ""'}
    <option value="{year}">{year format="%Y"}</option
{/exp:query}

Which works perfectly, but just isn’t what I want since it is spitting out 2010 12 times, etc… For instance, Jan->December will all have unique entry-dates, but they are all 2010, for instance. Is there a way to interpret each date and limit accordingly? If not - other ideas other than hard-coding?

Thanks!

from my SQL guru..

{exp:query sql='SELECT DISTINCT year(from_unixtime(field_id_12)) AS year FROM exp_weblog_data'}
    <option value="{year}">{year}</option
{/exp:query}

So, you’re good, i.e. just sharing your solution with us?

yep 😊

Cool 😊 Closing.