Thread

Hello, EE query wizards!  I’m hoping someone can help with with this one.  I’m looking to create a list of years from a custom date field.  I’m building a page that is essentially a yearly archive.  I have done this statically already but I would like to actually use a query to go into my entries for this channel and put together a list of the years for which there are entries, but on the basis of years represented in a custom date field and not the entry date.  I also need to limit the resulting list by a language, which is also a custom field in the field group.

So basically, I have a channel called “statements” in which I am using a custom date field to capture the dates around which the entries should be sorted.  I also have a custom field called “language” in which the language of the entry is stored - en for english and fr for french.  So what i need is a query that loops through all the entries in the channel and builds a list of unique years for which there are entries within a given language.  So if there are 3 entries in 2012 with “en” set as the language, that would be one list item - 2012.

Can someone coach me through the query that would achieve this?

So the ever-so-generous-with-his-time @objectivehtml (follow him on twitter y’all!) set me straight.  My query wound up being:

{exp:query sql="SELECT DISTINCT FROM_UNIXTIME(field_id_##, '%Y') as 'cf_statements_order_date' FROM exp_channel_data WHERE channel_id = ## AND field_id_## = '{user_language}' ORDER BY field_id_## DESC"}

 <li><a href="http://{path={segment_1}/{segment_2}/{cf_statements_order_date}}">{cf_statements_order_date}</a></li>

{/exp:query}

Very cool!  Consider this thread RESOLVED!